...

пятница, 30 октября 2015 г.

PostgreSQL 9.5: что нового? Часть 3. GROUPING SETS, CUBE, ROLLUP

Продолжаем знакомиться с новыми возможностями в 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.

Спасибо за внимание!

This entry passed through the Full-Text RSS service - if this is your content and you're reading it on someone else's site, please read the FAQ at http://ift.tt/jcXqJW.

на
Ярлыки: ,

Комментариев нет:

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)