Pivot tables с MySQL

Отдавна ми стоеше този пост в драфта, и днес реших спонтанно да взема да го допиша, за да мога да приключа колкото може повече задачи в тази година :)

Предполагам, почти на всеки му се е налагало да си прави различни сумарни сметки, за което Excel-а е безкрайно полезен с неговите си пивот таблици. Но, да предположим, че поради някакви причина искате това да стане директно в MySQL.

За целта, избрах произволен туториал на тема Pivot tables in Excel. Дори използвах и примерните данни в урока, които може да си импортирате от тук.

Първият пример е повече от елементарен, но ще ги изкараме подред.

SELECT 
    Product,
    SUM(`Amount`) AS `Sum of amount`
FROM orders 
GROUP BY `Product`
ORDER BY `Product` ASC

от която заявка получаваме следният резултат:

+----------+---------------+
| Product | Sum of amount |
+----------+---------------+
| Apple | 191257 |
| Banana | 340295 |
| Beans | 57281 |
| Broccoli | 142439 |
| Carrots | 136945 |
| Mango | 57079 |
| Orange | 104438 |
+----------+---------------+
7 rows in set (0.00 sec)

Както виждате, съвсем лесно може да филтрите, сортирате, и дори смените логиката (вместо да изкарате сумарно резултата, само бройката, средната стойност или каквото Ви дойде на ум).
Но следващата заявка е много по-интересна. Защо, ами защото за нея ще използваме prepared statements директно в MySQL (smirk), както и ще ни е нужно да си напишем една stored procedure, с която да си създадем динамично view. Разбира се, това е единствено за случаите, когато искате динамични колони (т.е. ако имате по-малко информация за извеждане, някой път ще е по-добре директно да ги изброите в един CASE примерно (think)). Та, ето структурата на нашата процедура:

BEGIN
    SET @query = NULL;
    SET SESSION group_concat_max_len = 1000000;
    SELECT
        GROUP_CONCAT(
            DISTINCT CONCAT(
                'SUM(IF(`Product` = \'', Product, '\', `Amount`, NULL)) AS `', `Product`, '`'
            )
        ) INTO @query
    FROM `orders` ORDER BY `Date` ASC;
    DROP VIEW IF EXISTS orders_view;
    SET @query = CONCAT('CREATE VIEW `orders_view` AS SELECT `Country`, ', @query, ' FROM `orders` GROUP BY `Country` ORDER BY `Country`, `Product` ASC');
    PREPARE stmt FROM @query;
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt;
END

След като създадем процедурата, и изпълним естествено, ако няма някаква грешка би трябвало да видим новосъздаденото ни View. И след като селектираме:

SELECT * FROM orders_view

+----------------+---------+----------+--------+-------+--------+-------+-------+
| Country | Carrots | Broccoli | Banana | Beans | Orange | Apple | Mango |
+----------------+---------+----------+--------+-------+--------+-------+-------+
| Australia | 8106 | 17953 | 52721 | 14433 | 8680 | 20634 | 9186 |
| Canada | NULL | 12407 | 33775 | NULL | 19929 | 24867 | 3767 |
| France | 9104 | 5341 | 36094 | 680 | 2256 | 80193 | 7388 |
| Germany | 21636 | 37197 | 39686 | 29905 | 8887 | 9082 | 8775 |
| New Zealand | NULL | 4390 | 40050 | NULL | 12010 | 10332 | NULL |
| United Kingdom | 41815 | 38436 | 42908 | 5100 | 21744 | 17534 | 5600 |
| United States | 56284 | 26715 | 95061 | 7163 | 30932 | 28615 | 22363 |
+----------------+---------+----------+--------+-------+--------+-------+-------+
7 rows in set (0.00 sec)

Та, това ще е за тази година. Навярно, наистина си е повече от работа, отколкото да си направиш същите статистики (а и отгоре) в Excel, но за всеки влак си има пътници :).