PIVOT в SQL — разворот строк в столбцы

Коротко

PIVOT превращает строки в столбцы — из «длинного» формата в «широкий». Типичная задача: таблица с колонками (month, metric_name, value) → таблица с колонками (month, revenue, users, orders). Обратная операция — UNPIVOT (из широкого в длинный). В PostgreSQL нет встроенного PIVOT — используют CASE WHEN или CROSSTAB. На собеседованиях PIVOT встречается в задачах на формирование отчётов.

PIVOT через CASE WHEN (универсальный способ)

Работает во всех СУБД. Самый частый подход:

-- Исходные данные (длинный формат):
-- month    | category    | revenue
-- 2025-01  | electronics | 500000
-- 2025-01  | books       | 120000
-- 2025-02  | electronics | 550000
-- 2025-02  | books       | 95000

-- PIVOT: категории → столбцы
SELECT
    month,
    SUM(CASE WHEN category = 'electronics' THEN revenue END) AS electronics,
    SUM(CASE WHEN category = 'books' THEN revenue END) AS books
FROM monthly_sales
GROUP BY month
ORDER BY month;

-- Результат (широкий формат):
-- month    | electronics | books
-- 2025-01  | 500000      | 120000
-- 2025-02  | 550000      | 95000

Паттерн: SUM(CASE WHEN category = 'X' THEN value END) для каждого значения, которое хотим превратить в столбец.

Выручка по месяцам (помесячные столбцы)

SELECT
    user_id,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN amount ELSE 0 END) AS jan,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN amount ELSE 0 END) AS feb,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3 THEN amount ELSE 0 END) AS mar
FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2025-04-01'
GROUP BY user_id;

Матрица retention

SELECT
    cohort_month,
    COUNT(DISTINCT CASE WHEN month_number = 0 THEN user_id END) AS m0,
    COUNT(DISTINCT CASE WHEN month_number = 1 THEN user_id END) AS m1,
    COUNT(DISTINCT CASE WHEN month_number = 2 THEN user_id END) AS m2,
    COUNT(DISTINCT CASE WHEN month_number = 3 THEN user_id END) AS m3
FROM (
    SELECT
        u.user_id,
        DATE_TRUNC('month', u.created_at) AS cohort_month,
        DATE_PART('month', AGE(DATE_TRUNC('month', e.event_date), DATE_TRUNC('month', u.created_at)))::INT AS month_number
    FROM users u
    JOIN events e ON u.user_id = e.user_id
) cohort_data
GROUP BY cohort_month
ORDER BY cohort_month;

Классическая задача — построить таблицу retention по когортам. CASE WHEN разворачивает месяцы в столбцы.

CROSSTAB в PostgreSQL

PostgreSQL предлагает tablefunc модуль с функцией crosstab:

-- Включить модуль (один раз)
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- CROSSTAB
SELECT *
FROM crosstab(
    'SELECT month, category, SUM(revenue)
     FROM monthly_sales
     GROUP BY month, category
     ORDER BY month, category',
    'SELECT DISTINCT category FROM monthly_sales ORDER BY category'
) AS ct(month TEXT, books NUMERIC, electronics NUMERIC);

CROSSTAB чище CASE WHEN, но требует расширения и фиксированного списка столбцов.

PIVOT в других СУБД

SQL Server / Azure SQL

SELECT month, electronics, books
FROM monthly_sales
PIVOT (
    SUM(revenue)
    FOR category IN (electronics, books)
) AS pvt;

SQL Server имеет встроенный оператор PIVOT — самый чистый синтаксис.

ClickHouse

SELECT
    month,
    sumIf(revenue, category = 'electronics') AS electronics,
    sumIf(revenue, category = 'books') AS books
FROM monthly_sales
GROUP BY month;

sumIf, countIf — ClickHouse-функции, аналогичные SUM(CASE WHEN ...) но компактнее.

MySQL

