PIVOT, UNPIVOT и GROUPING SETS / CUBE / ROLLUP

Аналитический отчёт почти всегда требует одной из двух операций над формой таблицы. Первая — превратить значения колонки в заголовки колонок: из «строки вида (месяц, регион, выручка)» сделать «таблицу регион x месяц». Это поворот, или pivot. Вторая — посчитать агрегаты не по одной комбинации ключей, а по нескольким сразу: выручка по регионам, выручка по продуктам, и общий итог — в одном результате.

Стандартный SQL обе задачи решает мучительно. Pivot пишется руками через десяток CASE WHEN, причём список значений приходится знать заранее.

GROUPING SETS, ROLLUP, CUBE: multi-level агрегация Многоуровневые итоги — через UNION ALL нескольких GROUP BY, каждый из которых заново сканирует таблицу. DuckDB даёт прямые конструкции: PIVOT / UNPIVOT для поворота и GROUPING SETS / CUBE / ROLLUP для многоуровневых агрегатов. Разберём механику каждой.

PIVOT: значения колонки становятся колонками

PIVOT берёт значения из одной колонки и раскладывает их по отдельным колонкам результата, агрегируя данные на пересечении. Классический случай — таблица продаж «длинного» формата, которую нужно показать как матрицу.

-- Исходные данные: long-формат
-- region | quarter | amount
-- EU     | Q1      | 100
-- EU     | Q2      | 150
-- US     | Q1      | 200
-- US     | Q2      | 120

PIVOT sales
  ON quarter            -- значения этой колонки станут заголовками
  USING sum(amount)     -- агрегат на пересечении
  GROUP BY region;      -- что остаётся строками

Результат — широкая таблица:

region | Q1  | Q2
EU     | 100 | 150
US     | 200 | 120

Три клаузы PIVOT отвечают на три вопроса. ON — какая колонка даёт новые заголовки (её уникальные значения Q1, Q2 становятся именами колонок). USING — какой агрегат считать на пересечении строки и колонки. GROUP BY — какие колонки остаются «строчными» осями.

Ключевое отличие от стандартного SQL: DuckDB сам определяет список значений для разворота. В классическом CASE-pivot вы обязаны заранее перечислить все кварталы: sum(CASE WHEN quarter='Q1' THEN amount END) AS Q1, .... Появился Q3 — запрос его не покажет, пока вы руками не добавите ещё один CASE. PIVOT ... ON quarter сначала выполняет неявный запрос «какие значения есть в quarter», и строит колонки динамически. Новый квартал появится в выводе сам.

PIVOT умеет разворачивать по нескольким колонкам и считать несколько агрегатов:

-- Развернуть по quarter, посчитать и сумму, и количество
PIVOT sales
  ON quarter
  USING sum(amount) AS revenue, count(*) AS orders
  GROUP BY region;
-- колонки: region, Q1_revenue, Q1_orders, Q2_revenue, Q2_orders

UNPIVOT: колонки становятся строками

UNPIVOT — обратная операция. Она берёт несколько колонок и сворачивает их в две: одну с именем бывшей колонки, другую с её значением. Это нужно, когда данные пришли в «широком» виде (по колонке на месяц), а для дальнейшей обработки удобнее «длинный» формат — длинный формат проще фильтровать, агрегировать и джойнить.

-- Исходные данные: wide-формат
-- region | jan | feb | mar
-- EU     | 100 | 150 | 120
-- US     | 200 | 180 | 210

UNPIVOT monthly_sales
  ON jan, feb, mar          -- какие колонки свернуть
  INTO
    NAME month              -- куда положить имя колонки
    VALUE amount;           -- куда положить значение

Результат:

region | month | amount
EU     | jan   | 100
EU     | feb   | 150
EU     | mar   | 120
US     | jan   | 200
US     | feb   | 180
US     | mar   | 210

ON перечисляет сворачиваемые колонки. INTO NAME задаёт имя колонки, куда попадут имена jan/feb/mar. INTO VALUE — имя колонки для самих чисел. Колонки, не упомянутые в ON (здесь region), остаются как есть и размножаются по строкам.

