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.

    Спасибо за внимание!
    Поделиться публикацией
    Похожие публикации
    Реклама помогает поддерживать и развивать наши сервисы

    Подробнее
    Реклама
    Комментарии 0

    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.