Pivot-таблицы в PostgreSQL: как переводить строки в столбцы с помощью CASE и crosstab без Excel

Типичная задача: нужно показать продажи по месяцам в разрезе категорий товаров - строки по вертикали, месяцы по горизонтали. Данные в базе хранятся в «длинном» формате: одна строка - одна транзакция. Многие в этот момент делают выгрузку в Excel, строят там сводную таблицу и идут дальше. Но если таких отчётов несколько, данные обновляются каждый день, а выгрузка занимает 10 минут - это уже не решение, а рутина.

PostgreSQL позволяет переворачивать данные прямо в запросе. Два основных пути: CASE WHEN для гибкости и читаемости, и функция crosstab из расширения tablefunc для более декларативного подхода. У каждого есть свои сценарии применения, ограничения и подводные камни.

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

Что значит «перевести строки в столбцы»

Представим таблицу sales:

Это «длинный» формат. Нам нужен «широкий»: одна строка на категорию, а месяцы - в отдельных столбцах.

Именно это называют pivot-преобразованием. В Excel это делается в два клика, в SQL - чуть сложнее, зато результат встраивается в любой запрос, CTE или представление.

Метод 1: CASE WHEN

Это самый прямолинейный способ. Суть: для каждого будущего столбца пишем отдельное выражение CASE WHEN month = 'Jan' THEN revenue END, а потом агрегируем.

Базовый шаблон

SELECT
  category,
  SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS jan,
  SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS feb,
  SUM(CASE WHEN month = 'Mar' THEN revenue ELSE 0 END) AS mar
FROM sales
GROUP BY category
ORDER BY category;

Несколько вещей, которые важно понимать про этот запрос:

• ELSE 0 не обязателен, если вас устраивает NULL вместо нуля. Но для SUM это не имеет значения - NULL всё равно игнорируется. Для AVG разница есть: NULL не влияет на среднее, а 0 влияет.
• Вместо SUM можно использовать MAX или MIN - это удобно, когда значение уникально на пересечении строки и столбца и агрегация технически не нужна, но синтаксис GROUP BY её требует.
• Псевдонимы столбцов задаются через AS - называйте их так, как должны выглядеть в отчёте.

Пример с реальной структурой

Допустим, есть таблица событий продукта:

CREATE TABLE user_events (
  user_id INT,
  event_type TEXT,  -- 'signup', 'purchase', 'churn'
  event_date DATE
);

Задача: для каждого пользователя показать, сколько раз он совершил каждый тип события.

SELECT
  user_id,
  COUNT(CASE WHEN event_type = 'signup'   THEN 1 END) AS signups,
  COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) AS purchases,
  COUNT(CASE WHEN event_type = 'churn'    THEN 1 END) AS churns
FROM user_events
GROUP BY user_id
ORDER BY user_id;

Здесь COUNT(CASE ... THEN 1 END) - распространённый паттерн. Когда условие не выполняется, CASE возвращает NULL, а COUNT его не считает. Результат: количество строк, где условие истинно.

Когда CASE WHEN удобнее всего

• Количество будущих столбцов небольшое и стабильное (3-15 значений).
• Нужно встроить преобразование в более сложный запрос с JOIN, CTE или фильтрами.
• Важна читаемость: коллеги смогут разобраться без документации.
• Нет прав на создание расширений в базе.

Метод 2: crosstab из расширения tablefunc

PostgreSQL включает расширение tablefunc, которое добавляет функцию crosstab(). Она принимает SQL-запрос в виде строки и возвращает уже «широкую» таблицу. Подход более декларативный, но синтаксис поначалу кажется непривычным.

Шаг 1: подключить расширение

CREATE EXTENSION IF NOT EXISTS tablefunc;

Это нужно сделать один раз для каждой базы данных. Требуются права суперпользователя или роль с разрешением на создание расширений. В облачных сервисах вроде Amazon RDS или Supabase расширение обычно доступно, но может потребоваться явное разрешение. Документация PostgreSQL по tablefunc: postgresql.org/docs/current/tablefunc.html.

Шаг 2: понять структуру входных данных

Функция crosstab ожидает запрос, который возвращает ровно три столбца:

1. row_name - значение, которое станет строкой (например, category).
2. category - значение, которое станет заголовком столбца (например, month).
3. value - значение на пересечении.

Данные должны быть отсортированы по первому и второму столбцам. Это критически важно - без правильного ORDER BY результат будет неправильным.

Базовый шаблон

