Пивот в PostgreSQL через FILTER: как развернуть строки в столбцы

Рано или поздно почти любой отчёт упирается в одну и ту же задачу: данные лежат «в высоту», а смотреть на них хочется «в ширину».

Например, в таблице заказов каждая строка — отдельный заказ:

user_id	status	amount
1	paid	1200
1	pending	500
1	cancelled	300
2	paid	900
2	paid	700

А в отчёте хочется видеть одну строку на пользователя и отдельные колонки под каждый статус:

user_id	paid	pending	cancelled
1	1200	500	300
2	1600	NULL	NULL

Такой разворот называют пивотом: мы превращаем длинную таблицу в широкую кросс-таблицу.

В PostgreSQL для простого пивота не нужны расширения, временные таблицы и сложные подзапросы. Очень часто достаточно агрегатной функции с условием FILTER (WHERE ...).

Главная идея FILTER

Обычный агрегат считает все строки внутри группы:

SELECT
  user_id,
  SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;

Такой запрос отвечает на вопрос:

Сколько денег принёс каждый пользователь по всем заказам?

А теперь хочется детальнее:

Сколько денег у пользователя в оплаченных заказах, сколько в ожидающих, сколько в отменённых?

Вот здесь и появляется FILTER.

SELECT
  user_id,
  SUM(amount) FILTER (WHERE status = 'paid') AS paid,
  SUM(amount) FILTER (WHERE status = 'pending') AS pending,
  SUM(amount) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY user_id;

Читается почти как обычный русский текст:

• сгруппируй заказы по пользователю;
• в колонку paid сложи только строки со статусом paid;
• в колонку pending сложи только строки со статусом pending;
• в колонку cancelled сложи только строки со статусом cancelled.

То есть каждый агрегат живёт внутри общего GROUP BY, но видит только свои строки.

Один агрегат — одна колонка отчёта

Самый понятный способ думать о таком пивоте:

каждая будущая колонка — это отдельный агрегат со своим условием.

Например, можно одновременно посчитать и суммы, и количество заказов:

SELECT
  user_id,
  SUM(amount) FILTER (WHERE status = 'paid') AS paid_amount,
  SUM(amount) FILTER (WHERE status = 'pending') AS pending_amount,
  SUM(amount) FILTER (WHERE status = 'cancelled') AS cancelled_amount,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid_count,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_count
FROM orders
GROUP BY user_id;

Здесь в одной строке отчёта лежит сразу несколько ответов:

• сколько денег в оплаченных заказах;
• сколько денег в ожидающих заказах;
• сколько денег в отменённых заказах;
• сколько было оплаченных заказов;
• сколько было отменённых заказов.

И всё это считается одним запросом, без ручной склейки результатов.

FILTER работает не только с SUM и COUNT

FILTER можно использовать почти с любым агрегатом.

Например, средний чек только по оплаченным заказам:

SELECT
  user_id,
  AVG(amount) FILTER (WHERE status = 'paid') AS avg_paid_amount
FROM orders
GROUP BY user_id;

Максимальная сумма отменённого заказа:

SELECT
  user_id,
  MAX(amount) FILTER (WHERE status = 'cancelled') AS max_cancelled_amount
FROM orders
GROUP BY user_id;

Список id оплаченных заказов:

SELECT
  user_id,
  ARRAY_AGG(id) FILTER (WHERE status = 'paid') AS paid_order_ids
FROM orders
GROUP BY user_id;

Это удобно: условие относится не ко всему запросу, а только к конкретному агрегату.

WHERE отфильтровал бы строки для всего запроса сразу. А FILTER позволяет каждому агрегату выбрать свой набор строк.

Пример с JOIN: отчёт по странам

Представим, что есть таблица пользователей и таблица заказов:

• users хранит пользователей и их страны;
• orders хранит заказы.

Нужно получить отчёт по странам: сколько оплаченных и отменённых заказов было в каждой стране.

SELECT
  u.country,
  COUNT(*) FILTER (WHERE o.status = 'paid') AS paid_orders,
  COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled_orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.country
ORDER BY u.country;

Здесь JOIN сначала соединяет пользователей с заказами, а потом GROUP BY собирает строки по странам.

После этого каждый COUNT считает только строки со своим статусом.

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

MAX FILTER: как достать значение по ключу

Есть ещё один частый случай: данные хранятся в формате «ключ-значение».

Например, таблица транзакций выглядит так:

user_id	kind	amount
1	deposit	1000
1	withdrawal	300
2	deposit	700

А хочется получить так:

user_id	deposit	withdrawal
1	1000	300
2	700	NULL

Для этого часто используют MAX(...) FILTER (...).

