Тази статия в момента е на руски — английският превод е в процес на изготвяне.
Рано или поздно отчёт упирается в одно и то же: данные лежат «в высоту», по строке на событие, а смотреть на них хочется «в ширину» — категории по колонкам, по строке на сущность. Это и есть пивот: разворот длинной таблицы в широкую кросс-таблицу. В PostgreSQL для этого не нужны ни расширения, ни хитрые подзапросы — достаточно агрегатной функции с оговоркой FILTER (WHERE ...), которая пропускает в подсчёт только строки под заданное условие, не выходя за пределы одного GROUP BY.
Базовый приём: один агрегат на столбец
Возьмём типичную ситуацию: в таблице orders у каждого заказа есть статус, и нам нужны по каждому пользователю суммы в разрезе статусов — каждый статус отдельной колонкой. Можно гонять три запроса и сшивать результат руками, а можно обойтись одним:
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,
COUNT(*) FILTER (WHERE status = 'paid') AS paid_count
FROM orders
GROUP BY user_id;
Идея проста: каждый агрегат видит внутри группы только те строки, что прошли его FILTER, а остальные для него будто не существуют. Приём универсален и ложится на любой агрегат — SUM, COUNT, MAX, MIN, AVG, array_agg. Хотите среднее по оплаченным и счётчик по отменённым в одной строке отчёта — пожалуйста, никто не мешает смешивать их в одном SELECT.
MAX FILTER, чтобы «достать значение по ключу»
Отдельная история — вертикальное хранение атрибутов, та самая модель «ключ-значение». Когда параметры сущности разложены по строкам, MAX(...) FILTER (...) аккуратно вытаскивает нужное значение в нужный столбец. Пусть есть таблица со строками вида (user_id, kind, amount):
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 молча возьмёт наибольшую, и о дубликатах вы не узнаете. Когда такое возможно, лучше заранее решить, чем их схлопывать.
FILTER против SUM(CASE WHEN)
До того как FILTER пришёл в стандарт SQL (и в PostgreSQL начиная с 9.4), тот же разворот собирали на CASE:
SELECT
user_id,
SUM(CASE WHEN status = 'paid' THEN amount END) AS paid,
SUM(CASE WHEN status = 'pending' THEN amount END) AS pending
FROM orders
GROUP BY user_id;
Чем FILTER лучше и где сидят грабли:
- Читаемость.
FILTER явно отделяет «что считаем» от «при каком условии», тогда как CASE прячет условие внутрь выражения и тонет в скобках.
- Подсчёт строк. С
COUNT начинаются нюансы: COUNT(*) FILTER (WHERE c) честно считает строки, а в варианте с CASE приходится опираться на то, что ветка без ELSE возвращает NULL, который COUNT пропускает. Один промах — COUNT(CASE WHEN c THEN 0 END) вместо THEN 1 — и счётчик внезапно посчитает всё подряд, ведь ноль для COUNT это вполне себе значение.
- Переносимость.
FILTER — часть стандарта, но поддержан не везде; CASE понимают почти все СУБД.
И сразу боевой пример — кросс-таб по странам и статусам, уже с JOIN:
SELECT
u.country,
COUNT(*) FILTER (WHERE o.status = 'paid') AS paid,
COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.country
ORDER BY u.country;
crosstab() и его потолок
У 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(...) приходится прописывать заранее и вручную — сама функция вывести их не умеет. Стоит набору категорий стать динамическим, и это превращается в мучение. У подхода с FILTER колонки тоже фиксированы, зато он проще, обходится без расширений и без трения дружит с JOIN, HAVING и оконными функциями.
Подводные камни и переносимость
- Помните про
NULL. Если под FILTER не прошла ни одна строка, SUM и MAX вернут не ноль, а NULL. Нужен именно ноль в отчёте — оберните в COALESCE(SUM(...) FILTER (...), 0).
- Число столбцов зашито в текст запроса. «Динамический пивот» с заранее неизвестным набором категорий придётся собирать генерацией SQL в коде приложения или на
PL/pgSQL — чистым декларативным запросом тут не отделаешься.
- MySQL (вплоть до 8.x) оговорку
FILTER не знает — там остаётся проверенный SUM(CASE WHEN ... THEN ... END).
- ClickHouse тоже без
FILTER, но даёт комбинатор -If: sumIf(amount, status = 'paid'), countIf(status = 'paid') — компактный и быстрый аналог пивота.
Вывод короткий: пока набор категорий статичен, в PostgreSQL берите FILTER. Это самый читаемый и самый стандартный способ развернуть строки в столбцы за один проход по данным — без расширений, без подзапросов и без сшивки результатов на стороне приложения.
Рано или поздно отчёт упирается в одно и то же: данные лежат «в высоту», по строке на событие, а смотреть на них хочется «в ширину» — категории по колонкам, по строке на сущность. Это и есть пивот: разворот длинной таблицы в широкую кросс-таблицу. В PostgreSQL для этого не нужны ни расширения, ни хитрые подзапросы — достаточно агрегатной функции с оговоркой
FILTER (WHERE ...), которая пропускает в подсчёт только строки под заданное условие, не выходя за пределы одногоGROUP BY.Базовый приём: один агрегат на столбец
Возьмём типичную ситуацию: в таблице
ordersу каждого заказа есть статус, и нам нужны по каждому пользователю суммы в разрезе статусов — каждый статус отдельной колонкой. Можно гонять три запроса и сшивать результат руками, а можно обойтись одним: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, COUNT(*) FILTER (WHERE status = 'paid') AS paid_count FROM orders GROUP BY user_id;Идея проста: каждый агрегат видит внутри группы только те строки, что прошли его
FILTER, а остальные для него будто не существуют. Приём универсален и ложится на любой агрегат —SUM,COUNT,MAX,MIN,AVG,array_agg. Хотите среднее по оплаченным и счётчик по отменённым в одной строке отчёта — пожалуйста, никто не мешает смешивать их в одномSELECT.MAX FILTER, чтобы «достать значение по ключу»
Отдельная история — вертикальное хранение атрибутов, та самая модель «ключ-значение». Когда параметры сущности разложены по строкам,
MAX(...) FILTER (...)аккуратно вытаскивает нужное значение в нужный столбец. Пусть есть таблица со строками вида(user_id, kind, amount):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молча возьмёт наибольшую, и о дубликатах вы не узнаете. Когда такое возможно, лучше заранее решить, чем их схлопывать.FILTER против SUM(CASE WHEN)
До того как
FILTERпришёл в стандарт SQL (и в PostgreSQL начиная с 9.4), тот же разворот собирали наCASE:SELECT user_id, SUM(CASE WHEN status = 'paid' THEN amount END) AS paid, SUM(CASE WHEN status = 'pending' THEN amount END) AS pending FROM orders GROUP BY user_id;Чем
FILTERлучше и где сидят грабли:FILTERявно отделяет «что считаем» от «при каком условии», тогда какCASEпрячет условие внутрь выражения и тонет в скобках.COUNTначинаются нюансы:COUNT(*) FILTER (WHERE c)честно считает строки, а в варианте сCASEприходится опираться на то, что ветка безELSEвозвращаетNULL, которыйCOUNTпропускает. Один промах —COUNT(CASE WHEN c THEN 0 END)вместоTHEN 1— и счётчик внезапно посчитает всё подряд, ведь ноль дляCOUNTэто вполне себе значение.FILTER— часть стандарта, но поддержан не везде;CASEпонимают почти все СУБД.И сразу боевой пример — кросс-таб по странам и статусам, уже с
JOIN:SELECT u.country, COUNT(*) FILTER (WHERE o.status = 'paid') AS paid, COUNT(*) FILTER (WHERE o.status = 'cancelled') AS cancelled FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.country ORDER BY u.country;crosstab() и его потолок
У 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(...)приходится прописывать заранее и вручную — сама функция вывести их не умеет. Стоит набору категорий стать динамическим, и это превращается в мучение. У подхода сFILTERколонки тоже фиксированы, зато он проще, обходится без расширений и без трения дружит сJOIN,HAVINGи оконными функциями.Подводные камни и переносимость
NULL. Если подFILTERне прошла ни одна строка,SUMиMAXвернут не ноль, аNULL. Нужен именно ноль в отчёте — оберните вCOALESCE(SUM(...) FILTER (...), 0).PL/pgSQL— чистым декларативным запросом тут не отделаешься.FILTERне знает — там остаётся проверенныйSUM(CASE WHEN ... THEN ... END).FILTER, но даёт комбинатор-If:sumIf(amount, status = 'paid'),countIf(status = 'paid')— компактный и быстрый аналог пивота.Вывод короткий: пока набор категорий статичен, в PostgreSQL берите
FILTER. Это самый читаемый и самый стандартный способ развернуть строки в столбцы за один проход по данным — без расширений, без подзапросов и без сшивки результатов на стороне приложения.