Как работает механизм транспонирования (PIVOT) в SQL?

PIVOT: Транспонирование Данных в SQL

PIVOT — это инструмент SQL для преобразования строк в столбцы (транспонирование). Вместо того чтобы видеть данные в формате "одна строка = одна категория", PIVOT позволяет развернуть категории в отдельные столбцы. Это полезно для отчётов, сравнений и аналитики.

Проблема, Которую Решает PIVOT

Исходные данные (узкий формат):

country | month  | revenue
USA     | Jan    | 1000
USA     | Feb    | 1200
Europe  | Jan    | 800
Europe  | Feb    | 950

После PIVOT (широкий формат):

country | Jan  | Feb
USA     | 1000 | 1200
Europe  | 800  | 950

Теперь легко сравнивать: USA растёт быстрее (20% vs 18.75%).

Синтаксис PIVOT

SELECT *
FROM (
  SELECT country, month, revenue
  FROM sales
) AS source_data
PIVOT (
  SUM(revenue)              -- Агрегирующая функция
  FOR month                 -- Колонка, значения которой становятся столбцами
  IN ('Jan', 'Feb', 'Mar')  -- Явное перечисление значений
)
AS pivot_table;

Простой Пример

SELECT *
FROM (
  SELECT 
    DATE_TRUNC('month', created_at)::DATE as month,
    category,
    SUM(amount) as total
  FROM sales
  GROUP BY month, category
) AS sales_data
PIVOT (
  SUM(total)
  FOR category IN ('Electronics', 'Clothing', 'Books')
)
AS pivoted_sales;

Результат:

month      | Electronics | Clothing | Books
2025-01-01 | 5000        | 3000     | 1500
2025-02-01 | 6000        | 3200     | 1800

Различные Агрегирующие Функции

-- Среднее
PIVOT (
  AVG(revenue)
  FOR month IN ('Jan', 'Feb')
)

-- Количество
PIVOT (
  COUNT(DISTINCT user_id)
  FOR month IN ('Jan', 'Feb')
)

-- Минимум/Максимум
PIVOT (
  MAX(price) - MIN(price) as price_range
  FOR category IN ('A', 'B', 'C')
)

Практические Примеры

1. Сравни Продажи по Регионам за Кварталы

SELECT *
FROM (
  SELECT
    region,
    CONCAT('Q', QUARTER(created_at), '_', YEAR(created_at)) as quarter,
    SUM(amount) as revenue
  FROM sales
  GROUP BY region, quarter
) AS regional_sales
PIVOT (
  SUM(revenue)
  FOR quarter IN ('Q1_2024', 'Q2_2024', 'Q3_2024', 'Q4_2024')
)
AS pivot_table;

Результат — удобное сравнение: видишь рост/падение по регионам.

2. Когортный Анализ (Retention Matrix)

SELECT *
FROM (
  SELECT
    DATE_TRUNC('month', u.created_at)::DATE as signup_month,
    EXTRACT(MONTH FROM e.created_at)::INT as activity_month,
    COUNT(DISTINCT u.user_id) as users
  FROM users u
  JOIN events e ON u.user_id = e.user_id
  WHERE e.created_at >= u.created_at
  GROUP BY signup_month, activity_month
) AS cohort_data
PIVOT (
  SUM(users)
  FOR activity_month IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
)
AS cohort_matrix;

Получишь матрицу, где строки = месяцы регистрации, столбцы = месяцы активности.

3. Данные о Совершенствованиях (A/B Тест)

SELECT *
FROM (
  SELECT
    DATE(test_date) as date,
    variant,
    SUM(CASE WHEN converted = TRUE THEN 1 ELSE 0 END)::FLOAT / COUNT(*) * 100 as conversion_rate
  FROM ab_test
  GROUP BY date, variant
) AS test_data
PIVOT (
  AVG(conversion_rate)
  FOR variant IN ('Control', 'Variant_A', 'Variant_B')
)
AS test_results;

Результат покажет конверсию для каждого варианта по дням.

4. Multi-Column PIVOT

Некоторые БД позволяют пивотировать по несколько колонок:

SELECT *
FROM (
  SELECT
    region,
    product_category,
    status,
    COUNT(*) as count
  FROM orders
  GROUP BY region, product_category, status
) AS sales_data
PIVOT (
  SUM(count)
  FOR status IN ('Completed', 'Pending', 'Cancelled')
)
AS pivot_table;

Альтернатива: CASE WHEN (Кросс-табуляция)

Не все БД поддерживают PIVOT. Альтернатива — CASE WHEN:

SELECT
  country,
  SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) as January,
  SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) as February,
  SUM(CASE WHEN month = 'Mar' THEN revenue ELSE 0 END) as March
FROM sales
GROUP BY country
ORDER BY country;

Это работает везде и часто быстрее PIVOT.

PIVOT vs CASE WHEN

Динамический PIVOT

Если количество категорий заранее неизвестно, используй динамический SQL (если поддерживается):

-- PostgreSQL пример с динамическим списком
SELECT
  country,
  STRING_AGG(DISTINCT month ORDER BY month) as months
FROM sales
GROUP BY country;

-- Затем составь CASE WHEN динамически в приложении

Или используй JSON:

SELECT
  country,
  JSON_OBJECT_AGG(month, revenue) as monthly_revenue
FROM sales
GROUP BY country;

UNPIVOT (Обратная Операция)

Преобразование широкого формата обратно в узкий:

-- Исходные данные (широкий формат)
SELECT * FROM sales_wide;
-- Результат: country, Jan, Feb, Mar

-- Обратно в узкий формат
SELECT
  country,
  'Jan' as month,
  Jan as revenue
FROM sales_wide
UNION ALL
SELECT
  country,
  'Feb' as month,
  Feb as revenue
FROM sales_wide
UNION ALL
SELECT
  country,
  'Mar' as month,
  Mar as revenue
FROM sales_wide;

Или используй UNPIVOT (SQL Server):

SELECT
  country,
  month,
  revenue
FROM sales_wide
UNPIVOT (
  revenue
  FOR month IN (Jan, Feb, Mar)
) AS unpivot_table;

Performance Tips

1. Ограничь данные в подзапросе:

   -- Плохо
   SELECT * FROM (
     SELECT * FROM huge_table  -- Миллионы строк
   ) PIVOT (...)
   
   -- Хорошо
   SELECT * FROM (
     SELECT country, month, revenue
     FROM sales
     WHERE created_at >= CURRENT_DATE - INTERVAL 12 month
   ) PIVOT (...)
   

2. Используй индексы на PIVOT колонках

3. Избегай PIVOT со множеством значений

   • PIVOT для 12 месяцев: OK
   • PIVOT для 1000 уникальных значений: BAD

Ключевые Выводы

• PIVOT: Преобразует строки в столбцы
• Идеален для: Отчётов, сравнений, когортного анализа
• Альтернатива: CASE WHEN (более портативно)
• Осторожность: Не используй с очень большим количеством значений
• UNPIVOT: Обратная операция

PIVOT делает отчёты и аналитику более читаемыми. Используй его для кросс-табуляции, но помни о производительности с большими наборами данных.