Əsas səhifə > MySQL > Emulating Oracle’s PIVOT operator in MySQL

Emulating Oracle’s PIVOT operator in MySQL

Bu yaxınlarda lazım olan bir halda MySQL-də tapa bilmədiyim, lakin Oracle-da mövcud olan PİVOT-dan danışacıq və bunun “workaround”-unu göstərəcik.
İlk öncə PİVOT nədir? Deyək ki aşağıdakı şəkildə cədvəlimiz var:

CREATE TABLE pivot_test (
  id            NUMBER,
  customer_id   NUMBER,
  product_code  VARCHAR2(5),
  quantity      NUMBER
);

Və Cədvəl daxilində aşağıdakı məlumatlar var:

INSERT INTO pivot_test VALUES (1, 1, 'A', 10);
INSERT INTO pivot_test VALUES (2, 1, 'B', 20);
INSERT INTO pivot_test VALUES (3, 1, 'C', 30);
INSERT INTO pivot_test VALUES (4, 2, 'A', 40);
INSERT INTO pivot_test VALUES (5, 2, 'C', 50);
INSERT INTO pivot_test VALUES (6, 3, 'A', 60);
INSERT INTO pivot_test VALUES (7, 3, 'B', 70);
INSERT INTO pivot_test VALUES (8, 3, 'C', 80);
INSERT INTO pivot_test VALUES (9, 3, 'D', 90);
INSERT INTO pivot_test VALUES (10, 4, 'A', 100);


SELECT * FROM pivot_test;

        ID CUSTOMER_ID PRODU   QUANTITY
---------- ----------- ----- ----------
         1           1 A             10
         2           1 B             20
         3           1 C             30
         4           2 A             40
         5           2 C             50
         6           3 A             60
         7           3 B             70
         8           3 C             80
         9           3 D             90
        10           4 A            100

10 rows selected.

Tapşırıq belədir ki, A , B və C product-unun ayrıca toplam QUANTITY-sini tapmalıyıq. Yəni bu cədvəldə toplam olaraq A , B və C-nin QUANTITY-si ayrılıqda nə qədərdir?
Oracle-da bu çox asan hazır şəkildə tapılır:

SELECT *
FROM   (SELECT product_code, quantity
        FROM   pivot_test)
PIVOT  (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c));

A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
-------------- -------------- --------------
           210             90            160

1 row selected.

Gördüyünüz kimi cədvəldə A , B və C ayrıca hesablandı.
MySQL-də belə bir operator yoxdur lakin həll yolu var.

CREATE TABLE pivot_test (
  id            int,
  customer_id   int,
  product_code  VARCHAR(5),
  quantity      int
);

-- insertlər yuxarıdakı qaydada

mysql> select * from pivot_test;
+------+-------------+--------------+----------+
| id   | customer_id | product_code | quantity |
+------+-------------+--------------+----------+
|    1 |           1 | A            |       10 |
|    2 |           1 | B            |       20 |
|    3 |           1 | C            |       30 |
|    4 |           2 | A            |       40 |
|    5 |           2 | C            |       50 |
|    6 |           3 | A            |       60 |
|    7 |           3 | B            |       70 |
|    8 |           3 | C            |       80 |
|    9 |           3 | D            |       90 |
|   10 |           4 | A            |      100 |
+------+-------------+--------------+----------+
10 rows in set (0.00 sec)

Və eyni nəticəni biz aşağıdakı sorğu ilə ala bilirik:

select
sum(case when product_code='A' then quantity end) as a_sum_quantity,
sum(case when product_code='B' then quantity end) as b_sum_quantity,
sum(case when product_code='C' then quantity end) as c_sum_quantity
from pivot_test;

+----------------+----------------+----------------+
| a_sum_quantity | b_sum_quantity | c_sum_quantity |
+----------------+----------------+----------------+
|            210 |             90 |            160 |
+----------------+----------------+----------------+
1 row in set (0.09 sec)

Bu üsulu yadda saxladıqdan daha advance bir çalışmanı həll edə bilərik.
Tapşırıq bundan ibarətdir ki, yaş aralıqlarına uyğun olaraq qadın və kişilərin nə qədər hesablaşma(xərcləmə)
apardıqlarını tapmaq lazımdır. Yəni deyək ki, 16 yaşında 5 kişi və 3 qadın var. 16 yaşlı kişilərin toplam harcaması ayrı, 16 yaşlı qadınların toplam nə qədər harcama apardığını ayrıca tapmaq lazımdır. Aşağıdakı sorğu ilə bunu çox asand tapa bilirik:

select (year(now())-year(birthday)) as `age`,
round(sum(case when gender=1 then sum end),2) as kisi_sum,
round(sum(case when gender=0 then sum end),2) as qadin_sum
from user_sum_statistics
where wu_status!=2 and wt_status!=2
group by `age`;
.
.
+------+----------+-----------+
| age  | kisi_sum | qadin_sum |
+------+----------+-----------+
|   16 |     6.00 |      NULL |
|   17 |     NULL |      4.80 |
|   18 |    27.90 |      NULL |
|   19 |     NULL |    222.20 |
|   20 |   158.80 |     27.00 |
|   21 |    74.20 |     74.98 |
|   22 |    40.30 |    718.18 |
|   23 |   118.40 |     26.00 |
|   24 |   271.92 |   1984.65 |
|   25 |     NULL |   1245.39 
.
.

Gördüyünüz kimi hesablanır və doğru cavab çıxır. Lakin 16 yaşlı qadın, 17 yaşlı kişi və.s yaşlı harcama aparmayan insanlar olduğu üçün NULL-lar çıxır. Hesablama-da NULL-un olması xoşagələn olmadığı üçün çox gözəl olardı ki NULL-ların yerinə 0 hesablansın.
Buna da MySQL imkanları ilə nail olmaq olar:

select (year(now())-year(birthday)) as `age`,
if(sum(case when gender=1 then sum end) is null,0,round(sum(case when gender=1 then sum end),2)) as kisi_sum,
if(sum(case when gender=0 then sum end) is null,0,round(sum(case when gender=0 then sum end),2)) as qadin_sum
from user_sum_statistics
where wu_status!=2 and wt_status!=2
group by `age`;

+------+----------+-----------+
| age  | kisi_sum | qadin_sum |
+------+----------+-----------+
|   16 |     6.00 |      0.00 |
|   17 |     0.00 |      4.80 |
|   18 |    27.90 |      0.00 |
|   19 |     0.00 |    222.20 |
|   20 |   158.80 |     27.00 |
|   21 |    74.20 |     74.98 |
|   22 |    40.30 |    718.18 |
|   23 |   118.40 |     26.00 |
|   24 |   271.92 |   1984.65 |
|   25 |     0.00 |   1245.39 |

Daha başqa üsul və inkişafetdirmələri(improvements) olanlar mütləq yazsınlar.

Təşəkkürlər😉

  1. İyun 13, 2013 tarixində, saat 07:13

    Gözəl bir mövzudur təşəkkürlər əlavə etdim arxivə🙂

    • İyun 13, 2013 tarixində, saat 07:15

      Qiymətləndirib comment atdığınız üçün də mən sizə təşəkkür edirəm )))

  1. No trackbacks yet.

Bir cavab yazın

Sistemə daxil olmaq üçün məlumatlarınızı daxil edin və ya ikonlardan birinə tıklayın:

WordPress.com Loqosu

WordPress.com hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Twitter rəsmi

Twitter hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Facebook fotosu

Facebook hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Google+ foto

Google+ hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

%s qoşulma

%d bloqqer bunu bəyənir: