Новое в стандарте и реализациях языка SQL

Чтобы объяснить, что такое PIVOT, я бы начал с электронных таблиц EXCEL. В версии MS Excel 5.0 появились так называемые сводные таблицы. Сводные таблицы представляют собой двумерную визуализацию многомерных структур данных, применяемых в технологии OLAP для построения хранилищ данных. Правильней даже сказать двумерные сечения трехмерных OLAP-кубов, если иметь в виду наличие на сводной таблице элемента, который называется «страница». Сводные таблицы позволяют выполнять стандартные операции с многомерными структурами, например, упоминавшееся уже сечение куба, свертку и детализацию – операцию обратную свертке.

Следует сказать, что сводная таблица не является реляционной, поскольку имеет не только заголовки столбцов, но и заголовки строк, при этом и те и другие формируются из данных, находящихся в столбцах обычных реляционных таблиц. Последнее, кстати, означает, что число строк и столбцов заранее неизвестно, т.к. они формируются динамически при выполнении запроса к реляционным данным. Кроме того, заголовки могут иметь многоуровневые подзаголовки, что и позволяет выполнять операции свертки (переход на более высокий уровень иерархии) и детализации (переход на более низкий уровень иерархии).

Такие свойства сводных таблиц позволяют их использовать, наряду со сводными диаграммами, в качестве клиента для визуального отображения многомерных данных, находящихся в хранилищах, поддерживаемых различными СУБД (например, MS  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server Analysis Services).

Чтобы пояснить сказанное примером, давайте рассмотрим такой запрос к одной из учебных баз на sql-ex.ru:

Консоль

Выполнить

SELECT maker, type 
FROM product;

select maker, type from product;

    Схема

Нет схемы

результатом которого является такая таблица:

Пусть теперь нам требуется получить таблицу со следующими заголовками:

     Типы продукции

П
р
о
и
з
в
о
д
и
т
е
л
и

Заголовками строк здесь являются уникальные имена производителей, которые берутся из столбца maker вышеприведенного запроса, а заголовками столбцов – уникальные типы продукции (соответственно, из столбца type). А что должно быть в середине? Ответ очевиден – некоторый агрегат, например, функция count(type), которая подсчитает для каждого производителя отдельно число моделей ПК, ноутбуков и принтеров, которые и заполнят соответствующие ячейки этой таблицы.

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

Можно сказать, что pivot-таблица в SQL – это одноуровневая сводная таблица.

Оператор PIVOT не является стандартным (я не уверен, что он когда-нибудь будет стандартизован ввиду нереляционной природы pivot-таблицы), поэтому я буду использовать в примерах его реализацию в языке  T-SQL (Transact-SQL) — процедурное расширение языка SQL, используемое для программирования на стороне сервера в Microsoft SQL Server и Sybase ASE.T-SQL (SQL Server 2005/2008).

Я могу и ошибиться в хронологии, но мне представляется, что успех реализации сводной таблицы в Excel привел к появлению так называемых перекрестных запросов в Access, и, наконец, к оператору PIVOT в T-SQL.

Содержание:

Оператор PIVOT
Оператор UNPIVOT
CROSSTAB в PostgreSQL