НАБОРЫ ГРУППИРОВОК

GROUPING SETS, ROLLUP и CUBE могут быть использованы в предложении GROUP BY для выполнения группировки по нескольким измерениям в одном запросе. Обратите внимание, что этот синтаксис несовместим с GROUP BY ALL.

Примеры

Вычислить средний доход по предоставленным четырём измерениям:

-- the syntax () denotes the empty set (i.e., computing an ungrouped aggregate)
SELECT city, street_name, avg(income)
FROM addresses
GROUP BY GROUPING SETS ((city, street_name), (city), (street_name), ());

Вычислить средний доход по тем же измерениям:

SELECT city, street_name, avg(income)
FROM addresses
GROUP BY CUBE (city, street_name);

Вычислить средний доход по измерениям (city, street_name), (city) и ():

SELECT city, street_name, avg(income)
FROM addresses
GROUP BY ROLLUP (city, street_name);

Описание

GROUPING SETS выполняют одну и ту же агрегацию по различным GROUP BY clauses в одном запросе.

CREATE TABLE students (course VARCHAR, type VARCHAR);
INSERT INTO students (course, type)
VALUES
    ('CS', 'Bachelor'), ('CS', 'Bachelor'), ('CS', 'PhD'), ('Math', 'Masters'),
    ('CS', NULL), ('CS', NULL), ('Math', NULL);

SELECT course, type, count(*)
FROM students
GROUP BY GROUPING SETS ((course, type), course, type, ());

В запросе выше мы группируем по четырём различным наборам: course, type, course, type и () (пустая группа). Результат содержит NULL для группы, которая не входит в набор группировок для результата, то есть запрос выше эквивалентен следующему оператору UNION:

Группировка по курсу и типу:

SELECT course, type, count(*)
FROM students
GROUP BY course, type
UNION ALL

Группировка по типу:

SELECT NULL AS course, type, count(*)
FROM students
GROUP BY type
UNION ALL

Группировка по курсу:

SELECT course, NULL AS type, count(*)
FROM students
GROUP BY course
UNION ALL

Группировка ни по чему:

SELECT NULL AS course, NULL AS type, count(*)
FROM students;

CUBE и ROLLUP являются синтаксическим сахаром для лёгкой генерации часто используемых наборов группировок.

Предложение ROLLUP создаст все «подгруппы» набора группировок, например, ROLLUP (country, city, zip) создаёт наборы группировок (country, city, zip), (country, city), (country), (). Это может быть полезно для получения различных уровней детализации в предложении group by. Это создаёт n+1 наборов группировок, где n — количество членов в предложении ROLLUP.

CUBE создаёт наборы группировок для всех комбинаций входов, например, CUBE (country, city, zip) создаст (country, city, zip), (country, city), (country, zip), (city, zip), (country), (city), (zip), (). Это создаёт 2^n наборов группировок.

Определение наборов группировок с помощью GROUPING_ID()

Строки супер-агрегации, сгенерированные GROUPING SETS, ROLLUP и CUBE, часто могут быть идентифицированы по значениям NULL для соответствующего столбца в группировке. Но если столбцы, используемые в группировке, сами могут содержать фактические значения NULL, то может быть сложно отличить, является ли значение в наборе результатов «настоящим» значением NULL, поступающим из данных самих по себе, или значением NULL, сгенерированным конструкцией группировки. Функция GROUPING_ID() или GROUPING() разработана для идентификации тех групп, которые сгенерировали строки супер-агрегации в наборе результатов.

GROUPING_ID() — это функция агрегации, которая принимает выражения столбцов, составляющие группировку(и). Она возвращает значение BIGINT. Значение возврата равно 0 для строк, которые не являются строками супер-агрегации. Но для строк супер-агрегации она возвращает целочисленное значение, которое идентифицирует комбинацию выражений, составляющих группу, для которой генерируется супер-агрегат. На этом этапе пример может оказаться полезным. Рассмотрим следующий запрос:

WITH days AS (
    SELECT
        year("generate_series")    AS y,
        quarter("generate_series") AS q,
        month("generate_series")   AS m
    FROM generate_series(DATE '2023-01-01', DATE '2023-12-31', INTERVAL 1 DAY)
)
SELECT y, q, m, GROUPING_ID(y, q, m) AS "grouping_id()"
FROM days
GROUP BY GROUPING SETS (
    (y, q, m),
    (y, q),
    (y),
    ()
)
ORDER BY y, q, m;

Вот результаты:

В этом примере, наименьший уровень группировки находится на уровне месяца, определённом набором группировок (y, q, m). Строки результата, соответствующие этому уровню, — это просто строки агрегата, и функция GROUPING_ID(y, q, m) возвращает 0 для этих строк. Набор группировок (y, q) приводит к строкам супер-агрегации по уровню месяца, оставляя значение NULL для столбца m, и для которого GROUPING_ID(y, q, m) возвращает 1. Набор группировок (y) приводит к строкам супер-агрегации по уровню квартала, оставляя значения NULL для столбцов m и q, для которых GROUPING_ID(y, q, m) возвращает 3. Наконец, набор группировок () приводит к одной строке супер-агрегации для всего набора результатов, оставляя значения NULL для y, q и m, и для которых GROUPING_ID(y, q, m) возвращает 7.

Для понимания взаимосвязи между возвращаемым значением и набором группировок можно представить себе GROUPING_ID(y, q, m) как запись в битовом поле, где первый бит соответствует последнему выражению, переданному в GROUPING_ID(), второй бит — выражению, перед последним, переданному в GROUPING_ID(), и так далее. Это может стать яснее, если преобразовать GROUPING_ID() в BIT:

WITH days AS (
    SELECT
        year("generate_series")    AS y,
        quarter("generate_series") AS q,
        month("generate_series")   AS m
    FROM generate_series(DATE '2023-01-01', DATE '2023-12-31', INTERVAL 1 DAY)
)
SELECT
    y, q, m,
    GROUPING_ID(y, q, m) AS "grouping_id(y, q, m)",
    right(GROUPING_ID(y, q, m)::BIT::VARCHAR, 3) AS "y_q_m_bits"
FROM days
GROUP BY GROUPING SETS (
    (y, q, m),
    (y, q),
    (y),
    ()
)
ORDER BY y, q, m;

Что возвращает следующие результаты:

Обратите внимание, что количество выражений, переданных в GROUPING_ID(), или порядок их передачи не зависит от фактических определений групп, отображаемых в GROUPING SETS-клаузе (или групп, подразумеваемых ROLLUP и CUBE). До тех пор, пока выражения, переданные в GROUPING_ID(), являются выражениями, которые где-то появляются в GROUPING SETS-клаузе, GROUPING_ID() установит бит, соответствующий позиции выражения, всякий раз, когда это выражение сворачивается в супер-агрегат.

Синтаксис