0Pricing
Coding Interview Prep · Урок

Синтаксис PIVOT и перекрёстных таблиц в разных СУБД

PIVOT в SQL Server и crosstab в Postgres, а также их ограничения

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

За пределами условной агрегации

Вы уже знаете переносимый вариант сводной таблицы с помощью CASE. Но интервьюеры также хотят понять, умеете ли Вы использовать специфичные для СУБД операторы сводных таблиц, когда они доступны.

SQL Server предоставляет специальный оператор PIVOT. PostgreSQL предлагает функцию crosstab в расширении tablefunc. Знание обоих вариантов и их особенностей говорит о Вашем практическом опыте.

Устройство PIVOT в SQL Server

PIVOT в SQL Server принимает три элемента:

  • Агрегатную функцию над столбцом значений.
  • Предложение FOR, в котором указывается столбец, чьи значения станут новыми столбцами.
  • Список IN с буквальными значениями, которые нужно превратить в столбцы.

Оператор должен применяться к производной таблице, содержащей ровно ключ, столбец для разбиения и значение — и ничего больше.

SELECT region, [Q1], [Q2]
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (
  SUM(amount)
  FOR quarter IN ([Q1], [Q2])
) AS p;

Неявная группировка GROUP BY

Тонкий нюанс PIVOT, который интервьюеры проверяют: группировка выполняется неявно. SQL Server группирует по каждому столбцу источника, который не является агрегируемым столбцом или столбцом из предложения FOR.

Поэтому, если в производной таблице случайно окажется дополнительный столбец, например order_id, сводная таблица также будет группироваться по нему, и строк окажется гораздо больше ожидаемого. Всегда сокращайте внутренний запрос до ключа, столбца для разбиения и значения.

-- WRONG: order_id leaks in and breaks grouping
FROM (SELECT region, quarter, amount, order_id FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ([Q1],[Q2])) AS p;
-- The pivot now groups by region AND order_id

Имена столбцов в квадратных скобках

В SQL Server имена столбцов сводной таблицы — это буквальные значения из данных, заключённые в квадратные скобки. Если значение начинается с цифры или содержит пробелы, скобки обязательны.

Во внешнем SELECT эти столбцы выбираются под теми же именами в квадратных скобках. Поэтому PIVOT также не может работать с неизвестными значениями без динамического SQL: список IN задаётся в запросе жёстко.

SELECT region, [2023], [2024]
FROM (SELECT region, yr, amount FROM sales) AS s
PIVOT (SUM(amount) FOR yr IN ([2023], [2024])) AS p;

crosstab в PostgreSQL

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

Сначала необходимо включить расширение. Функция crosstab ожидает, что исходный запрос вернёт ровно три столбца в таком порядке: идентификатор строки, категорию и значение.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);

Список определений столбцов

Самая подверженная ошибкам часть crosstab — завершающий список определений столбцов AS ct(...). Вы должны самостоятельно объявить имена и типы столбцов вывода, и они должны соответствовать количеству и порядку категорий.

Если для строки отсутствует категория, crosstab заполняет значения по позициям. Это может привести к смещению данных, если не использовать приведённую ниже двухаргументную форму.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);
-- ct(...) MUST list every output column and its type

Двухаргументная форма crosstab

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

Именно эту надёжную форму интервьюеры ожидают увидеть при разреженных категориях.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric);

В MySQL нет ни того ни другого

Если интервьюер спрашивает о MySQL, ответьте прямо: в MySQL нет ни PIVOT, ни crosstab. Единственный вариант — условная агрегация с CASE (или сокращённая запись SUM(... ) + IF()).

Именно поэтому переносимый шаблон с CASE так ценен: это наименьший общий знаменатель, работающий везде.

-- MySQL: only conditional aggregation works
SELECT
  region,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2
FROM sales
GROUP BY region;

Разбор примера: подсчёт статусов в SQL Server

Требование к отчёту: «одна строка на регион со столбцом, подсчитывающим заказы каждого статуса». В SQL Server передайте в PIVOT сокращённую производную таблицу, используя COUNT.

Поскольку Вы подсчитываете сам столбец статуса, в каждой группе учитывается каждая строка с непустым статусом. Во внешнем SELECT каждый статус указывается как столбец в квадратных скобках. Это лаконичная альтернатива написанию трёх выражений COUNT(CASE ...).

SELECT region, [pending], [shipped], [delivered]
FROM (SELECT region, status FROM orders) AS src
PIVOT (
  COUNT(status)
  FOR status IN ([pending], [shipped], [delivered])
) AS p;

Общие ограничения

У PIVOT и crosstab есть то же основное ограничение, что и у условной агрегации: столбцы вывода должны быть известны в момент написания запроса.

  • SQL Server: список IN содержит буквальные значения.
  • crosstab в PostgreSQL: список определений столбцов содержит буквальные значения.

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

Что выбрать?

Хороший ответ на собеседовании честно сравнивает варианты:

  • Агрегация с CASE: переносимая, понятная, работает в любой СУБД. Вариант по умолчанию.
  • PIVOT в SQL Server: краткая запись для большого количества столбцов, но неявная группировка может удивить.
  • crosstab в PostgreSQL: мощный, но многословный вариант, которому нужны расширение и список определений столбцов.

Если сомневаетесь, выбирайте условную агрегацию, а операторы конкретных СУБД упоминайте как альтернативы.

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

Разберитесь в поведении PIVOT в SQL Server, о котором спрашивают интервьюеры.

Итоги

Синтаксис сводных таблиц для конкретных СУБД на одном экране:

  • SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b])) с неявным GROUP BY по оставшимся столбцам.
  • PostgreSQL: crosstab() из tablefunc, которому нужен список определений столбцов; для разреженных данных используйте двухаргументную форму.
  • MySQL: ни один из этих вариантов не существует, используйте CASE.
  • Во всех трёх случаях столбцы должны быть известны во время написания запроса.

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

Урок «Синтаксис PIVOT и перекрёстных таблиц в разных СУБД» бесплатный?

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

Чему я научусь в уроке «Синтаксис PIVOT и перекрёстных таблиц в разных СУБД»?

PIVOT в SQL Server и crosstab в Postgres, а также их ограничения Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

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

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

Сколько времени занимает урок «Синтаксис PIVOT и перекрёстных таблиц в разных СУБД»?

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

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

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

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

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