SELECT
  user_id,
  MAX(amount) FILTER (WHERE kind = 'deposit') AS deposit,
  MAX(amount) FILTER (WHERE kind = 'withdrawal') AS withdrawal
FROM tx
GROUP BY user_id;

На первый взгляд может быть странно: почему именно MAX, если мы вроде бы не ищем максимум?

Дело в том, что агрегат обязан схлопнуть несколько строк группы в одно значение. Если для каждой пары user_id и kind есть ровно одна строка, то MAX просто вернёт это единственное значение.

То есть MAX здесь используется как аккуратный способ сказать:

возьми значение из нужной строки и положи его в отдельную колонку.

Но есть важный нюанс. Если у одного пользователя окажется несколько строк с kind = 'deposit', то MAX молча выберет наибольшую сумму.

Например:

user_id	kind	amount
1	deposit	1000
1	deposit	1500

В этом случае результатом будет 1500.

Поэтому перед таким приёмом важно понимать данные. Если дубликаты невозможны по бизнес-правилам — всё хорошо. Если возможны — нужно заранее решить, что именно делать: брать максимум, минимум, сумму, последнее значение по дате или сначала чистить дубликаты.

FILTER против SUM(CASE WHEN ...)

До FILTER такие отчёты часто писали через CASE.

Вот так:

SELECT
  user_id,
  SUM(CASE WHEN status = 'paid' THEN amount END) AS paid,
  SUM(CASE WHEN status = 'pending' THEN amount END) AS pending,
  SUM(CASE WHEN status = 'cancelled' THEN amount END) AS cancelled
FROM orders
GROUP BY user_id;

Этот вариант тоже рабочий. Логика такая:

• если статус подходит, вернуть amount;
• если не подходит, вернуть NULL;
• SUM пропускает NULL;
• в итоге суммируются только нужные строки.

Но с FILTER запрос обычно читается проще:

SUM(amount) FILTER (WHERE status = 'paid')

Здесь сразу видно две части:

• SUM(amount) — что считаем;
• FILTER (WHERE status = 'paid') — по какому условию считаем.

А в варианте с CASE условие прячется внутри выражения:

SUM(CASE WHEN status = 'paid' THEN amount END)

Для коротких запросов это терпимо. Но когда в отчёте 10–20 колонок, FILTER становится заметно приятнее.

Особенно аккуратно с COUNT и CASE

С SUM(CASE WHEN ...) ошибиться сложнее. А вот с COUNT есть классическая ловушка.

Правильный вариант через FILTER:

SELECT
  user_id,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid_count
FROM orders
GROUP BY user_id;

Аналог через CASE:

SELECT
  user_id,
  COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_count
FROM orders
GROUP BY user_id;

Почему это работает? Потому что если условие не выполнено, CASE вернёт NULL, а COUNT не считает NULL.

Но если случайно написать ELSE 0, результат сломается:

SELECT
  user_id,
  COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count
FROM orders
GROUP BY user_id;

Такой COUNT посчитает все строки, потому что для него и 1, и 0 — это обычные значения. COUNT не интересуется, «правдивое» значение или «ложное». Он просто считает всё, что не NULL.

Поэтому для подсчёта строк FILTER выглядит особенно чисто:

COUNT(*) FILTER (WHERE status = 'paid')

Никаких скрытых NULL, никаких ловушек с ELSE.

Что будет, если строк нет

Важный момент: если под условие FILTER не попала ни одна строка, многие агрегаты вернут NULL.

Например:

SELECT
  user_id,
  SUM(amount) FILTER (WHERE status = 'paid') AS paid_amount
FROM orders
GROUP BY user_id;

Если у пользователя нет оплаченных заказов, в paid_amount будет NULL, а не 0.

Для аналитики это иногда правильно: NULL означает «нет данных». Но в обычном отчёте чаще хочется видеть ноль.

Тогда используйте COALESCE.

SELECT
  user_id,
  COALESCE(SUM(amount) FILTER (WHERE status = 'paid'), 0) AS paid_amount,
  COALESCE(SUM(amount) FILTER (WHERE status = 'pending'), 0) AS pending_amount,
  COALESCE(SUM(amount) FILTER (WHERE status = 'cancelled'), 0) AS cancelled_amount
FROM orders
GROUP BY user_id;

COALESCE берёт первое не-NULL значение. Если сумма не посчиталась и вернулся NULL, он заменит его на 0.

FILTER не делает динамический пивот

У FILTER есть ограничение: колонки нужно заранее прописать в запросе.

Например, если вы пишете:

