PIVOT в SQL: как превратить строки в столбцы

Допустим, в базе хранятся продажи товаров по кварталам.
Стандартный GROUP BY вернёт такой результат:

А для отчёта нужно вот так, кварталы столбцами:

Такое преобразование строк в столбцы называется PIVOT. В статье разберём, как его сделать средствами SQL на примере базы данных сервиса доставки.

Проблема: «длинный» формат

В этой базе есть таблица events с действиями пользователей на разных платформах. Посчитаем количество событий по типу и платформе:

MySQL 8.1
SELECT
    event_name,
    platform,
    COUNT(*) AS cnt
FROM events
GROUP BY event_name, platform
ORDER BY event_name, platform;

Это так называемый «длинный» формат. Каждая комбинация «событие + платформа» занимает отдельную строку. Чтобы сравнить iOS с Android для одного события, приходится искать нужные строки глазами.

А хочется видеть данные так:

Платформы стали столбцами, а каждый тип события занимает одну строку. Именно такой результат мы хотим получить.

Как построить PIVOT: алгоритм

Прежде чем писать запрос, стоит ответить на четыре вопроса:

1. Что станет строками? event_name (тип события)
2. Что станет столбцами? platform (iOS, Android, Web)
3. Что будет значением в ячейках? COUNT(*) (количество событий)

CASE + GROUP BY: универсальный способ

Подход работает в любой СУБД: MySQL, PostgreSQL, SQLite, SQL Server.

MySQL 8.1
SELECT
    event_name,
    SUM(CASE WHEN platform = 'iOS' THEN 1 ELSE 0 END) AS iOS,
    SUM(CASE WHEN platform = 'Android' THEN 1 ELSE 0 END) AS Android,
    SUM(CASE WHEN platform = 'Web' THEN 1 ELSE 0 END) AS Web
FROM events
GROUP BY event_name
ORDER BY event_name;

Но как это работает? Разберём пошагово.

Что происходит внутри

Чтобы понять механику, уберём SUM и GROUP BY из запроса и посмотрим, что CASE вычисляет для каждой отдельной строки:

MySQL 8.1
SELECT
    event_name,
    platform,
    CASE WHEN platform = 'iOS' THEN 1 ELSE 0 END AS "iOS",
    CASE WHEN platform = 'Android' THEN 1 ELSE 0 END AS "Android",
    CASE WHEN platform = 'Web' THEN 1 ELSE 0 END AS "Web"
FROM events
WHERE event_name IN ('app_open', 'purchase')
LIMIT 6;

Каждый CASE проверяет платформу и ставит 1 при совпадении, 0 в остальных случаях. Вот фрагмент результата для наглядности:

Здесь показана часть строк для двух групп: app_open и purchase. Каждая строка получает 1 ровно в одном столбце, а в остальных 0. Например, строка с platform = 'iOS' получает 1 только в iOS.

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

Обратите внимание на ELSE 0 в CASE: без него несовпавшие строки получат NULL вместо 0, и результат SUM тоже может оказаться NULL. Подробнее об этом в разделе «Ловушка NULL» ниже.

Почему SUM, а не COUNT? SUM удобнее: достаточно заменить 1 на amount, чтобы считать суммы вместо количеств. Именно это мы сделаем в следующем примере.

MySQL 8.1
SELECT
    event_name,
    COUNT(*) FILTER (WHERE platform = 'iOS') AS iOS,
    COUNT(*) FILTER (WHERE platform = 'Android') AS Android,
    COUNT(*) FILTER (WHERE platform = 'Web') AS Web
FROM events
GROUP BY event_name
ORDER BY event_name;

От количеств к суммам

До сих пор мы считали количества событий. А что если нужны суммы денег? Достаточно заменить 1 на реальный столбец, например total_amount. Возьмём другую таблицу из той же базы, orders, и построим отчёт с выручкой по статусам заказов за каждый месяц:

MySQL 8.1
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(CASE WHEN status = 'delivered' THEN total_amount ELSE 0 END) AS delivered,
    SUM(CASE WHEN status = 'canceled' THEN total_amount ELSE 0 END) AS canceled,
    SUM(CASE WHEN status = 'refunded' THEN total_amount ELSE 0 END) AS refunded
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-07-01'
GROUP BY month
ORDER BY month;

MySQL 8.1
SELECT
    to_char(order_date, 'YYYY-MM') AS month,
    SUM(total_amount) FILTER (WHERE status = 'delivered') AS delivered,
    SUM(total_amount) FILTER (WHERE status = 'canceled') AS canceled,
    SUM(total_amount) FILTER (WHERE status = 'refunded') AS refunded
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-07-01'
GROUP BY month
ORDER BY month;

На что обратить внимание

Ловушка NULL

В примерах выше мы использовали ELSE 0 в CASE, чтобы гарантировать числовой результат. Но если написать CASE без ELSE:

MySQL 8.1
SUM(CASE WHEN status = 'refunded' THEN total_amount END)

...и в каком-то месяце не окажется ни одного возврата, CASE вернёт NULL для всех строк, а SUM из одних NULL даст NULL. Это критично, если дальше идут вычисления: 100 - NULL = NULL.

Избежать этого можно двумя способами:

1. Добавить ELSE 0 прямо в CASE (как мы делали в примерах выше):

   MySQL 8.1
   SUM(CASE WHEN status = 'refunded' THEN total_amount ELSE 0 END)
   

2. Обернуть в COALESCE. Полезно, когда в столбце уже могут встречаться NULL:

   MySQL 8.1
   COALESCE(SUM(CASE WHEN status = 'refunded' THEN total_amount END), 0)
   

Значения столбцов нужно знать заранее

Все подходы требуют явно указать значения, которые станут столбцами ('iOS', 'Android', 'Web'). Если в данных появится новая платформа, запрос нужно обновлять вручную.

Единственный способ обойти это: написать скрипт на стороне приложения (Python, PHP, JS) или использовать динамический SQL (сборку строки запроса через PREPARE / хранимые процедуры). Чистым статичным SQL эта задача не решается.

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

MySQL 8.1
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    $$
    SELECT event_name, platform, COUNT(*)
    FROM events
    GROUP BY event_name, platform
    ORDER BY event_name, platform
    $$,
    $$ VALUES ('Android'), ('iOS'), ('Web') $$
) AS ct(event_name TEXT, "Android" BIGINT, "iOS" BIGINT, "Web" BIGINT);

Подводя итоги