Продолжаем знакомиться с новыми возможностями в PostgreSQL 9.5.
Часть 1. INSERT… ON CONFLICT DO NOTHING/UPDATE и ROW LEVEL SECURITY
Часть 2. TABLESAMPLE
Сегодня рассмотрим множественные группировки в одном запросе. Эта возможность была описана еще в стандарте SQL-99. Её удобно применять в том случае, если вам нужно сделать несколько запросов к одной и той же таблице, отличающихся только условием в GROUP BY. Для этого модификаторы GROUPING SETS, ROLLUP, CUBE указываются в качестве элемента группировки после ключевого слова GROUP BY.
Давайте посмотрим поближе, как это работает.
Пусть у нас есть схема, в которой есть данные о платежах, каждый платеж имеет тип, город, в котором этот платеж был совершен и сумму платежа. Город обязательно имеет страну и, опционально, регион. Ниже приведены запросы для создания такой схемы и заполнения её данными.
Предположим, что мы хотим получить статистику о сумме платежей в каждом городе и в каждой стране. Раньше для этого нужно было написать запрос вида:
С версии 9.5 подобный запрос проще написать так:
Как видно, GROUPING SETS в результате запроса возвращает данные следующим образом: в каждой строке одной из колонок из перечисленных в скобках соответствует значение, в то время как остальные колонки (из списка в скобках) заполнены NULL. Колонки не перечисленные в GROUPING SETS, вычисляются как обычно.
Чтобы получить и полную сумму (без группировки) можно использовать пустую группировку — (). При пустой группировке все поля, участвующие в GROUPING SETS заполнены NULL:
Попробуем теперь получить сумму платежей в разрезе по городам, регионам и странам:
Странно, мы не делали пустую группировку, но получили строку в которой все поля NULL. На самом деле, это произошло потому, что у Москвы и Киева поле state_id не заполнено, поэтому GROUPING SETS справедливо сделал группировку и по state_id = NULL. Это легко проверить, выполнив следующий запрос:
Да, наше предположение оказалось верным и суммы совпали.
Хорошо, с тем, откуда берется эта странная строка мы разобрались, но как отличить в следующем запросе, в какой из строк полная сумма, а в какой — группировка по state_id = NULL?
Так ведь в полной сумме значение будет больше, скажете вы, и будете правы. Конечно, в данном запросе, можно понять, что строка с бóльшей суммой и есть полная сумма. Однако, если бы в таблице были не только положительные значения, но и отрицательные, определить полную сумму было бы сложнее. Ну или если использовать другую агрегатную функцию:
Какая строка соответствует средней сумме платежа в таблице, а какая — средней сумме по Киеву и Москве?
К счастью, решение есть: новая функция grouping(), с помощью неё мы можем узнать, участвует ли в данной строке определенная колонка в группировке. Если grouping(column_name) возвращает 0, то column_name участвует в группировке, если 1 — не участвует:
На самом деле, grouping возвращает битовую маску для колонок перечисленных в ней:
Так не очень понятно, приведем результат к типу bit(3):
Также можно в одном запросе использовать вместе обычную группировку и группировку с помощью GROUPING SETS:
Можно объединять колонки внутри GROUPING SETS в группы
Теперь перейдем к CUBE. CUBE — это что-то вроде множественного GROUPING SETS.
CUBE возвращает данные для всех возможных сочетаний колонок, перечисленных внутри. То есть для случая CUBE(c1, c2, c3)(где с1, c2, c3 — имена колонок) будут возвращены следующие сочетания:
(с1, null, null)
(null, c2, null)
(null, null, c3)
(c1, c2, null)
(c1, null, c3)
(null, c2, c3)
(c1, c2, c3)
(null, null, null)
Пример:
Как и в GROUPING SETS можно делать группировку внутри:
ROLLUP так же, как и CUBE — что-то вроде множественного GROUPING SETS, с тем отличием, что ROLLUP генерирует сочетания, убирая колонки по одной с конца. Таким образом, ROLLUP(c1, c2, c3, c4) вернет следующие сочетания:
(c1, c2, c3, c4)
(c1, c2, c3, null)
(c1, c2, null, null)
(c1, null, null, null)
(null, null, null, null)
Пример:
В заключение хочу сказать, что кроме удобства написания эти запросы потенциально (сам не мерил еще) работают быстрее, так как для выполнения запроса нужен всего одно сканирование таблицы вместо нескольких, которые выполняются в случае UNION ALL.
Спасибо за внимание!
Часть 1. INSERT… ON CONFLICT DO NOTHING/UPDATE и ROW LEVEL SECURITY
Часть 2. TABLESAMPLE
Сегодня рассмотрим множественные группировки в одном запросе. Эта возможность была описана еще в стандарте SQL-99. Её удобно применять в том случае, если вам нужно сделать несколько запросов к одной и той же таблице, отличающихся только условием в GROUP BY. Для этого модификаторы GROUPING SETS, ROLLUP, CUBE указываются в качестве элемента группировки после ключевого слова GROUP BY.
Давайте посмотрим поближе, как это работает.
Пусть у нас есть схема, в которой есть данные о платежах, каждый платеж имеет тип, город, в котором этот платеж был совершен и сумму платежа. Город обязательно имеет страну и, опционально, регион. Ниже приведены запросы для создания такой схемы и заполнения её данными.
Создание схемы
DROP TABLE IF EXISTS payment;
DROP TABLE IF EXISTS payment_type;
DROP TABLE IF EXISTS city;
DROP TABLE IF EXISTS state;
DROP TABLE IF EXISTS country;
CREATE TABLE country (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL
);
CREATE TABLE state (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
country_id INT REFERENCES country (id)
);
CREATE TABLE city (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
state_id INT NULL REFERENCES state (id),
country_id INT NOT NULL REFERENCES country (id),
population BIGINT NOT NULL
);
CREATE TABLE payment_type (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL
);
CREATE TABLE payment (
id BIGSERIAL PRIMARY KEY,
payment_type_id INT NOT NULL REFERENCES payment_type (id),
city_id INT NOT NULL REFERENCES city (id),
amount NUMERIC(10, 2) NOT NULL
);
INSERT INTO country (name)
VALUES ('Russia'), ('Ukraine');
INSERT INTO state (name, country_id)
VALUES ('Moscow region', 1), ('Samara region', 1), ('Kursk region', 1), ('Tatarstan', 1),
('Kiev region', 2), ('Lugansk region', 2), ('Lvov region', 2), ('Odessa region', 2);
–- Москва и Киев не являются частью Московской и Киевской области, согласно законам РФ и Украины
INSERT INTO city (name, state_id, country_id, population)
VALUES ('Moscow', NULL, 1, 12197596), ('Dubna', 1, 1, 75176),
('Samara', 2, 1, 1171820), ('Tolyatti', 2, 1, 719646), ('Syzran', 2, 1, 175222), ('Novokuybyshevsk', 2, 1, 105007),
('Kursk', 3, 1, 435117),
('Kazan', 4, 1, 1205651),
('Kiev', NULL, 2, 2888470), ('Irpen', 5, 2, 39972), ('Borispol', 5, 2, 60102), ('Belaya Tserkov', 5, 2, 211205),
('Lugansk', 6, 2, 417990), ('Lisichansk', 6, 2, 103459), ('Severodonetsk', 6, 2, 108899), ('Popasnaya', 6, 2, 21765),
('Lvov', 7, 2, 729038), ('Drogobych', 7, 2, 76866),
('Odessa', 8, 2, 1017022), ('Izmail', 8, 2, 72501);
INSERT INTO payment_type (name)
VALUES ('Online'), ('Box office'), ('Terminal');
INSERT INTO payment (payment_type_id, city_id, amount)
SELECT
ceil(random() * 3),
ceil(random() * 20),
trunc(cast(random() * 10000 AS NUMERIC), 2)
FROM generate_series(1, 10000);
Предположим, что мы хотим получить статистику о сумме платежей в каждом городе и в каждой стране. Раньше для этого нужно было написать запрос вида:
(SELECT sum(amount), c.country_id, NULL as city_id
FROM payment AS p
INNER JOIN city AS c
ON p.city_id=c.id
GROUP BY c.country_id
ORDER BY c.country_id)
UNION ALL
(SELECT sum(amount), NULL, p.city_id
FROM payment AS p
GROUP BY p.city_id
ORDER BY p.city_id)
Результат запроса
| sum | country_id | city_id |
|---|---|---|
| 19794121.93 | 1 | NULL |
| 30138426.57 | 2 | NULL |
| 2420939.72 | NULL | 1 |
| 2611787.51 | NULL | 2 |
| 2357570.54 | NULL | 3 |
| 2796471.48 | NULL | 4 |
| 2327588.11 | NULL | 5 |
| 2563701.69 | NULL | 6 |
| 2442654.38 | NULL | 7 |
| 2273408.5 | NULL | 8 |
| 2509228.24 | NULL | 9 |
| 2716771.77 | NULL | 10 |
| 2745394.99 | NULL | 11 |
| 2554721.34 | NULL | 12 |
| 2526112.36 | NULL | 13 |
| 2818708.34 | NULL | 14 |
| 2437768.84 | NULL | 15 |
| 2246483.68 | NULL | 16 |
| 2384795.14 | NULL | 17 |
| 2437849.05 | NULL | 18 |
| 2470876.07 | NULL | 19 |
| 2289716.75 | NULL | 20 |
С версии 9.5 подобный запрос проще написать так:
SELECT
sum(amount),
c.country_id,
p.city_id
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY GROUPING SETS(c.country_id, p.city_id);
Результат запроса
| sum | country_id | city_id |
|---|---|---|
| 19794121.93 | 1 | NULL |
| 30138426.57 | 2 | NULL |
| 2420939.72 | NULL | 1 |
| 2611787.51 | NULL | 2 |
| 2357570.54 | NULL | 3 |
| 2796471.48 | NULL | 4 |
| 2327588.11 | NULL | 5 |
| 2563701.69 | NULL | 6 |
| 2442654.38 | NULL | 7 |
| 2273408.5 | NULL | 8 |
| 2509228.24 | NULL | 9 |
| 2716771.77 | NULL | 10 |
| 2745394.99 | NULL | 11 |
| 2554721.34 | NULL | 12 |
| 2526112.36 | NULL | 13 |
| 2818708.34 | NULL | 14 |
| 2437768.84 | NULL | 15 |
| 2246483.68 | NULL | 16 |
| 2384795.14 | NULL | 17 |
| 2437849.05 | NULL | 18 |
| 2470876.07 | NULL | 19 |
| 2289716.75 | NULL | 20 |
Как видно, GROUPING SETS в результате запроса возвращает данные следующим образом: в каждой строке одной из колонок из перечисленных в скобках соответствует значение, в то время как остальные колонки (из списка в скобках) заполнены NULL. Колонки не перечисленные в GROUPING SETS, вычисляются как обычно.
Чтобы получить и полную сумму (без группировки) можно использовать пустую группировку — (). При пустой группировке все поля, участвующие в GROUPING SETS заполнены NULL:
SELECT
sum(amount),
p.city_id,
c.country_id
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY GROUPING SETS(p.city_id, C.country_id, ());Результат запроса
| sum | country_id | city_id |
|---|---|---|
| 19794121.93 | 1 | NULL |
| 30138426.57 | 2 | NULL |
| 49932548.5 | NULL | NULL |
| 2420939.72 | NULL | 1 |
| 2611787.51 | NULL | 2 |
| 2357570.54 | NULL | 3 |
| 2796471.48 | NULL | 4 |
| 2327588.11 | NULL | 5 |
| 2563701.69 | NULL | 6 |
| 2442654.38 | NULL | 7 |
| 2273408.5 | NULL | 8 |
| 2509228.24 | NULL | 9 |
| 2716771.77 | NULL | 10 |
| 2745394.99 | NULL | 11 |
| 2554721.34 | NULL | 12 |
| 2526112.36 | NULL | 13 |
| 2818708.34 | NULL | 14 |
| 2437768.84 | NULL | 15 |
| 2246483.68 | NULL | 16 |
| 2384795.14 | NULL | 17 |
| 2437849.05 | NULL | 18 |
| 2470876.07 | NULL | 19 |
| 2289716.75 | NULL | 20 |
Попробуем теперь получить сумму платежей в разрезе по городам, регионам и странам:
SELECT
sum(amount),
p.city_id,
c.state_id,
c.country_id
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id);
Результат запроса
| sum | city_id | state_id | country_id |
|---|---|---|---|
| 2420939.72 | 1 | NULL | NULL |
| 2611787.51 | 2 | NULL | NULL |
| 2357570.54 | 3 | NULL | NULL |
| 2796471.48 | 4 | NULL | NULL |
| 2327588.11 | 5 | NULL | NULL |
| 2563701.69 | 6 | NULL | NULL |
| 2442654.38 | 7 | NULL | NULL |
| 2273408.5 | 8 | NULL | NULL |
| 2509228.24 | 9 | NULL | NULL |
| 2716771.77 | 10 | NULL | NULL |
| 2745394.99 | 11 | NULL | NULL |
| 2554721.34 | 12 | NULL | NULL |
| 2526112.36 | 13 | NULL | NULL |
| 2818708.34 | 14 | NULL | NULL |
| 2437768.84 | 15 | NULL | NULL |
| 2246483.68 | 16 | NULL | NULL |
| 2384795.14 | 17 | NULL | NULL |
| 2437849.05 | 18 | NULL | NULL |
| 2470876.07 | 19 | NULL | NULL |
| 2289716.75 | 20 | NULL | NULL |
| 19794121.93 | NULL | NULL | 1 |
| 30138426.57 | NULL | NULL | 2 |
| 2611787.51 | NULL | 1 | NULL |
| 10045331.82 | NULL | 2 | NULL |
| 2442654.38 | NULL | 3 | NULL |
| 2273408.5 | NULL | 4 | NULL |
| 8016888.1 | NULL | 5 | NULL |
| 10029073.22 | NULL | 6 | NULL |
| 4822644.19 | NULL | 7 | NULL |
| 4760592.82 | NULL | 8 | NULL |
| 4930167.96 | NULL | NULL | NULL |
Странно, мы не делали пустую группировку, но получили строку в которой все поля NULL. На самом деле, это произошло потому, что у Москвы и Киева поле state_id не заполнено, поэтому GROUPING SETS справедливо сделал группировку и по state_id = NULL. Это легко проверить, выполнив следующий запрос:
SELECT sum(amount)
FROM payment
WHERE city_id IN (1, 9);
Результат запроса
| sum |
|---|
| 4930167.96 |
Да, наше предположение оказалось верным и суммы совпали.
Хорошо, с тем, откуда берется эта странная строка мы разобрались, но как отличить в следующем запросе, в какой из строк полная сумма, а в какой — группировка по state_id = NULL?
SELECT
sum(amount),
p.city_id,
c.state_id,
c.country_id
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ());
Результат запроса
| sum | city_id | state_id | country_id |
|---|---|---|---|
| 2420939.72 | 1 | NULL | NULL |
| 2611787.51 | 2 | NULL | NULL |
| 2357570.54 | 3 | NULL | NULL |
| 2796471.48 | 4 | NULL | NULL |
| 2327588.11 | 5 | NULL | NULL |
| 2563701.69 | 6 | NULL | NULL |
| 2442654.38 | 7 | NULL | NULL |
| 2273408.5 | 8 | NULL | NULL |
| 2509228.24 | 9 | NULL | NULL |
| 2716771.77 | 10 | NULL | NULL |
| 2745394.99 | 11 | NULL | NULL |
| 2554721.34 | 12 | NULL | NULL |
| 2526112.36 | 13 | NULL | NULL |
| 2818708.34 | 14 | NULL | NULL |
| 2437768.84 | 15 | NULL | NULL |
| 2246483.68 | 16 | NULL | NULL |
| 2384795.14 | 17 | NULL | NULL |
| 2437849.05 | 18 | NULL | NULL |
| 2470876.07 | 19 | NULL | NULL |
| 2289716.75 | 20 | NULL | NULL |
| 49932548.5 | NULL | NULL | NULL |
| 19794121.93 | NULL | NULL | 1 |
| 30138426.57 | NULL | NULL | 2 |
| 2611787.51 | NULL | 1 | NULL |
| 10045331.82 | NULL | 2 | NULL |
| 2442654.38 | NULL | 3 | NULL |
| 2273408.5 | NULL | 4 | NULL |
| 8016888.1 | NULL | 5 | NULL |
| 10029073.22 | NULL | 6 | NULL |
| 4822644.19 | NULL | 7 | NULL |
| 4760592.82 | NULL | 8 | NULL |
| 4930167.96 | NULL | NULL | NULL |
Так ведь в полной сумме значение будет больше, скажете вы, и будете правы. Конечно, в данном запросе, можно понять, что строка с бóльшей суммой и есть полная сумма. Однако, если бы в таблице были не только положительные значения, но и отрицательные, определить полную сумму было бы сложнее. Ну или если использовать другую агрегатную функцию:
SELECT
avg(amount),
p.city_id,
c.state_id,
c.country_id
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ());Результат запроса
| avg | city_id | state_id | country_id |
|---|---|---|---|
| 4841.87944 | 1 | NULL | NULL |
| 5141.313996062992126 | 2 | NULL | NULL |
| 4850.9681893004115226 | 3 | NULL | NULL |
| 4958.2827659574468085 | 4 | NULL | NULL |
| 4849.1418958333333333 | 5 | NULL | NULL |
| 5096.8224453280318091 | 6 | NULL | NULL |
| 5208.2182942430703625 | 7 | NULL | NULL |
| 4985.5449561403508772 | 8 | NULL | NULL |
| 5038.6109236947791165 | 9 | NULL | NULL |
| 5135.6744234404536862 | 10 | NULL | NULL |
| 5219.3821102661596958 | 11 | NULL | NULL |
| 4903.4958541266794626 | 12 | NULL | NULL |
| 5092.9684677419354839 | 13 | NULL | NULL |
| 5006.5867495559502664 | 14 | NULL | NULL |
| 4964.9059877800407332 | 15 | NULL | NULL |
| 4992.1859555555555556 | 16 | NULL | NULL |
| 4694.4786220472440945 | 17 | NULL | NULL |
| 5047.3065217391304348 | 18 | NULL | NULL |
| 4883.1542885375494071 | 19 | NULL | NULL |
| 4945.392548596112311 | 20 | NULL | NULL |
| 4993.25485 | NULL | NULL | NULL |
| 4990.9535879979828543 | NULL | NULL | 1 |
| 4994.7674129930394432 | NULL | NULL | 2 |
| 5141.313996062992126 | NULL | 1 | NULL |
| 4941.1371470732907034 | NULL | 2 | NULL |
| 5208.2182942430703625 | NULL | 3 | NULL |
| 4985.5449561403508772 | NULL | 4 | NULL |
| 5086.8579314720812183 | NULL | 5 | NULL |
| 5014.53661 | NULL | 6 | NULL |
| 4866.4421695257315843 | NULL | 7 | NULL |
| 4912.8924871001031992 | NULL | 8 | NULL |
| 4940.0480561122244489 | NULL | NULL | NULL |
Какая строка соответствует средней сумме платежа в таблице, а какая — средней сумме по Киеву и Москве?
К счастью, решение есть: новая функция grouping(), с помощью неё мы можем узнать, участвует ли в данной строке определенная колонка в группировке. Если grouping(column_name) возвращает 0, то column_name участвует в группировке, если 1 — не участвует:
SELECT
avg(amount),
p.city_id,
c.state_id,
c.country_id,
grouping(c.state_id)
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ());Результат запроса
| avg | city_id | state_id | country_id | grouping |
|---|---|---|---|---|
| 4841.87944 | 1 | NULL | NULL | 1 |
| 5141.313996062992126 | 2 | NULL | NULL | 1 |
| 4850.9681893004115226 | 3 | NULL | NULL | 1 |
| 4958.2827659574468085 | 4 | NULL | NULL | 1 |
| 4849.1418958333333333 | 5 | NULL | NULL | 1 |
| 5096.8224453280318091 | 6 | NULL | NULL | 1 |
| 5208.2182942430703625 | 7 | NULL | NULL | 1 |
| 4985.5449561403508772 | 8 | NULL | NULL | 1 |
| 5038.6109236947791165 | 9 | NULL | NULL | 1 |
| 5135.6744234404536862 | 10 | NULL | NULL | 1 |
| 5219.3821102661596958 | 11 | NULL | NULL | 1 |
| 4903.4958541266794626 | 12 | NULL | NULL | 1 |
| 5092.9684677419354839 | 13 | NULL | NULL | 1 |
| 5006.5867495559502664 | 14 | NULL | NULL | 1 |
| 4964.9059877800407332 | 15 | NULL | NULL | 1 |
| 4992.1859555555555556 | 16 | NULL | NULL | 1 |
| 4694.4786220472440945 | 17 | NULL | NULL | 1 |
| 5047.3065217391304348 | 18 | NULL | NULL | 1 |
| 4883.1542885375494071 | 19 | NULL | NULL | 1 |
| 4945.392548596112311 | 20 | NULL | NULL | 1 |
| 4993.25485 | NULL | NULL | NULL | 1 |
| 4990.9535879979828543 | NULL | NULL | 1 | 1 |
| 4994.7674129930394432 | NULL | NULL | 2 | 1 |
| 5141.313996062992126 | NULL | 1 | NULL | 0 |
| 4941.1371470732907034 | NULL | 2 | NULL | 0 |
| 5208.2182942430703625 | NULL | 3 | NULL | 0 |
| 4985.5449561403508772 | NULL | 4 | NULL | 0 |
| 5086.8579314720812183 | NULL | 5 | NULL | 0 |
| 5014.53661 | NULL | 6 | NULL | 0 |
| 4866.4421695257315843 | NULL | 7 | NULL | 0 |
| 4912.8924871001031992 | NULL | 8 | NULL | 0 |
| 4940.0480561122244489 | NULL | NULL | NULL | 0 |
На самом деле, grouping возвращает битовую маску для колонок перечисленных в ней:
SELECT
avg(amount),
p.city_id,
c.state_id,
c.country_id,
grouping(p.city_id, c.state_id, c.country_id)
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ());Результат запроса
| avg | city_id | state_id | country_id | grouping |
|---|---|---|---|---|
| 4841.87944 | 1 | NULL | NULL | 3 |
| 5141.313996062992126 | 2 | NULL | NULL | 3 |
| 4850.9681893004115226 | 3 | NULL | NULL | 3 |
| 4958.2827659574468085 | 4 | NULL | NULL | 3 |
| 4849.1418958333333333 | 5 | NULL | NULL | 3 |
| 5096.8224453280318091 | 6 | NULL | NULL | 3 |
| 5208.2182942430703625 | 7 | NULL | NULL | 3 |
| 4985.5449561403508772 | 8 | NULL | NULL | 3 |
| 5038.6109236947791165 | 9 | NULL | NULL | 3 |
| 5135.6744234404536862 | 10 | NULL | NULL | 3 |
| 5219.3821102661596958 | 11 | NULL | NULL | 3 |
| 4903.4958541266794626 | 12 | NULL | NULL | 3 |
| 5092.9684677419354839 | 13 | NULL | NULL | 3 |
| 5006.5867495559502664 | 14 | NULL | NULL | 3 |
| 4964.9059877800407332 | 15 | NULL | NULL | 3 |
| 4992.1859555555555556 | 16 | NULL | NULL | 3 |
| 4694.4786220472440945 | 17 | NULL | NULL | 3 |
| 5047.3065217391304348 | 18 | NULL | NULL | 3 |
| 4883.1542885375494071 | 19 | NULL | NULL | 3 |
| 4945.392548596112311 | 20 | NULL | NULL | 3 |
| 4993.25485 | NULL | NULL | NULL | 7 |
| 4990.9535879979828543 | NULL | NULL | 1 | 6 |
| 4994.7674129930394432 | NULL | NULL | 2 | 6 |
| 5141.313996062992126 | NULL | 1 | NULL | 5 |
| 4941.1371470732907034 | NULL | 2 | NULL | 5 |
| 5208.2182942430703625 | NULL | 3 | NULL | 5 |
| 4985.5449561403508772 | NULL | 4 | NULL | 5 |
| 5086.8579314720812183 | NULL | 5 | NULL | 5 |
| 5014.53661 | NULL | 6 | NULL | 5 |
| 4866.4421695257315843 | NULL | 7 | NULL | 5 |
| 4912.8924871001031992 | NULL | 8 | NULL | 5 |
| 4940.0480561122244489 | NULL | NULL | NULL | 5 |
Так не очень понятно, приведем результат к типу bit(3):
SELECT
avg(amount),
p.city_id,
c.state_id,
c.country_id,
grouping(p.city_id, c.state_id, c.country_id) :: BIT(3)
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY GROUPING SETS(p.city_id, c.state_id, c.country_id, ());Результат запроса
| avg | city_id | state_id | country_id | grouping |
|---|---|---|---|---|
| 4841.87944 | 1 | NULL | NULL | 011 |
| 5141.313996062992126 | 2 | NULL | NULL | 011 |
| 4850.9681893004115226 | 3 | NULL | NULL | 011 |
| 4958.2827659574468085 | 4 | NULL | NULL | 011 |
| 4849.1418958333333333 | 5 | NULL | NULL | 011 |
| 5096.8224453280318091 | 6 | NULL | NULL | 011 |
| 5208.2182942430703625 | 7 | NULL | NULL | 011 |
| 4985.5449561403508772 | 8 | NULL | NULL | 011 |
| 5038.6109236947791165 | 9 | NULL | NULL | 011 |
| 5135.6744234404536862 | 10 | NULL | NULL | 011 |
| 5219.3821102661596958 | 11 | NULL | NULL | 011 |
| 4903.4958541266794626 | 12 | NULL | NULL | 011 |
| 5092.9684677419354839 | 13 | NULL | NULL | 011 |
| 5006.5867495559502664 | 14 | NULL | NULL | 011 |
| 4964.9059877800407332 | 15 | NULL | NULL | 011 |
| 4992.1859555555555556 | 16 | NULL | NULL | 011 |
| 4694.4786220472440945 | 17 | NULL | NULL | 011 |
| 5047.3065217391304348 | 18 | NULL | NULL | 011 |
| 4883.1542885375494071 | 19 | NULL | NULL | 011 |
| 4945.392548596112311 | 20 | NULL | NULL | 011 |
| 4993.25485 | NULL | NULL | NULL | 111 |
| 4990.9535879979828543 | NULL | NULL | 1 | 110 |
| 4994.7674129930394432 | NULL | NULL | 2 | 110 |
| 5141.313996062992126 | NULL | 1 | NULL | 101 |
| 4941.1371470732907034 | NULL | 2 | NULL | 101 |
| 5208.2182942430703625 | NULL | 3 | NULL | 101 |
| 4985.5449561403508772 | NULL | 4 | NULL | 101 |
| 5086.8579314720812183 | NULL | 5 | NULL | 101 |
| 5014.53661 | NULL | 6 | NULL | 101 |
| 4866.4421695257315843 | NULL | 7 | NULL | 101 |
| 4912.8924871001031992 | NULL | 8 | NULL | 101 |
| 4940.0480561122244489 | NULL | NULL | NULL | 101 |
Также можно в одном запросе использовать вместе обычную группировку и группировку с помощью GROUPING SETS:
SELECT
avg(amount),
c.country_id,
p.payment_type_id,
p.city_id,
c.state_id
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY c.country_id, p.payment_type_id, GROUPING SETS(p.city_id, c.state_id, ());Результат запроса
| avg | country_id | payment_type_id | city_id | state_id |
|---|---|---|---|---|
| 5024.1955882352941176 | 1 | 1 | 1 | NULL |
| 4871.1540119760479042 | 1 | 1 | 2 | NULL |
| 4891.0804861111111111 | 1 | 1 | 3 | NULL |
| 5130.3479896907216495 | 1 | 1 | 4 | NULL |
| 4739.4527586206896552 | 1 | 1 | 5 | NULL |
| 4803.7104 | 1 | 1 | 6 | NULL |
| 5028.8194375 | 1 | 1 | 7 | NULL |
| 4903.6742 | 1 | 1 | 8 | NULL |
| 4931.2117088122605364 | 1 | 1 | NULL | NULL |
| 4407.8555056179775281 | 1 | 2 | 1 | NULL |
| 5068.5559638554216867 | 1 | 2 | 2 | NULL |
| 4812.6204093567251462 | 1 | 2 | 3 | NULL |
| 4564.1131034482758621 | 1 | 2 | 4 | NULL |
| 4963.2932530120481928 | 1 | 2 | 5 | NULL |
| 5153.3501219512195122 | 1 | 2 | 6 | NULL |
| 5446.8668965517241379 | 1 | 2 | 7 | NULL |
| 5057.8818012422360248 | 1 | 2 | 8 | NULL |
| 4917.934422641509434 | 1 | 2 | NULL | NULL |
| 5146.2380921052631579 | 1 | 3 | 1 | NULL |
| 5468.14 | 1 | 3 | 2 | NULL |
| 4855.5371929824561404 | 1 | 3 | 3 | NULL |
| 5137.8994387755102041 | 1 | 3 | 4 | NULL |
| 4831.1288757396449704 | 1 | 3 | 5 | NULL |
| 5353.0667682926829268 | 1 | 3 | 6 | NULL |
| 5172.241280487804878 | 1 | 3 | 7 | NULL |
| 4989.92 | 1 | 3 | 8 | NULL |
| 5121.7272005988023952 | 1 | 3 | NULL | NULL |
| 5224.1245625 | 2 | 1 | 9 | NULL |
| 5137.9207142857142857 | 2 | 1 | 10 | NULL |
| 5173.0209625668449198 | 2 | 1 | 11 | NULL |
| 4735.6070652173913043 | 2 | 1 | 12 | NULL |
| 5248.0194285714285714 | 2 | 1 | 13 | NULL |
| 4929.1857978723404255 | 2 | 1 | 14 | NULL |
| 5086.2014102564102564 | 2 | 1 | 15 | NULL |
| 4716.9701273885350318 | 2 | 1 | 16 | NULL |
| 4616.2608383233532934 | 2 | 1 | 17 | NULL |
| 4756.9175641025641026 | 2 | 1 | 18 | NULL |
| 4698.7787272727272727 | 2 | 1 | 19 | NULL |
| 5033.8821276595744681 | 2 | 1 | 20 | NULL |
| 4947.559810379241517 | 2 | 1 | NULL | NULL |
| 5195.4805945945945946 | 2 | 2 | 9 | NULL |
| 5213.8818617021276596 | 2 | 2 | 10 | NULL |
| 5332.2921935483870968 | 2 | 2 | 11 | NULL |
| 4946.331030303030303 | 2 | 2 | 12 | NULL |
| 5020.5288888888888889 | 2 | 2 | 13 | NULL |
| 5019.8181914893617021 | 2 | 2 | 14 | NULL |
| 4875.5393452380952381 | 2 | 2 | 15 | NULL |
| 5169.0016551724137931 | 2 | 2 | 16 | NULL |
| 4605.4601807228915663 | 2 | 2 | 17 | NULL |
| 4930.9780838323353293 | 2 | 2 | 18 | NULL |
| 4985.6017441860465116 | 2 | 2 | 19 | NULL |
| 5137.4943046357615894 | 2 | 2 | 20 | NULL |
| 5035.3225511732401398 | 2 | 2 | NULL | NULL |
| 4654.930718954248366 | 2 | 3 | 9 | NULL |
| 5048.5046242774566474 | 2 | 3 | 10 | NULL |
| 5171.3846739130434783 | 2 | 3 | 11 | NULL |
| 5042.0059302325581395 | 2 | 3 | 12 | NULL |
| 4997.4288095238095238 | 2 | 3 | 13 | NULL |
| 5071.0994117647058824 | 2 | 3 | 14 | NULL |
| 4941.5018562874251497 | 2 | 3 | 15 | NULL |
| 5110.9062837837837838 | 2 | 3 | 16 | NULL |
| 4853.5610857142857143 | 2 | 3 | 17 | NULL |
| 5451.8535625 | 2 | 3 | 18 | NULL |
| 4958.8998816568047337 | 2 | 3 | 19 | NULL |
| 4702.7937426900584795 | 2 | 3 | 20 | NULL |
| 5001.3644005920078934 | 2 | 3 | NULL | NULL |
| 4871.1540119760479042 | 1 | 1 | NULL | 1 |
| 4904.9742705167173252 | 1 | 1 | NULL | 2 |
| 5028.8194375 | 1 | 1 | NULL | 3 |
| 4903.6742 | 1 | 1 | NULL | 4 |
| 5024.1955882352941176 | 1 | 1 | NULL | NULL |
| 5068.5559638554216867 | 1 | 2 | NULL | 1 |
| 4868.3998074074074074 | 1 | 2 | NULL | 2 |
| 5446.8668965517241379 | 1 | 2 | NULL | 3 |
| 5057.8818012422360248 | 1 | 2 | NULL | 4 |
| 4407.8555056179775281 | 1 | 2 | NULL | NULL |
| 5468.14 | 1 | 3 | NULL | 1 |
| 5045.2698285714285714 | 1 | 3 | NULL | 2 |
| 5172.241280487804878 | 1 | 3 | NULL | 3 |
| 4989.92 | 1 | 3 | NULL | 4 |
| 5146.2380921052631579 | 1 | 3 | NULL | NULL |
| 5012.7593692022263451 | 2 | 1 | NULL | 5 |
| 4998.6716863905325444 | 2 | 1 | NULL | 6 |
| 4684.1941176470588235 | 2 | 1 | NULL | 7 |
| 4853.1891176470588235 | 2 | 1 | NULL | 8 |
| 5224.1245625 | 2 | 1 | NULL | NULL |
| 5163.1096456692913386 | 2 | 2 | NULL | 5 |
| 5015.9978440366972477 | 2 | 2 | NULL | 6 |
| 4768.7078978978978979 | 2 | 2 | NULL | 7 |
| 5056.6103405572755418 | 2 | 2 | NULL | 8 |
| 5195.4805945945945946 | 2 | 2 | NULL | NULL |
| 5089.1325141776937618 | 2 | 3 | NULL | 5 |
| 5029.1172686567164179 | 2 | 3 | NULL | 6 |
| 5139.3127164179104478 | 2 | 3 | NULL | 7 |
| 4830.0935588235294118 | 2 | 3 | NULL | 8 |
| 4654.930718954248366 | 2 | 3 | NULL | NULL |
Можно объединять колонки внутри GROUPING SETS в группы
SELECT
avg(amount),
c.country_id,
p.payment_type_id,
p.city_id,
c.state_id
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY GROUPING SETS((p.payment_type_id, c.country_id), ( c.state_id, p.city_id));Результат запроса
| avg | country_id | payment_type_id | city_id | state_id |
|---|---|---|---|---|
| 4931.2117088122605364 | 1 | 1 | NULL | NULL |
| 4947.559810379241517 | 2 | 1 | NULL | NULL |
| 4917.934422641509434 | 1 | 2 | NULL | NULL |
| 5035.3225511732401398 | 2 | 2 | NULL | NULL |
| 5121.7272005988023952 | 1 | 3 | NULL | NULL |
| 5001.3644005920078934 | 2 | 3 | NULL | NULL |
| 5141.313996062992126 | NULL | NULL | 2 | 1 |
| 4850.9681893004115226 | NULL | NULL | 3 | 2 |
| 4958.2827659574468085 | NULL | NULL | 4 | 2 |
| 4849.1418958333333333 | NULL | NULL | 5 | 2 |
| 5096.8224453280318091 | NULL | NULL | 6 | 2 |
| 5208.2182942430703625 | NULL | NULL | 7 | 3 |
| 4985.5449561403508772 | NULL | NULL | 8 | 4 |
| 5135.6744234404536862 | NULL | NULL | 10 | 5 |
| 5219.3821102661596958 | NULL | NULL | 11 | 5 |
| 4903.4958541266794626 | NULL | NULL | 12 | 5 |
| 5092.9684677419354839 | NULL | NULL | 13 | 6 |
| 5006.5867495559502664 | NULL | NULL | 14 | 6 |
| 4964.9059877800407332 | NULL | NULL | 15 | 6 |
| 4992.1859555555555556 | NULL | NULL | 16 | 6 |
| 4694.4786220472440945 | NULL | NULL | 17 | 7 |
| 5047.3065217391304348 | NULL | NULL | 18 | 7 |
| 4883.1542885375494071 | NULL | NULL | 19 | 8 |
| 4945.392548596112311 | NULL | NULL | 20 | 8 |
| 4841.87944 | NULL | NULL | 1 | NULL |
| 5038.6109236947791165 | NULL | NULL | 9 | NULL |
Теперь перейдем к CUBE. CUBE — это что-то вроде множественного GROUPING SETS.
CUBE возвращает данные для всех возможных сочетаний колонок, перечисленных внутри. То есть для случая CUBE(c1, c2, c3)(где с1, c2, c3 — имена колонок) будут возвращены следующие сочетания:
(с1, null, null)
(null, c2, null)
(null, null, c3)
(c1, c2, null)
(c1, null, c3)
(null, c2, c3)
(c1, c2, c3)
(null, null, null)
Пример:
SELECT
sum(amount),
p.payment_type_id,
c.country_id,
p.city_id
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY CUBE(p.payment_type_id, c.country_id, p.city_id);Результат запроса
| sum | payment_type_id | country_id | city_id |
|---|---|---|---|
| 854113.25 | 1 | 1 | 1 |
| 813482.72 | 1 | 1 | 2 |
| 704315.59 | 1 | 1 | 3 |
| 995287.51 | 1 | 1 | 4 |
| 687220.65 | 1 | 1 | 5 |
| 840649.32 | 1 | 1 | 6 |
| 804611.11 | 1 | 1 | 7 |
| 735551.13 | 1 | 1 | 8 |
| 6435231.28 | 1 | 1 | NULL |
| 835859.93 | 1 | 2 | 9 |
| 863170.68 | 1 | 2 | 10 |
| 967354.92 | 1 | 2 | 11 |
| 871351.7 | 1 | 2 | 12 |
| 918403.4 | 1 | 2 | 13 |
| 926686.93 | 1 | 2 | 14 |
| 793447.42 | 1 | 2 | 15 |
| 740564.31 | 1 | 2 | 16 |
| 770915.56 | 1 | 2 | 17 |
| 742079.14 | 1 | 2 | 18 |
| 775298.49 | 1 | 2 | 19 |
| 709777.38 | 1 | 2 | 20 |
| 9914909.86 | 1 | 2 | NULL |
| 16350141.14 | 1 | NULL | NULL |
| 784598.28 | 2 | 1 | 1 |
| 841380.29 | 2 | 1 | 2 |
| 822958.09 | 2 | 1 | 3 |
| 794155.68 | 2 | 1 | 4 |
| 823906.68 | 2 | 1 | 5 |
| 845149.42 | 2 | 1 | 6 |
| 789795.7 | 2 | 1 | 7 |
| 814318.97 | 2 | 1 | 8 |
| 6516263.11 | 2 | 1 | NULL |
| 961163.91 | 2 | 2 | 9 |
| 980209.79 | 2 | 2 | 10 |
| 826505.29 | 2 | 2 | 11 |
| 816144.62 | 2 | 2 | 12 |
| 768140.92 | 2 | 2 | 13 |
| 943725.82 | 2 | 2 | 14 |
| 819090.61 | 2 | 2 | 15 |
| 749505.24 | 2 | 2 | 16 |
| 764506.39 | 2 | 2 | 17 |
| 823473.34 | 2 | 2 | 18 |
| 857523.5 | 2 | 2 | 19 |
| 775761.64 | 2 | 2 | 20 |
| 10085751.07 | 2 | 2 | NULL |
| 16602014.18 | 2 | NULL | NULL |
| 782228.19 | 3 | 1 | 1 |
| 956924.5 | 3 | 1 | 2 |
| 830296.86 | 3 | 1 | 3 |
| 1007028.29 | 3 | 1 | 4 |
| 816460.78 | 3 | 1 | 5 |
| 877902.95 | 3 | 1 | 6 |
| 848247.57 | 3 | 1 | 7 |
| 723538.4 | 3 | 1 | 8 |
| 6842627.54 | 3 | 1 | NULL |
| 712204.4 | 3 | 2 | 9 |
| 873391.3 | 3 | 2 | 10 |
| 951534.78 | 3 | 2 | 11 |
| 867225.02 | 3 | 2 | 12 |
| 839568.04 | 3 | 2 | 13 |
| 948295.59 | 3 | 2 | 14 |
| 825230.81 | 3 | 2 | 15 |
| 756414.13 | 3 | 2 | 16 |
| 849373.19 | 3 | 2 | 17 |
| 872296.57 | 3 | 2 | 18 |
| 838054.08 | 3 | 2 | 19 |
| 804177.73 | 3 | 2 | 20 |
| 10137765.64 | 3 | 2 | NULL |
| 16980393.18 | 3 | NULL | NULL |
| 49932548.5 | NULL | NULL | NULL |
| 854113.25 | 1 | NULL | 1 |
| 784598.28 | 2 | NULL | 1 |
| 782228.19 | 3 | NULL | 1 |
| 2420939.72 | NULL | NULL | 1 |
| 813482.72 | 1 | NULL | 2 |
| 841380.29 | 2 | NULL | 2 |
| 956924.5 | 3 | NULL | 2 |
| 2611787.51 | NULL | NULL | 2 |
| 704315.59 | 1 | NULL | 3 |
| 822958.09 | 2 | NULL | 3 |
| 830296.86 | 3 | NULL | 3 |
| 2357570.54 | NULL | NULL | 3 |
| 995287.51 | 1 | NULL | 4 |
| 794155.68 | 2 | NULL | 4 |
| 1007028.29 | 3 | NULL | 4 |
| 2796471.48 | NULL | NULL | 4 |
| 687220.65 | 1 | NULL | 5 |
| 823906.68 | 2 | NULL | 5 |
| 816460.78 | 3 | NULL | 5 |
| 2327588.11 | NULL | NULL | 5 |
| 840649.32 | 1 | NULL | 6 |
| 845149.42 | 2 | NULL | 6 |
| 877902.95 | 3 | NULL | 6 |
| 2563701.69 | NULL | NULL | 6 |
| 804611.11 | 1 | NULL | 7 |
| 789795.7 | 2 | NULL | 7 |
| 848247.57 | 3 | NULL | 7 |
| 2442654.38 | NULL | NULL | 7 |
| 735551.13 | 1 | NULL | 8 |
| 814318.97 | 2 | NULL | 8 |
| 723538.4 | 3 | NULL | 8 |
| 2273408.5 | NULL | NULL | 8 |
| 835859.93 | 1 | NULL | 9 |
| 961163.91 | 2 | NULL | 9 |
| 712204.4 | 3 | NULL | 9 |
| 2509228.24 | NULL | NULL | 9 |
| 863170.68 | 1 | NULL | 10 |
| 980209.79 | 2 | NULL | 10 |
| 873391.3 | 3 | NULL | 10 |
| 2716771.77 | NULL | NULL | 10 |
| 967354.92 | 1 | NULL | 11 |
| 826505.29 | 2 | NULL | 11 |
| 951534.78 | 3 | NULL | 11 |
| 2745394.99 | NULL | NULL | 11 |
| 871351.7 | 1 | NULL | 12 |
| 816144.62 | 2 | NULL | 12 |
| 867225.02 | 3 | NULL | 12 |
| 2554721.34 | NULL | NULL | 12 |
| 918403.4 | 1 | NULL | 13 |
| 768140.92 | 2 | NULL | 13 |
| 839568.04 | 3 | NULL | 13 |
| 2526112.36 | NULL | NULL | 13 |
| 926686.93 | 1 | NULL | 14 |
| 943725.82 | 2 | NULL | 14 |
| 948295.59 | 3 | NULL | 14 |
| 2818708.34 | NULL | NULL | 14 |
| 793447.42 | 1 | NULL | 15 |
| 819090.61 | 2 | NULL | 15 |
| 825230.81 | 3 | NULL | 15 |
| 2437768.84 | NULL | NULL | 15 |
| 740564.31 | 1 | NULL | 16 |
| 749505.24 | 2 | NULL | 16 |
| 756414.13 | 3 | NULL | 16 |
| 2246483.68 | NULL | NULL | 16 |
| 770915.56 | 1 | NULL | 17 |
| 764506.39 | 2 | NULL | 17 |
| 849373.19 | 3 | NULL | 17 |
| 2384795.14 | NULL | NULL | 17 |
| 742079.14 | 1 | NULL | 18 |
| 823473.34 | 2 | NULL | 18 |
| 872296.57 | 3 | NULL | 18 |
| 2437849.05 | NULL | NULL | 18 |
| 775298.49 | 1 | NULL | 19 |
| 857523.5 | 2 | NULL | 19 |
| 838054.08 | 3 | NULL | 19 |
| 2470876.07 | NULL | NULL | 19 |
| 709777.38 | 1 | NULL | 20 |
| 775761.64 | 2 | NULL | 20 |
| 804177.73 | 3 | NULL | 20 |
| 2289716.75 | NULL | NULL | 20 |
| 2420939.72 | NULL | 1 | 1 |
| 2611787.51 | NULL | 1 | 2 |
| 2357570.54 | NULL | 1 | 3 |
| 2796471.48 | NULL | 1 | 4 |
| 2327588.11 | NULL | 1 | 5 |
| 2563701.69 | NULL | 1 | 6 |
| 2442654.38 | NULL | 1 | 7 |
| 2273408.5 | NULL | 1 | 8 |
| 19794121.93 | NULL | 1 | NULL |
| 2509228.24 | NULL | 2 | 9 |
| 2716771.77 | NULL | 2 | 10 |
| 2745394.99 | NULL | 2 | 11 |
| 2554721.34 | NULL | 2 | 12 |
| 2526112.36 | NULL | 2 | 13 |
| 2818708.34 | NULL | 2 | 14 |
| 2437768.84 | NULL | 2 | 15 |
| 2246483.68 | NULL | 2 | 16 |
| 2384795.14 | NULL | 2 | 17 |
| 2437849.05 | NULL | 2 | 18 |
| 2470876.07 | NULL | 2 | 19 |
| 2289716.75 | NULL | 2 | 20 |
| 30138426.57 | NULL | 2 | NULL |
Как и в GROUPING SETS можно делать группировку внутри:
SELECT
sum(amount),
p.payment_type_id,
c.country_id,
p.city_id
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY CUBE(p.payment_type_id, (c.country_id, p.city_id));Результат запроса
| sum | payment_type_id | country_id | city_id |
|---|---|---|---|
| 854113.25 | 1 | 1 | 1 |
| 813482.72 | 1 | 1 | 2 |
| 704315.59 | 1 | 1 | 3 |
| 995287.51 | 1 | 1 | 4 |
| 687220.65 | 1 | 1 | 5 |
| 840649.32 | 1 | 1 | 6 |
| 804611.11 | 1 | 1 | 7 |
| 735551.13 | 1 | 1 | 8 |
| 835859.93 | 1 | 2 | 9 |
| 863170.68 | 1 | 2 | 10 |
| 967354.92 | 1 | 2 | 11 |
| 871351.7 | 1 | 2 | 12 |
| 918403.4 | 1 | 2 | 13 |
| 926686.93 | 1 | 2 | 14 |
| 793447.42 | 1 | 2 | 15 |
| 740564.31 | 1 | 2 | 16 |
| 770915.56 | 1 | 2 | 17 |
| 742079.14 | 1 | 2 | 18 |
| 775298.49 | 1 | 2 | 19 |
| 709777.38 | 1 | 2 | 20 |
| 16350141.14 | 1 | NULL | NULL |
| 784598.28 | 2 | 1 | 1 |
| 841380.29 | 2 | 1 | 2 |
| 822958.09 | 2 | 1 | 3 |
| 794155.68 | 2 | 1 | 4 |
| 823906.68 | 2 | 1 | 5 |
| 845149.42 | 2 | 1 | 6 |
| 789795.7 | 2 | 1 | 7 |
| 814318.97 | 2 | 1 | 8 |
| 961163.91 | 2 | 2 | 9 |
| 980209.79 | 2 | 2 | 10 |
| 826505.29 | 2 | 2 | 11 |
| 816144.62 | 2 | 2 | 12 |
| 768140.92 | 2 | 2 | 13 |
| 943725.82 | 2 | 2 | 14 |
| 819090.61 | 2 | 2 | 15 |
| 749505.24 | 2 | 2 | 16 |
| 764506.39 | 2 | 2 | 17 |
| 823473.34 | 2 | 2 | 18 |
| 857523.5 | 2 | 2 | 19 |
| 775761.64 | 2 | 2 | 20 |
| 16602014.18 | 2 | NULL | NULL |
| 782228.19 | 3 | 1 | 1 |
| 956924.5 | 3 | 1 | 2 |
| 830296.86 | 3 | 1 | 3 |
| 1007028.29 | 3 | 1 | 4 |
| 816460.78 | 3 | 1 | 5 |
| 877902.95 | 3 | 1 | 6 |
| 848247.57 | 3 | 1 | 7 |
| 723538.4 | 3 | 1 | 8 |
| 712204.4 | 3 | 2 | 9 |
| 873391.3 | 3 | 2 | 10 |
| 951534.78 | 3 | 2 | 11 |
| 867225.02 | 3 | 2 | 12 |
| 839568.04 | 3 | 2 | 13 |
| 948295.59 | 3 | 2 | 14 |
| 825230.81 | 3 | 2 | 15 |
| 756414.13 | 3 | 2 | 16 |
| 849373.19 | 3 | 2 | 17 |
| 872296.57 | 3 | 2 | 18 |
| 838054.08 | 3 | 2 | 19 |
| 804177.73 | 3 | 2 | 20 |
| 16980393.18 | 3 | NULL | NULL |
| 49932548.5 | NULL | NULL | NULL |
| 2420939.72 | NULL | 1 | 1 |
| 2611787.51 | NULL | 1 | 2 |
| 2357570.54 | NULL | 1 | 3 |
| 2796471.48 | NULL | 1 | 4 |
| 2327588.11 | NULL | 1 | 5 |
| 2563701.69 | NULL | 1 | 6 |
| 2442654.38 | NULL | 1 | 7 |
| 2273408.5 | NULL | 1 | 8 |
| 2509228.24 | NULL | 2 | 9 |
| 2716771.77 | NULL | 2 | 10 |
| 2745394.99 | NULL | 2 | 11 |
| 2554721.34 | NULL | 2 | 12 |
| 2526112.36 | NULL | 2 | 13 |
| 2818708.34 | NULL | 2 | 14 |
| 2437768.84 | NULL | 2 | 15 |
| 2246483.68 | NULL | 2 | 16 |
| 2384795.14 | NULL | 2 | 17 |
| 2437849.05 | NULL | 2 | 18 |
| 2470876.07 | NULL | 2 | 19 |
| 2289716.75 | NULL | 2 | 20 |
ROLLUP так же, как и CUBE — что-то вроде множественного GROUPING SETS, с тем отличием, что ROLLUP генерирует сочетания, убирая колонки по одной с конца. Таким образом, ROLLUP(c1, c2, c3, c4) вернет следующие сочетания:
(c1, c2, c3, c4)
(c1, c2, c3, null)
(c1, c2, null, null)
(c1, null, null, null)
(null, null, null, null)
Пример:
SELECT
sum(amount),
p.payment_type_id,
c.country_id,
p.city_id
FROM payment AS p
INNER JOIN city AS c
ON p.city_id = c.id
GROUP BY ROLLUP(p.payment_type_id, c.country_id, p.city_id);Результат запроса
| sum | payment_type_id | country_id | city_id |
|---|---|---|---|
| 854113.25 | 1 | 1 | 1 |
| 813482.72 | 1 | 1 | 2 |
| 704315.59 | 1 | 1 | 3 |
| 995287.51 | 1 | 1 | 4 |
| 687220.65 | 1 | 1 | 5 |
| 840649.32 | 1 | 1 | 6 |
| 804611.11 | 1 | 1 | 7 |
| 735551.13 | 1 | 1 | 8 |
| 6435231.28 | 1 | 1 | NULL |
| 835859.93 | 1 | 2 | 9 |
| 863170.68 | 1 | 2 | 10 |
| 967354.92 | 1 | 2 | 11 |
| 871351.7 | 1 | 2 | 12 |
| 918403.4 | 1 | 2 | 13 |
| 926686.93 | 1 | 2 | 14 |
| 793447.42 | 1 | 2 | 15 |
| 740564.31 | 1 | 2 | 16 |
| 770915.56 | 1 | 2 | 17 |
| 742079.14 | 1 | 2 | 18 |
| 775298.49 | 1 | 2 | 19 |
| 709777.38 | 1 | 2 | 20 |
| 9914909.86 | 1 | 2 | NULL |
| 16350141.14 | 1 | NULL | NULL |
| 784598.28 | 2 | 1 | 1 |
| 841380.29 | 2 | 1 | 2 |
| 822958.09 | 2 | 1 | 3 |
| 794155.68 | 2 | 1 | 4 |
| 823906.68 | 2 | 1 | 5 |
| 845149.42 | 2 | 1 | 6 |
| 789795.7 | 2 | 1 | 7 |
| 814318.97 | 2 | 1 | 8 |
| 6516263.11 | 2 | 1 | NULL |
| 961163.91 | 2 | 2 | 9 |
| 980209.79 | 2 | 2 | 10 |
| 826505.29 | 2 | 2 | 11 |
| 816144.62 | 2 | 2 | 12 |
| 768140.92 | 2 | 2 | 13 |
| 943725.82 | 2 | 2 | 14 |
| 819090.61 | 2 | 2 | 15 |
| 749505.24 | 2 | 2 | 16 |
| 764506.39 | 2 | 2 | 17 |
| 823473.34 | 2 | 2 | 18 |
| 857523.5 | 2 | 2 | 19 |
| 775761.64 | 2 | 2 | 20 |
| 10085751.07 | 2 | 2 | NULL |
| 16602014.18 | 2 | NULL | NULL |
| 782228.19 | 3 | 1 | 1 |
| 956924.5 | 3 | 1 | 2 |
| 830296.86 | 3 | 1 | 3 |
| 1007028.29 | 3 | 1 | 4 |
| 816460.78 | 3 | 1 | 5 |
| 877902.95 | 3 | 1 | 6 |
| 848247.57 | 3 | 1 | 7 |
| 723538.4 | 3 | 1 | 8 |
| 6842627.54 | 3 | 1 | NULL |
| 712204.4 | 3 | 2 | 9 |
| 873391.3 | 3 | 2 | 10 |
| 951534.78 | 3 | 2 | 11 |
| 867225.02 | 3 | 2 | 12 |
| 839568.04 | 3 | 2 | 13 |
| 948295.59 | 3 | 2 | 14 |
| 825230.81 | 3 | 2 | 15 |
| 756414.13 | 3 | 2 | 16 |
| 849373.19 | 3 | 2 | 17 |
| 872296.57 | 3 | 2 | 18 |
| 838054.08 | 3 | 2 | 19 |
| 804177.73 | 3 | 2 | 20 |
| 10137765.64 | 3 | 2 | NULL |
| 16980393.18 | 3 | NULL | NULL |
| 49932548.5 | NULL | NULL | NULL |
В заключение хочу сказать, что кроме удобства написания эти запросы потенциально (сам не мерил еще) работают быстрее, так как для выполнения запроса нужен всего одно сканирование таблицы вместо нескольких, которые выполняются в случае UNION ALL.
Спасибо за внимание!
