Продвинутая группировка: ROLLUP, CUBE и разворот данных
Превратите плоский детальный запрос в отчёт с промежуточными итогами, общим итогом и колонками, читаемыми с первого взгляда
Главная идея: Обычный
GROUP BY даёт по одной строке на группу и ничего больше. Реальным отчётам нужны ещё промежуточные итоги, общий итог и часто данные, повёрнутые так, чтобы категории шли поверху как колонки. ROLLUP и CUBE формируют дополнительные строки-суперагрегаты за один проход, GROUPING() позволяет их различить, а условная агрегация (SUM(CASE ...)) разворачивает строки в колонки. В этом уроке все четыре приёма связаны воедино.
К концу урока вы сможете:
- добавлять строки промежуточных и общих итогов через
GROUP BY ... WITH ROLLUP, - использовать
GROUPING(), чтобы отличить настоящий NULL от маркера итога и подписать строки, - рассуждать о многомерных сводках и понимать, почему в ROLLUP важен порядок колонок,
- получать полную кросс-таблицу через
CUBE/GROUPING SETSтам, где диалект это поддерживает, и эмулировать её в MySQL, - разворачивать строки в колонки условной агрегацией.
1. Наша учебная схема
Всё, что ниже, работает с одной крошечной таблицей продаж. Создайте её один раз и держите в уме — каждый результат в этом уроке получен из этих восьми строк.
CREATE TABLE sales (
id INT PRIMARY KEY,
region VARCHAR(20),
product VARCHAR(20),
quarter VARCHAR(2),
amount DECIMAL(10,2)
);
INSERT INTO sales (id, region, product, quarter, amount) VALUES
(1, 'North', 'Widget', 'Q1', 100),
(2, 'North', 'Gadget', 'Q1', 150),
(3, 'North', 'Widget', 'Q2', 120),
(4, 'South', 'Widget', 'Q1', 90),
(5, 'South', 'Gadget', 'Q1', 70),
(6, 'South', 'Gadget', 'Q2', 110),
(7, 'North', 'Gadget', 'Q2', 130),
(8, 'South', 'Widget', 'Q2', 80);Исходные строки:
| id | region | product | quarter | amount |
|---|---|---|---|---|
| 1 | North | Widget | Q1 | 100 |
| 2 | North | Gadget | Q1 | 150 |
| 3 | North | Widget | Q2 | 120 |
| 4 | South | Widget | Q1 | 90 |
| 5 | South | Gadget | Q1 | 70 |
| 6 | South | Gadget | Q2 | 110 |
| 7 | North | Gadget | Q2 | 130 |
| 8 | South | Widget | Q2 | 80 |
Суммарные продажи равны 850. Запомните это число: именно его обязан воспроизвести общий итог каждого ROLLUP и CUBE ниже.