UNPIVOT хорошо сочетается с COLUMNS(): вместо ручного списка колонок можно дать regex:

-- Свернуть все колонки, кроме region
UNPIVOT monthly_sales
  ON COLUMNS(* EXCLUDE (region))
  INTO NAME month VALUE amount;

PIVOT и UNPIVOT — обратные друг другу: UNPIVOT приводит данные к длинному виду, а длинный вид — это нормальная форма для хранения и обработки. Общее правило: храните данные в длинном формате, разворачивайте в широкий только в момент презентации отчёта.

GROUPING SETS: несколько GROUP BY за один проход

Вторая задача — многоуровневые итоги. Допустим, нужен отчёт, где есть и выручка по регионам, и выручка по продуктам, и общий итог. Это три разные группировки. Наивное решение — три запроса через UNION ALL:

-- Наивно: три отдельных GROUP BY, три сканирования таблицы
SELECT region, NULL AS product, sum(amount) FROM sales GROUP BY region
UNION ALL
SELECT NULL, product, sum(amount) FROM sales GROUP BY product
UNION ALL
SELECT NULL, NULL, sum(amount) FROM sales;

Каждый UNION ALL-блок заново читает sales. GROUPING SETS выражает то же намерение одной конструкцией — и движок делает это за один проход по данным:

-- GROUPING SETS: три группировки, одно сканирование
SELECT region, product, sum(amount) AS revenue
FROM sales
GROUP BY GROUPING SETS (
    (region),      -- группировка по региону
    (product),     -- группировка по продукту
    ()             -- пустой набор = общий итог по всей таблице
);

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

ROLLUP и CUBE: сокращения для частых наборов

ROLLUP и CUBE — это синтаксический сахар над GROUPING SETS для двух очень частых паттернов.

ROLLUP (a, b, c) строит иерархические подытоги, отбрасывая ключи справа налево. Он разворачивается в наборы (a, b, c), (a, b), (a), (). Это то, что нужно для отчёта с подытогами по уровням: детальная строка, подытог по (a, b), подытог по (a), общий итог.

-- ROLLUP: иерархия регион -> продукт
SELECT region, product, sum(amount) AS revenue
FROM sales
GROUP BY ROLLUP (region, product);
-- разворачивается в GROUPING SETS ((region, product), (region), ())

CUBE (a, b, c) строит все возможные комбинации ключей — все подмножества множества {a, b, c}. Для двух колонок это (a, b), (a), (b), () — четыре набора. Для N колонок — 2^N наборов. CUBE нужен, когда вам нужны срезы по всем сочетаниям измерений.

-- CUBE: все комбинации region и product
SELECT region, product, sum(amount) AS revenue
FROM sales
GROUP BY CUBE (region, product);
-- разворачивается в GROUPING SETS ((region, product), (region), (product), ())

ROLLUP и CUBE — частные случаи GROUPING SETS, поэтому всё, что они делают, можно записать через GROUPING SETS руками. Используйте ROLLUP/CUBE, когда нужный набор совпадает с их шаблоном, и GROUPING SETS — когда нужен произвольный набор.

Попробуй сам

Создайте таблицу:

CREATE TABLE sales AS
  SELECT * FROM (VALUES
    ('EU', 'A', 'Q1', 100),
    ('EU', 'B', 'Q1', 150),
    ('EU', 'A', 'Q2', 120),
    ('US', 'A', 'Q1', 200),
    ('US', 'B', 'Q2', 180),
  ) t(region, product, quarter, amount);

Задания:

1. Через PIVOT постройте матрицу region x quarter с суммой amount.
2. Возьмите результат задания 1 как таблицу и через UNPIVOT верните его в длинный формат. Сравните с исходной таблицей.
3. Через GROUPING SETS посчитайте выручку по регионам, по продуктам и общий итог — одним запросом.
4. Напишите запрос с ROLLUP (region, product) и с CUBE (region, product). Сравните, какие строки добавляет CUBE сверх ROLLUP, и объясните почему.
5. Добавьте в запрос с ROLLUP колонку GROUPING(region) и GROUPING(product). Посмотрите, как по их значениям различаются детальные и итоговые строки.