SELECT
  user_id,
  SUM(amount) FILTER (WHERE status = 'paid') AS paid,
  SUM(amount) FILTER (WHERE status = 'pending') AS pending,
  SUM(amount) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY user_id;

то вы заранее решили, что в отчёте будут три колонки:

• paid;
• pending;
• cancelled.

Если завтра появится новый статус refunded, он сам собой в отчёт не добавится. Нужно будет дописать ещё одну колонку:

SUM(amount) FILTER (WHERE status = 'refunded') AS refunded

Это нормально для большинства бизнес-отчётов, где набор колонок известен заранее: статусы заказов, месяцы квартала, типы платежей, роли пользователей.

Но если категории заранее неизвестны и должны превращаться в колонки автоматически, обычный SQL-запрос становится неудобным. В таком случае динамический пивот обычно собирают в коде приложения или через генерацию SQL в PL/pgSQL.

crosstab в PostgreSQL

В PostgreSQL есть и специальный инструмент для пивотов — функция crosstab() из расширения tablefunc.

Пример выглядит так:

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT user_id, status, SUM(amount) FROM orders GROUP BY 1, 2 ORDER BY 1, 2',
  'SELECT DISTINCT status FROM orders ORDER BY 1'
) AS ct(
  user_id int,
  paid numeric,
  pending numeric,
  cancelled numeric
);

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

Вот эта часть обязательна:

AS ct(
  user_id int,
  paid numeric,
  pending numeric,
  cancelled numeric
)

То есть PostgreSQL не может просто посмотреть на значения статусов и сам создать под них колонки в результате обычного запроса.

Поэтому для простых отчётов FILTER часто приятнее:

• не нужно подключать расширение;
• запрос проще читать;
• хорошо работает вместе с JOIN;
• удобно сочетать с GROUP BY, HAVING, сортировкой и другими частями запроса;
• легко смешивать разные агрегаты в одном отчёте.

Пример с HAVING

Допустим, нужно показать только страны, где есть хотя бы 10 оплаченных заказов.

SELECT
  u.country,
  COUNT(*) FILTER (WHERE o.status = 'paid') AS paid_orders,
  COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled_orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.country
HAVING COUNT(*) FILTER (WHERE o.status = 'paid') >= 10
ORDER BY paid_orders DESC;

Здесь FILTER используется не только в SELECT, но и в HAVING.

То есть мы не просто выводим счётчик оплаченных заказов, а ещё и фильтруем группы по этому счётчику.

Как это выглядит в других базах данных

FILTER — часть стандарта SQL, и в PostgreSQL он доступен начиная с версии 9.4.

Но поддерживают его не все СУБД.

В MySQL 8.x такого синтаксиса нет, поэтому там обычно пишут через CASE:

SELECT
  user_id,
  SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid,
  SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled
FROM orders
GROUP BY user_id;

В ClickHouse для таких задач есть специальные функции с суффиксом If:

SELECT
  user_id,
  sumIf(amount, status = 'paid') AS paid,
  sumIf(amount, status = 'pending') AS pending,
  sumIf(amount, status = 'cancelled') AS cancelled,
  countIf(status = 'paid') AS paid_count
FROM orders
GROUP BY user_id;

Идея та же самая: агрегат считает только строки, которые прошли условие.

Когда использовать FILTER

FILTER особенно хорош, когда:

• вы пишете отчёт в PostgreSQL;
• нужно развернуть несколько известных категорий в колонки;
• хочется получить всё одним GROUP BY;
• в отчёте много условных сумм, счётчиков, средних значений;
• важна читаемость запроса.

Типичные примеры:

• суммы заказов по статусам;
• количество пользователей по ролям;
• оплаты по типам платежей;
• заявки по этапам воронки;
• события по дням недели;
• метрики по странам;
• значения из модели «ключ-значение».

Главное

Пивот — это разворот данных из длинного формата в широкий: категории из строк становятся отдельными колонками.

В PostgreSQL простой пивот удобно делать через агрегаты с FILTER:

SELECT
  user_id,
  SUM(amount) FILTER (WHERE status = 'paid') AS paid,
  SUM(amount) FILTER (WHERE status = 'pending') AS pending,
  SUM(amount) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders
GROUP BY user_id;

Главная мысль:

один столбец отчёта — один агрегат со своим условием.

FILTER делает запрос понятным: отдельно видно, что мы считаем, и отдельно видно, какие строки попадают в расчёт.

Если под условие не попала ни одна строка, SUM, MAX, AVG и похожие агрегаты могут вернуть NULL. Для отчётного нуля используйте COALESCE.

Если категории заранее известны, FILTER — один из самых чистых и удобных способов собрать пивот в PostgreSQL: без расширений, без ручной склейки запросов и без лишней магии.