Функция crosstab в PostgreSQL

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

Но для чтения такой формат часто неудобен. Например, продажи могут храниться так:

product	quarter	amount
Ноутбук	Q1	120000
Ноутбук	Q2	145000
Ноутбук	Q3	98000
Монитор	Q1	70000

Для базы это нормальная структура. Для отчета человек обычно хочет увидеть кварталы отдельными колонками:

product	q1	q2	q3	q4
Ноутбук	120000	145000	98000	NULL
Монитор	70000	NULL	NULL	NULL

Получается конфликт форматов: нормализация удобна для записи и хранения, а «широкая» матрица - для анализа и презентации. Инженерный инструмент вроде crosstab нужен как раз для преобразования одного формата в другой на стороне базы.

В других СУБД похожая задача решается оператором PIVOT. В PostgreSQL такой оператор реализован через функцию crosstab из расширения tablefunc.

Подключаем tablefunc

crosstab не входит в ядро PostgreSQL по умолчанию. Сначала нужно подключить стандартное расширение из набора contrib-модулей:

CREATE EXTENSION IF NOT EXISTS tablefunc;

После этого функция становится доступна в базе.

Документация PostgreSQL: модуль tablefunc.

Три поля, из которых строится матрица

Классический вариант crosstab принимает запрос, который возвращает ровно три поля:

1. RowID - идентификатор строки будущего отчета.
2. Category - категория, которая станет колонкой.
3. Value - значение на пересечении строки и категории.

В примере с продажами это выглядит так:

RowID    Category    Value
Ноутбук  Q1          120000
Ноутбук  Q2          145000
Ноутбук  Q3          98000
Монитор  Q1          70000

crosstab группирует строки по RowID, значения Category превращает в колонки, а Value размещает на пересечениях.

Это «золотое правило» функции: входной запрос должен быть подготовлен именно в формате RowID - Category - Value. Если нужны дополнительные признаки, их нельзя просто добавить четвертым полем и ожидать, что crosstab сам поймет структуру отчета.

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

Полный пример с продажами по кварталам

Предположим, есть таблица sales со следующими полями:

• product - товар;
• quarter - квартал;
• amount - сумма продаж.

Запрос с crosstab будет выглядеть так:

SELECT *
FROM crosstab(
    $$
        SELECT
            product,
            quarter,
            amount
        FROM sales
        ORDER BY product, quarter
    $$,
    $$
        VALUES
            ('Q1'),
            ('Q2'),
            ('Q3'),
            ('Q4')
    $$
) AS pivot_report(
    product text,
    q1 numeric,
    q2 numeric,
    q3 numeric,
    q4 numeric
);

Здесь два SQL-запроса.

Первый возвращает исходные данные:

SELECT
    product,
    quarter,
    amount
FROM sales
ORDER BY product, quarter

Его колонки соответствуют схеме:

• product - RowID;
• quarter - Category;
• amount - Value.

Второй запрос перечисляет все возможные категории:

VALUES
    ('Q1'),
    ('Q2'),
    ('Q3'),
    ('Q4')

Порядок категорий здесь определяет порядок результирующих колонок. Первая категория попадет в q1, вторая - в q2 и так далее.

Последний блок описывает результат:

AS pivot_report(
    product text,
    q1 numeric,
    q2 numeric,
    q3 numeric,
    q4 numeric
)

Имена и типы колонок здесь обязательны. crosstab возвращает результат типа setof record, поэтому PostgreSQL должен заранее знать структуру выходной таблицы. Это плата за статически описанный и типизированный результат.

Типы в AS должны соответствовать типам, которые возвращает внутренний запрос. Если amount имеет тип integer, а в результате указать numeric, это нужно делать осознанно и проверять совместимость типов.

Почему ORDER BY обязателен

Внутренний запрос должен сортировать данные как минимум по идентификатору строки и категории:

ORDER BY product, quarter