MySQL не имеет PIVOT — используйте CASE WHEN (универсальный способ выше).

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

Из широкого формата в длинный:

-- Исходные данные (широкий формат):
-- user_id | jan  | feb  | mar
-- 1       | 5000 | 3200 | 8100

-- UNPIVOT через UNION ALL
SELECT user_id, 'jan' AS month, jan AS revenue FROM user_revenue
UNION ALL
SELECT user_id, 'feb' AS month, feb AS revenue FROM user_revenue
UNION ALL
SELECT user_id, 'mar' AS month, mar AS revenue FROM user_revenue;

-- Результат (длинный формат):
-- user_id | month | revenue
-- 1       | jan   | 5000
-- 1       | feb   | 3200
-- 1       | mar   | 8100

PostgreSQL: UNPIVOT через LATERAL

SELECT user_id, month, revenue
FROM user_revenue
CROSS JOIN LATERAL (
    VALUES
        ('jan', jan),
        ('feb', feb),
        ('mar', mar)
) AS t(month, revenue);

LATERAL VALUES — элегантнее UNION ALL и быстрее (одно сканирование таблицы).

PIVOT в pandas

Аналог в pandas:

# PIVOT
pivot = df.pivot_table(
    index='month',
    columns='category',
    values='revenue',
    aggfunc='sum'
)

# UNPIVOT (melt)
melted = pivot.reset_index().melt(
    id_vars='month',
    var_name='category',
    value_name='revenue'
)

Подробнее — в гайде по pivot_table.

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

Забыли агрегацию. CASE WHEN category = 'X' THEN revenue END без SUM — одна строка на комбинацию. Нужен SUM(CASE ...) + GROUP BY.

Динамические столбцы. PIVOT через CASE WHEN требует перечислить все значения заранее. Если категорий 100 — нужен динамический SQL или pandas.

NULL вместо 0. CASE WHEN без ELSE возвращает NULL. Если нужны нули: SUM(CASE ... ELSE 0 END) или COALESCE(SUM(CASE ...), 0).

Вопросы с собеседований

-- Как развернуть строки в столбцы в SQL? -- Через CASE WHEN: SUM(CASE WHEN col = 'val' THEN metric END) + GROUP BY. В SQL Server — оператор PIVOT. В PostgreSQL — CROSSTAB (tablefunc).

-- Как построить таблицу retention по когортам? -- GROUP BY когорта, CASE WHEN month_number = N для каждого месяца. Каждый столбец — COUNT DISTINCT users для соответствующего месяца жизни когорты.

-- Как сделать обратную операцию (UNPIVOT)? -- UNION ALL с каждым столбцом. В PostgreSQL: CROSS JOIN LATERAL VALUES. В pandas: melt().

-- В чём проблема PIVOT для динамических данных? -- Столбцы нужно перечислить заранее. Если значений заранее неизвестно — нужен динамический SQL (EXECUTE), что сложнее. Альтернатива — делать PIVOT в приложении или pandas.

FAQ

PIVOT vs pivot_table в pandas?

SQL PIVOT — статичный, столбцы перечисляются явно. pandas pivot_table — динамический, столбцы создаются автоматически. Для отчётов с фиксированной структурой — SQL. Для EDA — pandas.

Как обработать большое количество значений?

Если значений > 10–15, PIVOT в SQL становится громоздким. Варианты: динамический SQL, JSONB-агрегация (jsonb_object_agg в PostgreSQL), или PIVOT в pandas/BI-инструменте.

CROSSTAB vs CASE WHEN?

CROSSTAB чище синтаксически, но требует расширение tablefunc и фиксированный тип результата. CASE WHEN — универсальный, работает в любой СУБД, проще для отладки.

Как тренироваться

PIVOT — частая задача на SQL-собеседованиях. Задачи на агрегации и формирование отчётов — в тренажёре Карьерник. Больше вопросов — в разделе с примерами.