0Pricing
SQL Interview Prep · Урок

Преобразование столбцов в строки

Обратное преобразование широких таблиц с помощью UNPIVOT или UNION ALL

«Преобразование столбцов в строки» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Обратная задача

Обратное преобразование сводной таблицы — это зеркальное отражение построения сводной таблицы: Вы берёте широкую таблицу и превращаете её столбцы обратно в строки. Интервьюеры задают этот вопрос, когда данные поступают в форме электронной таблицы, но для анализа их нужно нормализовать.

Пример: таблица со столбцами q1, q2, q3, q4 для каждого региона должна превратиться в строки вида (region, quarter, amount). Именно такой длинный формат лучше всего подходит для агрегации, объединения и построения диаграмм.

-- Wide input we want to unpivot
region | q1  | q2  | q3  | q4
-------+-----+-----+-----+----
East   | 100 | 150 | 120 | 180
West   | 200 | 250 | 210 | 260

Переносимый шаблон UNION ALL

Ответ, не зависящий от диалекта, — UNION ALL: напишите по одному SELECT для каждого исходного столбца, и каждый из них должен выдавать буквальную метку и значение этого столбца.

Используйте UNION ALL, а не UNION, чтобы не тратить ресурсы на удаление дубликатов и сохранить каждую строку, даже если две ячейки содержат одинаковое значение.

SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
UNION ALL
SELECT region, 'Q2', q2 FROM wide_sales
UNION ALL
SELECT region, 'Q3', q3 FROM wide_sales
UNION ALL
SELECT region, 'Q4', q4 FROM wide_sales;

Почему UNION ALL, а не UNION

Это классическая ловушка на собеседовании. UNION удаляет дублирующиеся строки во всём результате. Если для East и West в Q1 было значение 100, обычный UNION объединит одинаковые строки, и Вы потеряете данные.

UNION ALL объединяет результаты без удаления дубликатов, что и требуется при обратном преобразовании сводной таблицы. Кроме того, он работает быстрее, поскольку для удаления дубликатов не нужны сортировка или хеширование.

-- UNION would wrongly merge identical (region, quarter, amount) rows
-- UNION ALL keeps every row, always the correct choice here

Согласование типов столбцов

Каждая ветвь UNION ALL должна возвращать одинаковое количество столбцов с совместимыми типами в одном и том же порядке. Имена столбцов берутся из первого SELECT.

Если широкие столбцы имеют разные типы (например, один имеет тип int, а другой — decimal), СУБД выбирает общий тип. Если типы действительно несовместимы, выполните явное приведение, чтобы объединение не завершилось ошибкой.

SELECT region, 'revenue' AS metric, CAST(revenue AS decimal(12,2)) AS val FROM t
UNION ALL
SELECT region, 'units',   CAST(units   AS decimal(12,2))        FROM t;

UNPIVOT в SQL Server

В SQL Server есть специальный оператор UNPIVOT, который короче, чем UNION ALL. Вы указываете имя нового столбца значений, имя нового столбца меток и перечисляете исходные столбцы, которые нужно объединить.

Важно помнить: UNPIVOT удаляет строки, в которых значение равно NULL. На собеседовании проверяют, знаете ли Вы об этом побочном эффекте.

SELECT region, quarter, amount
FROM wide_sales
UNPIVOT (
  amount FOR quarter IN (q1, q2, q3, q4)
) AS u;

UNPIVOT удаляет NULL

Если в столбце q3 у региона находится NULL, UNPIVOT в SQL Server просто исключает эту строку из вывода. Если Вам нужна строка для каждого столбца независимо от наличия NULL, используйте UNION ALL, который сохраняет такие строки.

На собеседовании сформулируйте этот компромисс: встроенный UNPIVOT краток, но теряет строки с NULL; UNION ALL многословен, зато сохраняет все данные.

-- UNPIVOT: q3 NULL for East -> no (East, Q3) row produced
-- UNION ALL: (East, 'Q3', NULL) row IS produced

PostgreSQL: LATERAL VALUES

В PostgreSQL нет UNPIVOT, но есть удобный идиоматичный вариант: CROSS JOIN LATERAL со списком VALUES. Каждая широкая строка расширяется за счёт небольшой встроенной таблицы пар (метка, значение).