Это не декоративная часть запроса. crosstab обрабатывает значения последовательно и рассчитывает, что данные одного RowID идут рядом, а категории расположены в предсказуемом порядке.

Без явного ORDER BY порядок строк не гарантирован. В результате значения могут попасть не в те столбцы, на которые рассчитывает отчет. Запрос иногда будет выглядеть рабочим на небольшом объеме данных, а затем начнет давать некорректную раскладку после изменения плана выполнения.

Поэтому сортировку лучше считать обязательной частью шаблона:

ORDER BY row_id, category

Почему лучше использовать два аргумента

У crosstab есть вариант с одним аргументом:

crosstab(source_sql)

Он берет категории из входного набора и последовательно раскладывает значения по выходным колонкам. У такого подхода есть проблема с пропущенными категориями.

Предположим, в данных за 2025 год нет продаж за Q2:

product	quarter	amount
Ноутбук	Q1	120000
Ноутбук	Q3	98000
Ноутбук	Q4	110000

Для отчета Q3 все равно должен оставаться третьим столбцом. Но однопараметрический вариант может разложить значения подряд: первое значение в Q1, второе - в Q2, третье - в Q3. Тогда продажи за Q3 окажутся на месте Q2.

Безопаснее явно передать список категорий вторым аргументом:

crosstab(
    source_sql,
    category_sql
)

В нашем примере это:

VALUES
    ('Q1'),
    ('Q2'),
    ('Q3'),
    ('Q4')

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

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

crosstab и CASE WHEN или FILTER

До появления crosstab такую матрицу часто строили вручную:

SELECT
    product,
    SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
    SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2,
    SUM(amount) FILTER (WHERE quarter = 'Q3') AS q3,
    SUM(amount) FILTER (WHERE quarter = 'Q4') AS q4
FROM sales
GROUP BY product;

Или через условную агрегацию с CASE WHEN.

Такой вариант остается вполне уместным:

• когда колонок всего две-три;
• когда нужна дополнительная логика агрегации;
• когда не хочется подключать расширение;
• когда запрос должен быть максимально очевиден для команды.

crosstab удобнее, когда категорий много. Для 12 месяцев ручной список из CASE или FILTER быстро становится громоздким. Функция дает более компактную запись и выполняет трансформацию внутри PostgreSQL. Поскольку tablefunc реализован на C, crosstab может быть более подходящим вариантом для больших объемов данных, чем ручная группировка.

При этом это не универсальная замена CASE и FILTER. Для небольшой сводки обычная агрегация часто читается лучше.

Когда отдавать матрицу из базы

crosstab особенно полезен, если результат запроса сразу потребляется отчетом или фронтендом. Клиент получает готовую структуру:

product | q1 | q2 | q3 | q4

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

Это снижает объем логики на стороне приложения и позволяет централизовать преобразование в SQL. В BI-системе или в клиентском коде Pivot тоже можно построить, но тогда одна и та же логика может дублироваться в нескольких местах.

Граница применимости - динамические колонки. В блоке AS список полей задается заранее:

AS pivot_report(
    product text,
    q1 numeric,
    q2 numeric,
    q3 numeric,
    q4 numeric
)

Сделать одним простым запросом результат вида «все годы, которые сейчас есть в базе» нельзя. Для динамического количества колонок потребуется генерация SQL на бэкенде или PL/pgSQL. Это уже отдельная задача, и crosstab не избавляет от необходимости заранее сформировать структуру результата.

Чек-лист для crosstab

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

• расширение подключено:

  CREATE EXTENSION IF NOT EXISTS tablefunc;
  

• исходный запрос возвращает ровно три поля: RowID, Category, Value;

• данные отсортированы через ORDER BY RowID, Category;

• список категорий передан вторым аргументом;

• порядок категорий соответствует порядку колонок в результате;

• в AS указаны все выходные колонки;

• типы в AS соответствуют типам результата внутреннего запроса;

• список колонок фиксирован и заранее известен.

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