Условие

Дана таблица sales(dt, region, category, amount). Постройте отчёт:

По строкам — region, по колонкам — category, в ячейках — SUM(amount).
Добавьте итоги по строкам и колонкам (Total row / column).
Покажите промежуточные итоги по region независимо от category (subtotals).

Решение

Подход

Pivot можно сделать через CASE WHEN (универсально), PIVOT (Snowflake/BigQuery/SQL Server) или Postgres crosstab. Итоги — GROUPING SETS, ROLLUP, CUBE.

1) Pivot вручную

SELECT
    region,
    SUM(CASE WHEN category = 'electronics' THEN amount END) AS electronics,
    SUM(CASE WHEN category = 'fashion'     THEN amount END) AS fashion,
    SUM(CASE WHEN category = 'home'        THEN amount END) AS home,
    SUM(CASE WHEN category = 'food'        THEN amount END) AS food,
    SUM(amount)                                              AS total
FROM sales
GROUP BY region
ORDER BY region;

Универсально работает везде. Минус: список категорий нужно знать заранее. Для динамического pivot — dynamic SQL.

1') Snowflake PIVOT

SELECT * FROM (
    SELECT region, category, amount FROM sales
)
PIVOT (SUM(amount) FOR category IN ('electronics', 'fashion', 'home', 'food'))
ORDER BY region;

2) ROLLUP — иерархические итоги

SELECT
    COALESCE(region,   'TOTAL') AS region,
    COALESCE(category, 'ALL')   AS category,
    SUM(amount) AS amount
FROM sales
GROUP BY ROLLUP (region, category)
ORDER BY region, category;

Даёт: (region, category), (region, NULL) — итог по региону, (NULL, NULL) — всех. Это left-prefix, иерархия от слева направо.

3) GROUPING SETS — независимые срезы

SELECT
    region,
    category,
    SUM(amount) AS amount
FROM sales
GROUP BY GROUPING SETS (
    (region, category),
    (region),
    (category),
    ()
)
ORDER BY region NULLS LAST, category NULLS LAST;

Даёт четыре среза: детальный, по region, по category, общий.

4) CUBE — все комбинации

GROUP BY CUBE (region, category)
-- = GROUPING SETS ((region, category), (region), (category), ())

Различение «итоговая строка» и «NULL по факту»

Если в данных есть честный NULL — COALESCE(region, 'TOTAL') не отличит. Используем GROUPING():

SELECT
    CASE WHEN GROUPING(region)   = 1 THEN 'TOTAL' ELSE region END AS region,
    CASE WHEN GROUPING(category) = 1 THEN 'ALL'   ELSE category END AS category,
    SUM(amount) AS amount
FROM sales
GROUP BY ROLLUP (region, category);

GROUPING(col) = 1 означает «эта строка — итог по col».

Подводные камни

Pivot через CASE требует знать список категорий. Если категории динамические — string_agg(... 'WHEN x THEN amount END') через dynamic SQL.
ROLLUP vs CUBE: ROLLUP — иерархический (region → region+category), CUBE — все 2^n сочетания. CUBE дороже.
COALESCE(col, 'TOTAL') ломается, если в данных есть NULL — путаются итоги и реальные NULL.
GROUP BY GROUPING SETS ((a), ()) даёт grand total — частый шаблон.
ORDER BY NULLS FIRST/LAST — для красивого вывода итогов.
PIVOT в Snowflake / SQL Server не одинаков синтаксически: Snowflake требует subquery с только нужными колонками — иначе Snowflake группирует по всем остальным.
Postgres crosstab требует extension tablefunc; на проде доступен не всегда.

Эталонный ответ

-- Pivot + итоги одним запросом
SELECT
    COALESCE(region, 'TOTAL')                                     AS region,
    SUM(CASE WHEN category='electronics' THEN amount END)         AS electronics,
    SUM(CASE WHEN category='fashion'     THEN amount END)         AS fashion,
    SUM(CASE WHEN category='home'        THEN amount END)         AS home,
    SUM(CASE WHEN category='food'        THEN amount END)         AS food,
    SUM(amount)                                                   AS total
FROM sales
GROUP BY ROLLUP (region)
ORDER BY GROUPING(region), region;

ROLLUP / GROUPING SETS дают итоги; pivot реализуется через CASE либо PIVOT (Snowflake/BQ). Для точного различения итогов от NULL — функция GROUPING().

DataLearn SQL-101: Pivot, ROLLUP, CUBE — кросс-таблицы в SQL