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.
Давайте посмотрим поближе, как это работает.

Пусть у нас есть схема, в которой есть данные о платежах, каждый платеж имеет тип, город, в котором этот платеж был совершен и сумму платежа. Город обязательно имеет страну и, опционально, регион. Ниже приведены запросы для создания такой схемы и заполнения её данными.

Предположим, что мы хотим получить статистику о сумме платежей в каждом городе и в каждой стране. Раньше для этого нужно было написать запрос вида:

(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)

С версии 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);

Как видно, 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, ());

Попробуем теперь получить сумму платежей в разрезе по городам, регионам и странам:

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);

Странно, мы не делали пустую группировку, но получили строку в которой все поля NULL. На самом деле, это произошло потому, что у Москвы и Киева поле state_id не заполнено, поэтому GROUPING SETS справедливо сделал группировку и по state_id = NULL. Это легко проверить, выполнив следующий запрос:

SELECT sum(amount)
FROM payment
WHERE city_id IN (1, 9);

Да, наше предположение оказалось верным и суммы совпали.

Хорошо, с тем, откуда берется эта странная строка мы разобрались, но как отличить в следующем запросе, в какой из строк полная сумма, а в какой — группировка по 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, ());

Так ведь в полной сумме значение будет больше, скажете вы, и будете правы. Конечно, в данном запросе, можно понять, что строка с бóльшей суммой и есть полная сумма. Однако, если бы в таблице были не только положительные значения, но и отрицательные, определить полную сумму было бы сложнее. Ну или если использовать другую агрегатную функцию:

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, ());

Какая строка соответствует средней сумме платежа в таблице, а какая — средней сумме по Киеву и Москве?
К счастью, решение есть: новая функция 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, ());

На самом деле, 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, ());

Так не очень понятно, приведем результат к типу 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, ());

Также можно в одном запросе использовать вместе обычную группировку и группировку с помощью 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, ());

Можно объединять колонки внутри 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));

Теперь перейдем к 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);

Как и в 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));

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);

В заключение хочу сказать, что кроме удобства написания эти запросы потенциально (сам не мерил еще) работают быстрее, так как для выполнения запроса нужен всего одно сканирование таблицы вместо нескольких, которые выполняются в случае UNION ALL.

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