Этот вариант чище длинного UNION ALL и читает исходную таблицу только один раз.

SELECT w.region, v.quarter, v.amount
FROM wide_sales w
CROSS JOIN LATERAL (VALUES
  ('Q1', w.q1),
  ('Q2', w.q2),
  ('Q3', w.q3),
  ('Q4', w.q4)
) AS v(quarter, amount);

Чтение таблицы один раз

Стоит упомянуть важный момент производительности: наивный UNION ALL просматривает широкую таблицу один раз для каждой ветви (для четырёх кварталов — четыре раза). Вариант с LATERAL VALUES и UNPIVOT в SQL Server читают источник один раз.

На больших таблицах это важно. Если приходится использовать UNION ALL, оптимизатор всё равно может выполнять повторное сканирование, поэтому упомяните LATERAL или UNPIVOT как более эффективный вариант.

Фильтрация пустых ячеек

С помощью UNION ALL или LATERAL Вы сохраняете строки со значениями NULL. Если нужны только заполненные ячейки, добавьте фильтр. Это имитирует поведение UNPIVOT в SQL Server.

Решение о сохранении или удалении NULL зависит от конкретной задачи, поэтому перед написанием запроса уточните требование у интервьюера.

SELECT region, quarter, amount
FROM (
  SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
  UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
) t
WHERE amount IS NOT NULL;

Практический пример: агрегация после преобразования в длинный формат

Частый следующий вопрос: «из широкой таблицы по кварталам получить общую выручку по каждому региону за все кварталы». После преобразования в длинный формат агрегация становится тривиальной: достаточно одной SUM с группировкой по региону.

Это показывает настоящую причину сначала выполнять такое преобразование. Суммирование четырёх отдельных столбцов хрупко, а SUM(amount) GROUP BY region в длинном формате масштабируется на любое количество кварталов.

WITH long_sales AS (
  SELECT region, 'Q1' AS quarter, q1 AS amount FROM wide_sales
  UNION ALL SELECT region, 'Q2', q2 FROM wide_sales
  UNION ALL SELECT region, 'Q3', q3 FROM wide_sales
  UNION ALL SELECT region, 'Q4', q4 FROM wide_sales
)
SELECT region, SUM(amount) AS total
FROM long_sales
GROUP BY region;

Когда преобразовывать данные в длинный формат

Распознайте признак необходимости преобразования в длинный формат в условии задачи:

  • В исходных данных есть повторяющиеся столбцы, которые на самом деле содержат значения (месяцы, годы, показатели).
  • Вам нужно агрегировать, объединять или отображать на графике данные по этим значениям.
  • Вы хотите нормализовать денормализованные данные электронной таблицы при импорте.

Длинный формат почти всегда лучше подходит для дальнейшей работы с SQL, поэтому преобразование в него часто выполняется первым.

Быстрая проверка

Убедитесь, что знаете наиболее распространённую ошибку при преобразовании в длинный формат.

Итоги

Преобразование в длинный формат превращает столбцы в строки:

  • Переносимый: по одному SELECT для каждого столбца, объединённые с помощью UNION ALL (никогда не обычного UNION).
  • SQL Server: встроенный UNPIVOT, компактный, но отбрасывает значения NULL.
  • PostgreSQL: CROSS JOIN LATERAL (VALUES ...), один проход по данным.
  • Согласуйте количество и типы столбцов во всех ветвях; отфильтруйте значения NULL, если этого требует условие задачи.

Часто задаваемые вопросы

Урок «Преобразование столбцов в строки» бесплатный?

Да — полный текст урока «Преобразование столбцов в строки» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Преобразование столбцов в строки»?

Обратное преобразование широких таблиц с помощью UNPIVOT или UNION ALL Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «Преобразование столбцов в строки»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Сводные столбцы с условной агрегацией
  2. Синтаксис PIVOT и перекрёстных таблиц в разных СУБД
  3. Преобразование столбцов в строки
  4. Динамические сводные таблицы с неизвестными столбцами
← Назад к SQL Interview Prep