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 - функция является «чёрным ящиком» для планировщика, и иногда вспомогательный запрос с предварительной агрегацией работает быстрее.

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

Вакансии для аналитиков

Preply

сегодня

data analyst for an educational project

зп не указана

senior

офис Барселона

Tamaska

сегодня

системный аналитик админ-панели управления рекламой

зп не указана

senior

удалённо

NDA

сегодня

продуктовый аналитик международных поставок для отелей

зп не указана

senior

удалённо

Вакансии для аналитиков

Preply

сегодня

data analyst for an educational project

зп не указана

senior

офис Барселона

Tamaska

сегодня

системный аналитик админ-панели управления рекламой

зп не указана

senior

удалённо

NDA

сегодня

продуктовый аналитик международных поставок для отелей

зп не указана

senior

удалённо

Неправильный порядок в 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 не нужен

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

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

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

FAQ

Как подключить tablefunc в PostgreSQL?

Выполните CREATE EXTENSION IF NOT EXISTS tablefunc; от имени пользователя с правами суперпользователя или с ролью, которой разрешено создавать расширения. Команду нужно запустить один раз в нужной базе данных. Проверить, что расширение активно: SELECT * FROM pg_extension WHERE extname = 'tablefunc';.

Можно ли сделать pivot с динамическим набором столбцов в чистом SQL?

Нет. Стандартный SQL требует фиксированный список столбцов на этапе разбора запроса. Для динамического набора нужно либо строить запрос программно на стороне приложения, либо использовать PL/pgSQL с EXECUTE. Альтернатива - агрегация в JSONB, если «широкий» формат не обязателен.

Чем отличается CASE WHEN pivot sql от crosstab?

CASE WHEN - это стандартный SQL без зависимостей, легко читается, удобен для небольшого числа столбцов. crosstab - функция из расширения tablefunc, декларативнее при большом числе категорий, но требует строгого формата входных данных и правильной типизации. Семантически оба подхода решают одну задачу.

Почему crosstab возвращает неправильные значения?

Скорее всего, используется однаргументная версия при наличии пропущенных комбинаций. Функция заполняет столбцы значениями подряд, не зная, что какого-то значения нет. Решение: перейти на двухаргументную версию с явным списком категорий и убедиться, что данные отсортированы по row_name и category.

Как сделать сводную таблицу с несколькими метриками (не одной)?

CASE WHEN легко масштабируется: добавьте отдельные выражения для каждой метрики. Например, SUM(CASE WHEN month='Jan' THEN revenue END) AS jan_rev и COUNT(CASE WHEN month='Jan' THEN 1 END) AS jan_orders в одном запросе. С crosstab это сложнее - функция поддерживает только одно значение на пересечении, придётся делать несколько crosstab и объединять через JOIN.

Работает ли crosstab в Amazon RDS или Supabase?

В Amazon RDS для PostgreSQL расширение tablefunc поддерживается и включено в список разрешённых расширений. В Supabase tablefunc тоже доступен. Команда CREATE EXTENSION tablefunc выполняется стандартно. Если возникает ошибка прав - обратитесь к документации вашего провайдера по управлению расширениями.

Итог

Для большинства аналитических задач CASE WHEN - достаточный и предсказуемый инструмент. Он не требует расширений, легко встраивается в сложные запросы и понятен коллегам без объяснений. crosstab оправдывает себя, когда категорий много и писать десятки CASE-выражений неудобно, но требует аккуратности с сортировкой и типами.

Главное ограничение обоих подходов - статичность: список столбцов нужно знать заранее. Если категории в данных меняются, проще строить итоговый SQL в коде приложения или дашборда, а не пытаться обойти это средствами самого PostgreSQL.

Попробуйте оба шаблона на своих данных. После первого рабочего запроса разница между «выгрузить и сделать в Excel» и «получить результат прямо из базы» станет очевидной.