Как создать crosstab запрос в PostgreSQL: секции и статусы

Быстрый ответ

Для использования функционала crosstab в PostgreSQL необходимо установить расширение tablefunc. Вот как выглядит стандартное использование функции crosstab:

SELECT * FROM crosstab(
  $$SELECT row_label, category, value FROM ваша_таблица ORDER BY 1,2$$
) AS результат(row_label тип_данных, category1 тип_данных, category2 тип_данных);

Важно следить за правильностью порядка сортировки в ORDER BY для соответствия ожиданиям функции crosstab. Подставьте row_label, category, value и тип_данных в соответствии со структурой вашей базы данных.

Обработка особенностей

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

SELECT * FROM crosstab(
  $$SELECT row_label, category, value FROM ваша_таблица$$,
  $$SELECT DISTINCT category FROM ваша_таблица ORDER BY 1$$
) AS результат(row_label тип_данных, category1 тип_данных, ...);

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

Углубляемся в возможности crosstab

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

SELECT * FROM crosstab(
  $$SELECT row_label, category, value::text FROM ваша_таблица ORDER BY 1,2$$
) AS финальный_результат(row_label тип_данных, category1 желаемый_тип_данных, ...);

Чтобы узнать, установлено ли расширение в PostgreSQL, выполните следующий запрос:

SELECT * FROM pg_proc WHERE proname = 'crosstab';

Когда crosstab не подходит

Если использовать tablefunc невозможно по тем или иным причинам, можно использовать функции JSON или агрегирование с конструкциями CASE:

SELECT 
  row_label,
  JSON_OBJECT_AGG(category, value) as категории
FROM ваша_таблица
GROUP BY row_label;

SELECT 
  row_label,
  SUM(CASE WHEN category = 'category1' THEN value ELSE 0 END) as category1,
  SUM(CASE WHEN category = 'category2' THEN value ELSE 0 END) as category2
FROM ваша_таблица
GROUP BY row_label;

Данные подходы позволяют эмулировать функции crosstab() для создания сводных таблиц.

Визуализация

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

| Раздел            | Блюдо              | День недели      |
| ------------------| ------------------- | ----------------- |
| Закуска (🧀)      | Сырная тарелка     | Вторник 🌒        |
| Основное (🥩)     | Стейк              | Среда 🌓          |
| Десерт (🍰)       | Чизкейк            | Четверг 🌔        |

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

SELECT * FROM CROSSTAB(
  'SELECT dish, day_of_week, rating FROM tastings ORDER BY 1,2',
  'SELECT DISTINCT day_of_week FROM tastings ORDER BY 1'
) AS ct(dish TEXT, tuesday_rating INT, wednesday_rating INT, thursday_rating INT);

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

| Блюдо (Раздел) | Вторник 🌒| Среда 🌓    | Четверг 🌔 |
| --------------- | --------- | ------------ | ----------- |
| Сырная тарелка | 5 🌟      | 4 🌟         | 5 🌟        |
| Стейк          | 4 🌟      | 5 🌟         | 4 🌟        |
| Чизкейк        | 3 🌟      | 3 🌟         | 5 🌟        |

Столбцы создаются динамически, чтобы отразить оценки каждого блюда.

\crosstabview

Используйте мета-команду \crosstabview в терминале psql для комфортной работы со сводными таблицами.

Подробнее об этом расскажет наш спикер на видео

Остерегаемся дубликатов

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

Обучаемся у лучших

Для того чтобы быть всегда в курсе последних подходов и наиболее эффективно использовать crosstab в PostgreSQL, ознакомьтесь с лучшими ответами на Stackoverflow.

Полезные материалы

1. PostgreSQL: Документация: 16: F.43. tablefunc — функции, возвращающие таблицы (crosstab и другие) — Официальная документация PostgreSQL.
2. Как портировать приложение с iPad на iPhone – Stack Overflow — Обсуждение на StackOverflow.
3. Flask: Скачивание файла CSV по нажатию на кнопку – Stack Overflow — Ответ, который может быть интересным при работе с crosstab.
4. Модуль 'tablefunc' PostgreSQL с примерами — Примеры использования на GitHub.