PIVOT и UNPIVOT в SQL: строки в столбцы и обратно

Коротко: разворот не меняет определение метрики

PIVOT переводит значения одной колонки в набор колонок, агрегируя повторные строки. UNPIVOT делает обратный шаг: имена нескольких колонок становятся значениями одной строки. Эти операции меняют форму, не дают нового смысла числам. До разворота определите зерно: у нас одна строка исходника — событие, одна строка широкого результата — платформа.

В PostgreSQL 14 удобный переносимый вариант — count(*) FILTER (WHERE ...) или sum(CASE WHEN ... THEN 1 ELSE 0 END). В DuckDB есть самостоятельный PIVOT; у PostgreSQL в стандартном SELECT такого оператора нет, но расширение tablefunc содержит crosstab. Если ещё нет ясного зерна, прочитайте разбор кардинальности JOIN.

Поворот не обязателен для анализа. Длинный формат лучше переносит появление новой категории: новая строка не меняет набор полей результата. Широкий формат удобен, когда человек должен сравнить три конкретных шага рядом или когда контракт витрины требует эти три колонки. Поэтому задача начинается не с выбора синтаксиса PIVOT, а с вопроса, кто будет читать итог и какие категории считаются обязательными.

Три шага в длинной таблице

Ограничим время полуоткрытым интервалом от 1 до 4 июля и оставим search, checkout_start, purchase. Всего в этих шагах 27 386 событий. Повторные доставки здесь не удаляем: вопрос именно о строках журнала, а не об уникальных клиентах. Если отчёт должен считать людей, понадобится другое зерно и count(DISTINCT client_id) по каждому шагу.

В длинной форме android/search — 7 744 строки, android/checkout_start — 2 694, android/purchase — 1 506. Такая таблица удобна для дальнейших фильтров и графиков; широкая нужна для одного сравнительного отчёта.

Одна и та же покупка может появиться в журнале повторно из-за доставки события. Здесь это не ошибка запроса: мы честно считаем сырые строки. Но подпись «покупатели» была бы неверной. Если нужен продуктовый показатель, отделите таблицу событий от таблицы пользователей, выберите правило дедупликации и только затем разворачивайте. PIVOT прекрасно замаскирует неудачное определение метрики в аккуратных колонках, поэтому контроль до него важнее самого поворота.

PostgreSQL: условная агрегация

Каждая колонка считает строки одной категории. count(*) FILTER возвращает ноль для пустой категории в существующей группе — полезное отличие от sum(CASE WHEN ... THEN 1 END), которая без ELSE может вернуть NULL. Итоговая сумма трёх колонок должна равняться count(*) по строке, поскольку вход заранее ограничен этими тремя шагами.

На android 7 744 + 2 694 + 1 506 = 11 944. По всем платформам результат — три строки. Если список шагов расширится, колонку придётся добавить в SELECT и в контроль равенства; сам по себе PIVOT не знает, какие значения бизнес считает полным набором.

Проверяйте сумму по каждой строке, а не только общий итог: ошибка в одном имени шага может частично компенсироваться случайным расширением другого фильтра. Здесь суммы по ios и web равны 8 034 и 7 408. Общий итог 27 386 получается как сумма этих трёх платформ. Это полезный инвариант, пока платформа у всех выбранных событий заполнена; если появится настоящий NULL, он станет отдельной строкой группировки, которую надо назвать.

SELECT platform,
       count(*) FILTER (WHERE event_name='search') AS searches,
       count(*) FILTER (WHERE event_name='checkout_start') AS checkouts,
       count(*) FILTER (WHERE event_name='purchase') AS purchases,
       count(*) AS total
FROM app_events
WHERE event_name IN ('search','checkout_start','purchase')
  AND event_time >= TIMESTAMP '2026-07-01'
  AND event_time < TIMESTAMP '2026-07-04'
GROUP BY platform ORDER BY platform;

DuckDB PIVOT: короче, но только в своём диалекте

DuckDB умеет объявить ось через ON event_name IN (...), агрегат через USING count(*), оставшееся поле через GROUP BY platform. На тех же Parquet выход 7 744/2 694/1 506 для android. Синтаксис не переносится в PostgreSQL как есть; для статьи и рабочего SQL, который должен жить в обоих движках, условная агрегация проще.

Если убрать список IN, набор колонок зависит от фактически встреченных событий. Это опасно для потребителя отчёта: сегодня нет категории — и колонка исчезла. Явный словарь категорий или фиксированный FILTER делает контракт результата стабильным.