SELECT *
FROM crosstab(
  'SELECT category, month, SUM(revenue)::NUMERIC
   FROM sales
   GROUP BY category, month
   ORDER BY category, month',
  'VALUES (''Jan''), (''Feb''), (''Mar'')'
) AS ct(
  category TEXT,
  jan NUMERIC,
  feb NUMERIC,
  mar NUMERIC
);

Обратите внимание на несколько моментов:

• Внутри строки SQL одинарные кавычки экранируются удвоением: ''Jan''.
• Второй аргумент функции - запрос, возвращающий список категорий в нужном порядке. Можно использовать VALUES или отдельный SELECT.
• Псевдоним AS ct(...) обязателен - в нём вы описываете все выходные столбцы с их типами. Если столбцов не хватит или типы не совпадут, PostgreSQL вернёт ошибку.

Двухаргументная vs однаргументная версия

Есть два варианта вызова crosstab:

Почти всегда нужна двухаргументная версия. Если для категории «Clothing» нет данных за февраль, однаргументная версия подставит на это место следующее доступное значение. Это тихая ошибка, которую легко не заметить.

Пример с той же таблицей user_events

SELECT *
FROM crosstab(
  'SELECT user_id::TEXT, event_type, COUNT(*)::INT
   FROM user_events
   GROUP BY user_id, event_type
   ORDER BY user_id::TEXT, event_type',
  'VALUES (''churn''), (''purchase''), (''signup'')'
) AS ct(
  user_id TEXT,
  churns INT,
  purchases INT,
  signups INT
);

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

CASE WHEN против crosstab: что выбрать

Если вы пишете запрос раз и надолго, crosstab немного экономит строки кода. Если запрос нужно поддерживать, показывать коллегам или встраивать в сложную логику - CASE WHEN понятнее и надёжнее.

Динамический pivot: когда столбцы не известны заранее

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

Обходные пути существуют, но они сложнее:

• PL/pgSQL-функция, которая динамически строит текст запроса и выполняет его через EXECUTE. Гибко, но сложно поддерживать.
• Формирование запроса на стороне приложения - код сначала получает список категорий, потом строит нужный SQL. Это, пожалуй, самый чистый подход для продакшена.
• JSON/array-агрегация - вместо отдельных столбцов складывать данные в jsonb. Менее читаемо в отчёте, зато не требует знать список категорий заранее.

SELECT
  category,
  jsonb_object_agg(month, revenue ORDER BY month) AS monthly_revenue
FROM sales
GROUP BY category;

Производительность: что нужно знать

Оба метода делают полное сканирование таблицы, если нет подходящего индекса. Для аналитических запросов это нормально - но есть несколько моментов, которые влияют на скорость.

Агрегируйте до преобразования. Если исходная таблица большая, сначала схлопните данные через CTE или подзапрос, а потом делайте pivot. Это уменьшает объём данных, с которым работает финальная часть запроса.

WITH aggregated AS (
  SELECT category, month, SUM(revenue) AS total
  FROM sales
  WHERE event_date >= '2024-01-01'
  GROUP BY category, month
)
SELECT
  category,
  SUM(CASE WHEN month = 'Jan' THEN total ELSE 0 END) AS jan,
  SUM(CASE WHEN month = 'Feb' THEN total ELSE 0 END) AS feb
FROM aggregated
GROUP BY category;

Индексы на фильтрующих столбцах. Если в WHERE есть условие по дате или статусу, индекс на этом столбце ускорит выборку до агрегации.

EXPLAIN ANALYZE. Перед тем как встраивать запрос в отчёт, посмотрите план выполнения. Особенно это важно для crosstab - функция является «чёрным ящиком» для планировщика, и иногда вспомогательный запрос с предварительной агрегацией работает быстрее.

Типичные ошибки

Неправильный порядок в crosstab

Однаргументная версия функции разбирает строки последовательно. Если данные не отсортированы, значения «съезжают» в соседние столбцы. Ошибка тихая - запрос выполняется без предупреждений, но цифры неверные. Всегда используйте ORDER BY в запросе внутри crosstab и двухаргументную версию.

Несовпадение типов в псевдониме

Если в запросе значение возвращается как BIGINT, а в псевдониме написано INT, PostgreSQL выдаст ошибку или молча обрежет значение. Приводите типы явно внутри запроса: SUM(revenue)::NUMERIC.

NULL вместо нуля

Если для комбинации «строка + столбец» нет записей, crosstab поставит NULL. При дальнейших вычислениях это может сломать арифметику. Оборачивайте нужные столбцы в COALESCE(jan, 0) на уровне внешнего запроса.

CASE WHEN с AVG и нулями

