PIVOT в SQL: превращаем строки в столбцы

PIVOT — это операция преобразования строк в столбцы. В SQL Server и Oracle есть ключевое слово PIVOT, но в PostgreSQL его нет. Зато есть несколько подходов, каждый со своими плюсами. Разберём их на реальном примере: отчёт по выручке категорий товаров по месяцам.

Исходные данные

Предположим, что у нас есть таблица продаж:

CREATE TABLE sales (
  sale_date  DATE,
  category   TEXT,
  amount     NUMERIC
);

Хотим получить такой отчёт:

Способ 1: CASE WHEN + агрегация (ручной PIVOT)

Самый переносимый способ — работает в любой СУБД:

SELECT
  DATE_TRUNC('month', sale_date)::DATE AS month,
  SUM(CASE WHEN category = 'electronics' THEN amount ELSE 0 END) AS electronics,
  SUM(CASE WHEN category = 'clothing'    THEN amount ELSE 0 END) AS clothing,
  SUM(CASE WHEN category = 'books'       THEN amount ELSE 0 END) AS books
FROM sales
GROUP BY 1
ORDER BY 1;

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

Практический совет: используйте FILTER вместо CASE WHEN — синтаксис чище:

SELECT
  DATE_TRUNC('month', sale_date)::DATE AS month,
  SUM(amount) FILTER (WHERE category = 'electronics') AS electronics,
  SUM(amount) FILTER (WHERE category = 'clothing')    AS clothing,
  SUM(amount) FILTER (WHERE category = 'books')       AS books
FROM sales
GROUP BY 1
ORDER BY 1;

Способ 2: crosstab из расширения tablefunc

PostgreSQL поставляется с расширением tablefunc, которое добавляет функцию crosstab. Её нужно включить один раз:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Затем пишем запрос в два шага. Функция crosstab принимает два запроса: первый — данные, второй — список значений-столбцов:

SELECT *
FROM crosstab(
  -- Запрос 1: данные, отсортированные по (row_name, category)
  $$
    SELECT
      DATE_TRUNC('month', sale_date)::TEXT AS month,
      category,
      SUM(amount)::NUMERIC
    FROM sales
    GROUP BY 1, 2
    ORDER BY 1, 2
  $$,
  -- Запрос 2: уникальные значения категорий
  $$
    SELECT DISTINCT category
    FROM sales
    ORDER BY 1
  $$
) AS ct(
  month        TEXT,
  books        NUMERIC,
  clothing     NUMERIC,
  electronics  NUMERIC
);

Важно: столбцы в AS ct(...) должны идти в том же порядке, что возвращает второй запрос. Если категория отсутствует для данного месяца, crosstab подставит NULL.

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

Способ 3: динамический PIVOT через PL/pgSQL

Когда список категорий заранее неизвестен — строим запрос динамически:

CREATE OR REPLACE FUNCTION pivot_sales_by_category()
RETURNS VOID AS $$
DECLARE
  cols      TEXT;
  sql_query TEXT;
BEGIN
  -- Собираем список столбцов
  SELECT STRING_AGG(
    FORMAT(
      'SUM(amount) FILTER (WHERE category = %L) AS %I',
      category,
      category
    ),
    ', '
    ORDER BY category
  )
  INTO cols
  FROM (SELECT DISTINCT category FROM sales) c;

  -- Строим и выполняем запрос
  sql_query := FORMAT(
    'SELECT DATE_TRUNC(''month'', sale_date)::DATE AS month, %s
     FROM sales GROUP BY 1 ORDER BY 1',
    cols
  );

  EXECUTE sql_query;
END;
$$ LANGUAGE plpgsql;

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

UNPIVOT: обратная операция

Иногда нужно сделать обратное — превратить столбцы в строки. В PostgreSQL для этого удобен UNNEST с массивами или VALUES:

-- Исходная таблица с "широким" форматом
SELECT month, category, revenue
FROM monthly_pivot
CROSS JOIN LATERAL (
  VALUES
    ('electronics', electronics),
    ('clothing',    clothing),
    ('books',       books)
) AS unpivoted(category, revenue)
ORDER BY month, category;

Реальный отчёт: выручка по кварталам и каналам продаж

Объединим всё в один практичный запрос:

WITH base AS (
  SELECT
    DATE_PART('year',    sale_date)::INT AS year,
    DATE_PART('quarter', sale_date)::INT AS quarter,
    channel,
    SUM(amount) AS revenue
  FROM sales
  GROUP BY 1, 2, 3
)
SELECT
  year,
  quarter,
  SUM(revenue) FILTER (WHERE channel = 'online')  AS online,
  SUM(revenue) FILTER (WHERE channel = 'offline') AS offline,
  SUM(revenue) FILTER (WHERE channel = 'mobile')  AS mobile,
  SUM(revenue) AS total
FROM base
GROUP BY 1, 2
ORDER BY 1, 2;

Когда какой способ использовать

Итог

В PostgreSQL нет встроенного PIVOT, но условная агрегация через FILTER покрывает большинство задач. Расширение tablefunc с функцией crosstab подходит для производительных статических отчётов. Для по-настоящему динамических таблиц — стройте SQL в приложении или в PL/pgSQL.

Потренируйтесь на реальных задачах в нашем тренажёре SQL — там есть задачи на агрегацию и аналитические запросы.