Условная агрегация иногда выглядит длиннее, зато у неё видны все бизнес-имена и фильтры в одном SELECT. В отчёте с небольшой, заранее известной осью это преимущество: ревьюер сразу замечает, что checkout_start включён, а payment_submit намеренно отсутствует. DuckDB PIVOT полезнее, когда ось шире или вопрос исследовательский, но сохранить результат для внешнего потребителя всё равно стоит с описанной схемой.

PIVOT (
  SELECT platform, event_name FROM app_events
  WHERE event_name IN ('search','checkout_start','purchase')
    AND event_time >= TIMESTAMP '2026-07-01'
    AND event_time < TIMESTAMP '2026-07-04'
) ON event_name IN ('search','checkout_start','purchase')
USING count(*) GROUP BY platform ORDER BY platform;

UNPIVOT: вернуть длинную форму

После расчёта широкой таблицы BI-инструмент может снова потребовать три строки на платформу. В PostgreSQL CROSS JOIN LATERAL (VALUES ...) переносит имена колонок в step и значения в events. Отдельный запрос не читает журнал второй раз, если широкий результат уже сохранён как витрина.

В DuckDB есть UNPIVOT; он даёт те же три строки на android. Проверка обратимости здесь — 3 платформы × 3 шага = 9 строк и сумма 27 386. Но если исходный шаг был отсутствующим, а при PIVOT вы заменили NULL на 0, после обратного шага уже нельзя отличить «измеренный ноль» от «не было исходной записи».

Обратимость касается формы агрегированной таблицы, не исходных событий. После PIVOT каждое событие уже вошло в число, и UNPIVOT не восстановит его event_id, время или клиента. Это частая ошибка при подготовке выгрузки: длинная таблица после UNPIVOT выглядит как исходник, но её зерно — платформа × шаг, а не событие. Для последующего анализа путей нужен исходный журнал, а не развёрнутая сводка.

WITH wide AS (
  SELECT platform,
         count(*) FILTER (WHERE event_name='search') AS searches,
         count(*) FILTER (WHERE event_name='checkout_start') AS checkouts,
         count(*) FILTER (WHERE event_name='purchase') AS purchases
  FROM app_events
  WHERE event_name IN ('search','checkout_start','purchase')
    AND event_time >= TIMESTAMP '2026-07-01' AND event_time < TIMESTAMP '2026-07-04'
  GROUP BY platform
)
SELECT w.platform, x.step, x.events
FROM wide w CROSS JOIN LATERAL (VALUES
  ('search',w.searches),('checkout_start',w.checkouts),('purchase',w.purchases)
) AS x(step,events)
ORDER BY w.platform,x.step;

DuckDB UNPIVOT и отличие от LATERAL

DuckDB записывает обратный разворот короче: UNPIVOT ... ON ... INTO NAME ... VALUE .... В примере ниже вход — одна широкая строка, поэтому выход ровно три. У PostgreSQL LATERAL VALUES выполняет ту же структурную задачу, не требуя расширения.

Не смешивайте это с CROSS JOIN LATERAL к JSON-массиву: там количество строк зависит от числа элементов в конкретной записи. Здесь число строк заранее равно числу выбранных колонок. Разбор LATERAL помогает увидеть общую механику без смешения задач.

UNPIVOT (
  SELECT 'android' AS platform, 7744 AS searches, 2694 AS checkouts, 1506 AS purchases
) ON searches, checkouts, purchases INTO NAME step VALUE events;

То же в pandas: pivot_table и melt

В pandas pivot не агрегирует дубли ключа. Если у android/search 7 744 исходные строки, pivot(index="platform", columns="event_name", values="client_id") падает: он не знает, какое значение поставить в одну клетку. pivot_table(..., aggfunc="size") явно считает строки, а melt возвращает длинный формат.

Один блок ниже воспроизводит обе ветки. Он печатает три числа android и число строк после melt — 9. Важно задавать fill_value=0 только когда бизнес действительно хочет явные нули: пропавшая категория и нулевое событие — разные сигналы.

В pandas исходный pivot чаще всего падает именно на реальном журнале, где одно сочетание платформы и шага встречается тысячи раз. Это полезная ошибка: она заставляет выбрать агрегат. В SQL запрос без агрегата тоже не может просто сложить тысячи client_id в одну ячейку. Не исправляйте ошибку pandas drop_duplicates по паре платформа-шаг: так останется случайное событие, а не измеренная частота.

import pandas as pd

e = pd.read_parquet('app_events.parquet', columns=['event_time', 'event_name', 'platform', 'client_id'])
e['event_time'] = pd.to_datetime(e['event_time'])
start, end = pd.Timestamp('2026-07-01'), pd.Timestamp('2026-07-04')
e = e.loc[e.event_name.isin(['search','checkout_start','purchase']) & e.event_time.ge(start) & e.event_time.lt(end)]
try:
    e.pivot(index='platform', columns='event_name', values='client_id')
