Продолжаем знакомиться с новыми возможностями в 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.
Давайте посмотрим поближе, как это работает.
Пусть у нас есть схема, в которой есть данные о платежах, каждый платеж имеет тип, город, в котором этот платеж был совершен и сумму платежа. Город обязательно имеет страну и, опционально, регион. Ниже приведены запросы для создания такой схемы и заполнения её данными.
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.
Спасибо за внимание!
ссылка на оригинал статьи http://habrahabr.ru/post/269849/
Добавить комментарий