UNPIVOT в SQL: как развернуть столбцы в строки

Развернуть столбцы в строки — это когда таблица с колонками jan, feb, mar превращается в таблицу с двумя колонками month и revenue, где каждый бывший столбец стал отдельной строкой. Такая операция называется UNPIVOT, и она зеркальна к PIVOT. Короткий ответ на «как»: в PostgreSQL готового оператора нет — разворачивают через UNION ALL или, что аккуратнее и быстрее, через CROSS JOIN LATERAL (VALUES ...). В SQL Server и Oracle есть ключевое слово UNPIVOT. В pandas та же задача решается методом melt. Всё остальное — детали, из-за которых запрос либо читается за секунду, либо превращается в простыню на двадцать UNION-ов.

Что такое UNPIVOT и когда он нужен?

UNPIVOT переводит данные из «широкого» формата в «длинный». Широкая таблица — та, где значения одного показателя размазаны по нескольким колонкам. Классика жанра: кто-то выгрузил отчёт из таблицы Excel, и выручка по менеджерам легла вот так.

-- Широкая таблица sales_wide: выручка по месяцам
-- manager | jan | feb | mar
-- Иван    | 100 | 120 |  90
-- Анна    | 200 | 180 | 210

С такими данными неудобно работать: чтобы посчитать сумму за квартал, нужно писать jan + feb + mar, а как только добавится апрель — переписывать все запросы, где эти колонки упоминались. Длинный вариант той же таблицы выглядит скучнее, но с ним живётся легче.

-- Длинная таблица sales_long
-- manager | month | revenue
-- Иван    | jan   | 100
-- Иван    | feb   | 120
-- Иван    | mar   |  90
-- Анна    | jan   | 200
-- ...

Названия месяцев уехали в отдельную колонку-измерение, а числа — в колонку-значение. Именно этого хотят почти все инструменты аналитики, и именно к этому виду вы приводите данные, когда делаете UNPIVOT. Если само понятие «широкий против длинного» пока плывёт, стоит сначала укрепить базу по выборкам и группировкам — например, на вводном курсе по SQL, а потом возвращаться к развороту.

Как развернуть столбцы в строки в PostgreSQL?

Самый лобовой способ, который работает в любой СУБД, — собрать результат из отдельных SELECT через UNION ALL. По одному подзапросу на каждый разворачиваемый столбец.

SELECT manager, 'jan' AS month, jan AS revenue FROM sales_wide
UNION ALL
SELECT manager, 'feb' AS month, feb AS revenue FROM sales_wide
UNION ALL
SELECT manager, 'mar' AS month, mar AS revenue FROM sales_wide;

Читается прозрачно, и на трёх колонках это нормально. Но у подхода два минуса. Во-первых, база читает sales_wide столько раз, сколько у вас UNION ALL-веток — для трёх месяцев три прохода, для двенадцати двенадцать. На большой таблице это ощутимо бьёт по времени. Во-вторых, добавление нового месяца означает дописать ещё один блок и не забыть про запятые и алиасы. Когда я вижу такой запрос на пятнадцать веток, обычно это первый кандидат на переписывание.

Чем LATERAL и VALUES лучше UNION ALL?

SELECT s.manager, v.month, v.revenue
FROM sales_wide AS s
CROSS JOIN LATERAL (
    VALUES
        ('jan', s.jan),
        ('feb', s.feb),
        ('mar', s.mar)
) AS v(month, revenue);

Есть и однострочник через unnest — он разворачивает два массива параллельно, элемент к элементу.

SELECT manager, month, revenue
FROM sales_wide,
     unnest(
         ARRAY['jan', 'feb', 'mar'],
         ARRAY[jan, feb, mar]
     ) AS u(month, revenue);

Компактно, но у массива значений должен быть единый тип, а длины обоих массивов обязаны совпадать. Для аккуратных числовых колонок работает отлично; когда типы разнородные, я всё же возвращаюсь к LATERAL VALUES — там проще расставить приведения типов по месту.

Как работает оператор UNPIVOT в SQL Server и Oracle?

Если под рукой SQL Server или Oracle, разворот пишется штатным оператором UNPIVOT, и это заметно короче.

SELECT manager, month, revenue
FROM sales_wide
UNPIVOT (
    revenue FOR month IN (jan, feb, mar)
) AS u;

Читается почти по-английски: значения собираем в колонку revenue, имена столбцов — в колонку month, а разворачиваем перечисленные в скобках jan, feb, mar. У оператора есть важная особенность, о которой легко забыть: UNPIVOT по умолчанию выбрасывает строки, где значение равно NULL. Если у Ивана в марте не было продаж и там стоит NULL, строка Иван | mar в результат не попадёт вовсе. Иногда это ровно то, что нужно, а иногда — незаметно потерянные данные. Когда NULL-ы важны, в SQL Server их сохраняют через CROSS APPLY (VALUES ...) — прямой аналог постгресовского LATERAL VALUES, который NULL-ы не трогает. В PostgreSQL оператора UNPIVOT нет вообще, так что там выбор между ветками UNION ALL и LATERAL — единственный, и он в пользу второго.