except ValueError as error:
    print(type(error).__name__)
wide = e.pivot_table(index='platform', columns='event_name', values='client_id', aggfunc='size', fill_value=0)
long = wide.reset_index().melt(id_vars='platform', var_name='step', value_name='events')
print(int(wide.loc['android','search']), int(wide.loc['android','checkout_start']), int(wide.loc['android','purchase']))
print(len(long), int(long.events.sum()))

Ловушка 1: не назвали зерно до разворота

Неправильно брать сырые строки и ожидать одну запись в клетке. У android/search 7 744 события. В pandas pivot откажется с ValueError, а SQL PIVOT всё равно потребует агрегат. Исправление — выбрать count(*) для событий или count(DISTINCT client_id) для людей и подписать результат.

Если сначала соединить события с другой таблицей один-ко-многим, PIVOT честно агрегирует уже размноженные строки. Сравните общий count(*) до и после JOIN ещё до поворота.

Ловушка 2: опечатка создаёт правдоподобный ноль

Неправильный фильтр event_name='purchaze' не даёт ошибку: колонка purchase превратится в нули на всех трёх платформах вместо 1 506/986/1 090. Исправление — сверять фактический словарь SELECT DISTINCT event_name и общий итог 27 386 с суммой ожидаемых колонок.

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

Ловушка 3: категории нет в периоде

Если строить динамический PIVOT только по увиденным значениям, категория без событий не даст колонки. Для фиксированного API отчёта это нарушение контракта, даже если в реальном мире событий действительно ноль. Явный список трёх шагов удерживает девять клеток результата.

Та же проблема возникает в строках календаря. Для отсутствующих дней нужен отдельный ряд через generate_series, для отсутствующих категорий — список допустимых значений; затем LEFT JOIN фактов.

Ловушка 4: NULL подменён нулём слишком рано

В результате условного count пустая категория в существующей платформе имеет 0. В результате sum(CASE WHEN ... THEN 1 END) без ELSE она получила бы NULL. Если эти состояния различаются в продукте, не делайте глобальный coalesce до решения о качестве данных.

Случай с настоящим пропуском платформы ещё сложнее: строка с NULL-platform станет отдельной группой, а не нулём в android. Проверяйте оси и факты отдельно.

Ловушка 5: сумма стала похожа на конверсию

Делить 1 506 purchase на 7 744 search для android и называть результат конверсией клиентов нельзя: числитель и знаменатель здесь события с повторами. Один клиент может искать несколько раз и покупать несколько раз. Исправление — пересчитать разных клиентов на каждом шаге, определить окно и убедиться, что покупатель входит в когорту искавших.

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

В других СУБД

PostgreSQL 14: FILTER и CASE в агрегатах, обратный ход через LATERAL VALUES. Расширение tablefunc документирует crosstab, но требует отдельного решения о схеме выходных столбцов. DuckDB 1.5.4: PIVOT и UNPIVOT выполнены выше. SQL Server поддерживает собственный синтаксис PIVOT/UNPIVOT; запросы разных движков не заменяют друг друга посимвольно.

Для переносимой витрины с фиксированными колонками условная агрегация часто прозрачнее. Если список категорий динамический, формирование схемы результата становится отдельной задачей в приложении или BI, а не магическим свойством PIVOT.

При переносе сравнивайте не только названия колонок. У одной системы отсутствующая категория может стать NULL, у другой — нулём после явного COALESCE; одна вернёт колонку только при наличии значения, другая — благодаря фиксированному списку. Проверочный набор должен содержать и повторные события, и пустую клетку, и незнакомую категорию. Наш июльский срез демонстрирует повторы; для теста пустой клетки используйте отдельный маленький синтетический набор, не выдавая его за авиаданные.

Частые вопросы

Есть ли PIVOT в PostgreSQL? Отдельного оператора PIVOT в SELECT нет; используйте FILTER/CASE или документированный crosstab из tablefunc.

Чем pivot отличается от pivot_table в pandas? Первый требует уникальную пару ключей, второй агрегирует повторы выбранной функцией.

Как вернуть строки из колонок? В PostgreSQL — LATERAL VALUES либо UNION ALL; в DuckDB — UNPIVOT; в pandas — melt.

Можно ли считать NULL нулём? Только после определения ожидаемой сетки и проверки, что строка действительно отсутствует, а не потеряна при загрузке. Для событийной витрины явно храните версию словаря шагов: новое имя события не должно молча превратить старую колонку в ноль.