0Pricing
SQL Academy · Урок

Шаблоны перекрёстных таблиц (PostgreSQL crosstab())

Создавайте настоящие сводные таблицы с помощью функции crosstab() расширения tablefunc

«Шаблоны перекрёстных таблиц (PostgreSQL crosstab())» — бесплатный урок SQL Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Зачем нужна настоящая перекрёстная таблица?

Для сводных таблиц на основе CASE требуется перечислить каждый целевой столбец. Для действительно широких сводных таблиц, например с одним столбцом на каждый товар, используется функция crosstab() расширения для перекрёстных таблиц.

Подключение расширения

Дополнительные модули PostgreSQL включают поддержку перекрёстных таблиц:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Базовый синтаксис перекрёстной таблицы

Функция перекрёстной таблицы принимает строку SQL с тремя столбцами (ключ строки, категория, значение) и возвращает ключ строки плюс один столбец на каждую категорию:

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders
    GROUP BY user_id, status
    ORDER BY user_id, status
  $$
) AS ct (
  user_id BIGINT,
  paid    INT,
  pending INT,
  cancelled INT
);

Зачем объявлять выходные столбцы

SQL имеет статическую типизацию — планировщику нужны выходные столбцы во время разбора запроса. Поэтому схему указывают в предложении AS, включая типы данных.

Перекрёстная таблица с двумя аргументами (с набором категорий)

Для разреженных данных передайте список категорий отдельно, чтобы отсутствующие значения становились NULL, а не приводили к смещению столбцов:

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders GROUP BY user_id, status
    ORDER BY user_id
  $$,
  $$ VALUES ('paid'), ('pending'), ('cancelled') $$
) AS ct (
  user_id BIGINT, paid INT, pending INT, cancelled INT
);

Когда CASE лучше перекрёстной таблицы

Для небольшого известного набора категорий CASE и FILTER проще: не нужно расширение и не возникает ловушек варианта с двумя аргументами. Используйте перекрёстную таблицу, когда:

  • У вас много категорий
  • Категории загружаются динамически
  • Вы формируете данные для внешнего потребителя сводных таблиц

Динамические сводные таблицы

Если набор категорий неизвестен во время выполнения, сформируйте SQL в приложении или используйте PL/pgSQL с функцией форматирования и EXECUTE.

-- Build the SQL dynamically:
SELECT string_agg(format('SUM(CASE WHEN status = %L THEN 1 END) AS %I',
                          status, status), ', ')
FROM (SELECT DISTINCT status FROM orders) s;

Широкий формат для электронных таблиц

В отчётах для аналитиков часто нужен широкий формат. Сформируйте его в SQL или передайте данные в длинном формате, а сведение поручите инструменту BI.

Обратное преобразование: из широкого формата в длинный

Чтобы преобразовать широкий формат в длинный, используйте UNION ALL или jsonb_each_text() в PostgreSQL:

SELECT id, key AS month, (value)::NUMERIC AS revenue
FROM monthly_wide,
     jsonb_each_text(to_jsonb(monthly_wide) - 'id');

Производительность

Функция crosstab() один раз выполняет внутренний SQL-запрос и строит сводную таблицу в памяти. Узкое место такое же, как у обычного запроса с GROUP BY.

Ограничения перекрёстных таблиц

В PostgreSQL нет встроенного ключевого слова PIVOT, в отличие от Oracle и SQL Server. Функция crosstab() служит обходным решением.

Итоги

Для большинства сводных таблиц лучше всего подходят CASE и FILTER. Используйте crosstab(), когда категорий много или их набор заранее неизвестен.

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

Какое расширение предоставляет функцию crosstab() в PostgreSQL?

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

Урок «Шаблоны перекрёстных таблиц (PostgreSQL crosstab())» бесплатный?

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

Чему я научусь в уроке «Шаблоны перекрёстных таблиц (PostgreSQL crosstab())»?

Создавайте настоящие сводные таблицы с помощью функции crosstab() расширения tablefunc Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

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

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

Сколько времени занимает урок «Шаблоны перекрёстных таблиц (PostgreSQL crosstab())»?

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

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

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

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

  1. UNION, INTERSECT, EXCEPT
  2. UNION ALL и UNION (стоимость удаления дубликатов)
  3. Выражения CASE и сводные запросы
  4. Шаблоны перекрёстных таблиц (PostgreSQL crosstab())
← Назад к SQL Academy