Условная агрегация: длинные строки в широкие колонки

Длинное против широкого

Обычный GROUP BY status даёт длинный вывод: одна строка на категорию, значение под значением. Отчёту обычно нужен широкий вывод: одна строка на сущность (день, пользователь) с одной колонкой на категорию. Разворот (pivot) — это переход между ними, и фокус в том, чтобы каждая выходная колонка была своим условным агрегатом.

Два способа написать колонку разворота

Когда встречаешь отчётный запрос из трёх отдельных блоков SELECT … WHERE status='…', ты видишь антипаттерн, который этот раздел заменяет. Есть две идиомы для написания каждой колонки разворота прямо в запросе, и для счётов и сумм они эквивалентны:

-- Форма FILTER (современная, стандартная, рекомендуемая):
SELECT
  created_at::date AS day,
  COUNT(*) FILTER (WHERE status = 'paid')      AS paid,
  COUNT(*) FILTER (WHERE status = 'pending')   AS pending,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(total) FILTER (WHERE status = 'paid')    AS paid_revenue
FROM orders
GROUP BY created_at::date
ORDER BY day;

-- Форма CASE (переносимая, старая, работает везде):
SELECT
  created_at::date AS day,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid,        -- или:
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
  SUM(CASE WHEN status = 'paid' THEN total END)    AS paid_revenue
FROM orders
GROUP BY created_at::date;

Механизм одинаков в обеих: каждая колонка — агрегат, который построчно либо включает значение, либо не вносит ничего. С FILTER агрегат просто пропускает несовпавшие строки. С CASE несовпавшая ветка возвращает 0 (для счёта через SUM) или NULL (который агрегаты игнорируют). FILTER чище и является сеньорским дефолтом; CASE — переносимый запасной вариант для движков без FILTER.

Выигрыш, который важен на масштабе, — число сканов. Обе формы считают все колонки разворота за один проход — движок проверяет условие каждой колонки против каждой строки по мере её протекания. Антипаттерн — один запрос на категорию: три отдельных SELECT count(*) FROM orders WHERE status='paid' (затем 'pending', затем 'cancelled') — это три Seq Scan таблицы. На таблице orders в 12М строк это ~3 × 800мс ≈ 2.4с против ~850мс у одного группового запроса с тремя колонками FILTER — и разрыв растёт линейно с числом колонок разворота: разворот на 7 статусов — это один скан вместо семи. Условная агрегация превращает отчёт из N сканов в отчёт из 1 скана — именно поэтому разворачивают у источника, а не шлют по запросу на колонку из приложения.

Ловушка счёта через CASE

Самый частый баг здесь — счёт с неверным запасным значением:

-- НЕВЕРНО: COUNT считает не-NULL, а 0 — не NULL → считает КАЖДУЮ строку
COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)   -- всегда = число всех строк

-- ВЕРНЫЕ варианты:
COUNT(CASE WHEN status = 'paid' THEN 1 END)          -- ELSE это NULL → COUNT пропустит
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)     -- SUM складывает 1, 0 не вносят вклада
COUNT(*) FILTER (WHERE status = 'paid')              -- чище всего

COUNT считает не-NULL значения; 0 — не NULL, поэтому COUNT(CASE … ELSE 0 END) считает все строки — тихая ошибка «промах на всё». Либо убери ELSE (тогда несовпадения это NULL и COUNT их пропустит), используй SUM из 1/0, либо используй FILTER. Это ровно та ловушка, ради устранения которой добавили FILTER.

Сочетание с GROUP BY и итогами

Условная агрегация компонуется со всем, что ты выучил. Добавь GROUP BY по сущности, чтобы получить одну широкую строку на сущность; наложи ROLLUP из прошлого урока, чтобы добавить строку итогов по всем дням:

SELECT
  created_at::date AS day,
  COUNT(*) FILTER (WHERE status = 'paid')    AS paid,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY ROLLUP (created_at::date)
ORDER BY day NULLS LAST;

Это также ручной родственник функции crosstab из расширения tablefunc и оконных функций: оконные функции (следующий раздел, 04-window-functions) сохраняют одну строку на входную строку, добавляя агрегатные колонки, тогда как условная агрегация сворачивает строки и распределяет категории по колонкам. Выбирай условную агрегацию, когда нужна компактная сводная таблица; тянись за окнами, когда нужно сохранить каждую детальную строку рядом с агрегатом её группы.