Почему длинный формат удобнее для агрегаций и BI?

Длинный формат окупается на первой же агрегации. Сумму по всем месяцам в широкой таблице пришлось бы складывать перечислением колонок, а в длинной это обычный GROUP BY.

SELECT month, SUM(revenue) AS total
FROM sales_long
GROUP BY month
ORDER BY month;

Добавился апрель — таблица получит новые строки, а запрос вообще не изменится. В широком варианте пришлось бы лезть в SELECT и дописывать SUM(apr). Тот же принцип в BI-инструментах: дашборды и графики любят «опрятные» данные, где одна строка — одно наблюдение, а колонки — это переменные. Разбивку по месяцам на графике вы задаёте одним измерением month, а не тычете в двенадцать отдельных полей.

Особенно наглядно это на таблице метрик, где показатели лежат по колонкам. Разворот превращает её в набор пар «метрика — значение», и любой новый показатель ложится в ту же схему.

-- было: metrics_daily(day, dau, wau, revenue)
SELECT m.day, x.metric, x.value
FROM metrics_daily AS m
CROSS JOIN LATERAL (
    VALUES
        ('dau',     m.dau::numeric),
        ('wau',     m.wau::numeric),
        ('revenue', m.revenue::numeric)
) AS x(metric, value);

Теперь любой график строится по колонке metric, а фильтр WHERE metric = 'dau' вытащит только нужный ряд — без переписывания структуры. Если хочется освежить, что скрывается за самими показателями, у меня разобран, скажем, DAU и его подводные камни.

Чем UNPIVOT в SQL похож на melt в pandas?

Если вы разворачивали таблицы в pandas, то UNPIVOT вам знаком под именем melt — это ровно та же операция.

import pandas as pd

long_df = sales_wide.melt(
    id_vars=["manager"],
    value_vars=["jan", "feb", "mar"],
    var_name="month",
    value_name="revenue",
)

Соответствие один в один: id_vars — это колонки, которые остаются как есть (наш manager), value_vars — те, что разворачиваем (jan, feb, mar), var_name задаёт имя колонки с названиями (month), value_name — колонки со значениями (revenue). Обратная операция в pandas — pivot_table, ровно как PIVOT разворачивает длинное обратно в широкое в SQL. Понимание этой симметрии здорово экономит время: определились с форматом один раз — и переносите логику между SQL и Python почти механически. Потренировать melt и заодно pivot_table можно в Python-тренажёре, а синтаксис методов лежит в Python-справочнике.

Какие ошибки ломают разворот чаще всего?

Первая и самая частая — несовместимые типы. Все разворачиваемые колонки схлопываются в одну, а у колонки может быть только один тип. Если dau целочисленный, а revenue дробный, PostgreSQL честно ругнётся на несовпадение типов в VALUES. Лечится приведением к общему типу — ::numeric или ::text, — как в примере с метриками выше. В UNPIVOT у SQL Server требование то же самое.

Вторая — молчаливая потеря NULL-ов. Оператор UNPIVOT их отбрасывает, UNION ALL и LATERAL VALUES — сохраняют. Прежде чем разворачивать, решите, что для вас NULL: «нет данных» или «ноль». От этого зависит, оставлять строку или фильтровать её WHERE value IS NOT NULL уже сознательно, а не по случайности выбранного способа.

Третья — исчезнувший контекст. После разворота колонка month содержит строки 'jan', 'feb', а не настоящие даты, и сортировка по алфавиту поставит apr перед jan. Если нужен хронологический порядок, подмешивайте номер месяца прямо в VALUES или превращайте текст в дату. И четвёртое, по мелочи: не рассчитывайте на порядок строк без явного ORDER BY — база вправе вернуть их как ей удобно.

Где это встречается на собеседовании и как потренироваться?

Разворот столбцов в строки — стабильный сюжет на собеседованиях аналитиков в продуктовых командах. Формулировка обычно живая: «дали выгрузку, где месяцы по колонкам, — приведи к виду, удобному для дашборда» или «напиши то же самое без оператора UNPIVOT, на чистом PostgreSQL». Проверяют, знаете ли вы про LATERAL, помните ли поведение NULL-ов и не сложите ли всё в тяжёлый UNION ALL на пустом месте. Какие форматы встречаются в конкретных компаниях, я собрал в разборах на странице компаний, а типовые задачи с решениями — в подборке вопросов для собеса.

Лучший способ закрепить — прогнать оба варианта руками: широкую в длинную и обратно. В SQL-тренажёре можно взять таблицу с месяцами по колонкам и развернуть её сначала через UNION ALL, потом через LATERAL VALUES, и сравнить планы выполнения. Пять SQL-задач открыты бесплатно; полный набор из четырёхсот с лишним задач, PostgreSQL прямо в браузере и безлимитный AI-собес — уже в Pro. Одна отработанная тема на реальных данных стоит десятка перечитанных статей, эту в том числе.

Связанная тема — PIVOT в SQL: строки в столбцы (сводная таблица).