При проведении аналитики часто требуется посмотреть на статистические показатели в разных разрезах. Например, для анализа средней зарплаты интересно посмотреть на среднюю зарплату по каждому подразделению и по всей организации в целом. Для решения этой задачи SQL поддерживает наборы группирования, реализованные через ROLLUP, CUBE, GROUPING SETS.
Данные, выбранные предложениями FROM и WHERE, группируются отдельно для каждого заданного набора группирования, затем для каждой группы вычисляются агрегатные функции как для простых предложений GROUP BY, и в конце результаты объединяются.
Конструкция ROLLUP в цикле выполняет группировку по подспискам переданного списка выражений, на каждой итерации убирая по одному выражению с конца списка, включая пустой список (вычисление агрегатных функций по всем строкам).
Например, для GROUP BY ROLLUP (выражение1, выражение2, выражение3) результатом запроса будет объединение результатов GROUP BY по
- выражение1, выражение2, выражение3;
- выражение1, выражение2;
- выражение1;
- все строки выборки.
Синтаксис ROLLUP:
GROUP BY ROLLUP (выражение1, выражение2, ... , выражениеN)
Рассмотрим пример. Получим минимальную и максимальную цены на товары в городах с идентификаторами 2 и 3 в следующих разрезах:
- по каждой категории товаров в городе;
- по всем магазинам города;
- по всем категориям товаров всех городов.
Для начала выведем исходные данные:
SELECT sa.city_id,
p.category_id,
pp.price
FROM product_price pp
JOIN product p
ON p.product_id = pp.product_id
JOIN store_address sa
ON sa.store_id = pp.store_id
WHERE sa.city_id IN (2, 3)
ORDER BY sa.city_id,
p.category_id,
pp.price
| city_id | category_id | price | |
|---|---|---|---|
| 1 | 2 | 6 | 21500.00 |
| 2 | 2 | 6 | 22900.00 |
| 3 | 2 | 6 | 23500.00 |
| 4 | 2 | 7 | 17800.00 |
| 5 | 3 | 3 | 10500.00 |
Воспользуемся ROLLUP:
SELECT sa.city_id,
p.category_id,
min(pp.price) AS price_min,
max(pp.price) AS price_max
FROM product_price pp
JOIN product p
ON p.product_id = pp.product_id
JOIN store_address sa
ON sa.store_id = pp.store_id
WHERE sa.city_id IN (2, 3)
GROUP BY ROLLUP (sa.city_id, p.category_id)
ORDER BY sa.city_id NULLS LAST,
p.category_id NULLS LAST
| city_id | category_id | price_min | price_max | |
|---|---|---|---|---|
| 1 | 2 | 6 | 21500.00 | 23500.00 |
| 2 | 2 | 7 | 17800.00 | 17800.00 |
| 3 | 2 | NULL | 17800.00 | 23500.00 |
| 4 | 3 | 3 | 10500.00 | 10500.00 |
| 5 | 3 | NULL | 10500.00 | 10500.00 |
| 6 | NULL | NULL | 10500.00 | 23500.00 |
Обрати внимание, что значения полей из GROUP BY, по которым не производилась группировка, равны NULL. Таким образом, результаты группировки по sa.city_id и p.category_id представлены в строках 1, 2 и 4. Результаты группировки по sa.city_id представлены в строках 3 и 5. Строка 6 является вычислением агрегатных функций по всему набору данных.