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

Предполагам, почти на всеки му се е налагало да си прави различни сумарни сметки, за което 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, но за всеки влак си има пътници :).