Если использовать AVG(CASE WHEN ... THEN value ELSE 0 END), нули от несовпавших строк участвуют в расчёте среднего. Правильный вариант: AVG(CASE WHEN ... THEN value END) без ELSE - тогда несовпавшие строки дают NULL и в среднее не попадают.

Чеклист перед запуском

1. Убедитесь, что данные не дублируются до GROUP BY - дубли исказят агрегаты.
2. Проверьте, все ли значения категорий включены в CASE или второй аргумент crosstab.
3. Для crosstab: есть ORDER BY по row_name и category в исходном запросе.
4. Для crosstab: используется двухаргументная версия, если возможны пропущенные значения.
5. Типы в псевдониме AS ct(...) совпадают с реальными типами в запросе.
6. Если нужны нули вместо NULL - добавлен COALESCE.
7. Если считаете среднее - CASE без ELSE 0.
8. Для больших таблиц: предварительная агрегация вынесена в CTE.

Pivot в представлениях и CTE: как переиспользовать преобразование

Один из недооценённых сценариев: pivot-запрос нужен не один раз, а как основа для нескольких отчётов. В этом случае имеет смысл вынести преобразование в представление или материализованное представление.

Обычное представление (VIEW) просто сохраняет текст запроса. Каждый раз при обращении PostgreSQL выполняет его заново. Это удобно, если данные часто меняются и нужна актуальность.

CREATE VIEW sales_wide AS
SELECT
  category,
  SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS jan,
  SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS feb,
  SUM(CASE WHEN month = 'Mar' THEN revenue ELSE 0 END) AS mar
FROM sales
GROUP BY category;

Материализованное представление (MATERIALIZED VIEW) хранит результат на диске и не пересчитывается автоматически. Его нужно обновлять вручную командой REFRESH MATERIALIZED VIEW sales_wide;. Зато чтение из него работает как из обычной таблицы и значительно быстрее на больших объёмах.

Когда использовать каждый вариант:

• VIEW подходит для небольших таблиц или когда данные обновляются несколько раз в день и нужна свежесть.
• MATERIALIZED VIEW оправдан для тяжёлых агрегатов, которые вычисляются долго, а читаются часто. Например, ежедневный отчёт по выручке за квартал.
• CTE внутри запроса удобен, когда преобразование нужно только в конкретном контексте и не планируется переиспользовать.

Несколько метрик в одной «широкой» таблице

Иногда на пересечении строки и столбца нужно показать не одно значение, а несколько. Например, выручка и количество заказов одновременно. С CASE WHEN это решается прямолинейно: добавьте столько выражений, сколько нужно метрик.

SELECT
  category,
  SUM(CASE WHEN month = 'Jan' THEN revenue END) AS jan_revenue,
  COUNT(CASE WHEN month = 'Jan' THEN 1 END)    AS jan_orders,
  SUM(CASE WHEN month = 'Feb' THEN revenue END) AS feb_revenue,
  COUNT(CASE WHEN month = 'Feb' THEN 1 END)    AS feb_orders
FROM sales
GROUP BY category;

Результат читается хорошо, если метрик две-три. При большем числе имена столбцов быстро становятся длинными и запрос труднее воспринимать. В таком случае стоит рассмотреть альтернативу: оставить данные в «длинном» формате и строить «широкий» вид уже в инструменте визуализации, например в Metabase, Redash или Superset.

С crosstab несколько метрик реализуются сложнее. Функция принимает ровно одно значение на пересечении. Обходной путь: запустить отдельный crosstab для каждой метрики и объединить результаты через JOIN по ключевому полю.

SELECT r.category, r.jan AS jan_revenue, o.jan AS jan_orders
FROM crosstab(
  'SELECT category, month, SUM(revenue)::NUMERIC FROM sales GROUP BY category, month ORDER BY 1,2',
  'VALUES (''Jan''), (''Feb'')'
) AS r(category TEXT, jan NUMERIC, feb NUMERIC)
JOIN crosstab(
  'SELECT category, month, COUNT(*)::INT FROM sales GROUP BY category, month ORDER BY 1,2',
  'VALUES (''Jan''), (''Feb'')'
) AS o(category TEXT, jan INT, feb INT) USING (category);

Это работает, но синтаксис громоздкий. Для нескольких метрик CASE WHEN почти всегда выигрывает по читаемости.

Когда pivot в PostgreSQL не нужен

Не каждая задача требует преобразования данных прямо в базе. Иногда это лишняя работа, которая усложняет поддержку без реальной пользы.

Сравнение подходов по сценариям использования

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