Если возможностей ROLLUP
и CUBE
не хватает, то можно наборы группирования задать самому через GROUPING SETS
.
Синтаксис:
GROUP BY GROUPING SETS ((выражение_11, выражение_12, ... выражение_1n), ... (выражение_m1, выражение_m2, ... выражение_mk))
В каждом внутреннем списке GROUPING SETS
могут задаваться ноль или более столбцов или выражений, которые воспринимаются так же, как если бы они были непосредственно записаны в предложении GROUP BY
. Пустой набор группировки означает, что все строки сводятся к одной группе (которая выводится, даже если входных строк нет), как для агрегатных функций без предложения GROUP BY
.
Значения полей из GROUP BY
, по которым не производилась группировка, в результате выполнения запроса будут равны NULL
.
Рассмотрим пример, аналогичный заданию CUBE
. Получим минимальную и максимальную цены на товары в городах с идентификаторами 2 и 4 по:
- только по городу;
- только по категории товара;
- по всем строкам.
Исходные данные:
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, 4)
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 | 4 | 6 | 20000.00 |
6 | 4 | 8 | 37000.00 |
7 | 4 | 9 | 75600.00 |
Добавим GROUPING SETS
:
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, 4)
GROUP BY GROUPING SETS ((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 | NULL | 17800.00 | 23500.00 |
2 | 4 | NULL | 20000.00 | 75600.00 |
3 | NULL | 6 | 20000.00 | 23500.00 |
4 | NULL | 7 | 17800.00 | 17800.00 |
5 | NULL | 8 | 37000.00 | 37000.00 |
6 | NULL | 9 | 75600.00 | 75600.00 |
7 | NULL | NULL | 17800.00 | 75600.00 |
Повнимательнее рассмотрим результат.
Строки
# | city_id | category_id | price_min | price_max |
---|---|---|---|---|
1 | 2 | NULL | 17800.00 | 23500.00 |
2 | 4 | NULL | 20000.00 | 75600.00 |
получены в результате GROUP BY sa.city_id
(итоги по городам).
Строки
# | city_id | category_id | price_min | price_max |
---|---|---|---|---|
3 | NULL | 6 | 20000.00 | 23500.00 |
4 | NULL | 7 | 17800.00 | 17800.00 |
5 | NULL | 8 | 37000.00 | 37000.00 |
6 | NULL | 9 | 75600.00 | 75600.00 |
получены в результате GROUP BY p.category_id
(итоги по категориям).
Строка
# | city_id | category_id | price_min | price_max |
---|---|---|---|---|
7 | NULL | NULL | 17800.00 | 75600.00 |
является результатом вычисления итогов по всем строкам.