loading..
Русский    English
16:55
листать

Операторы PIVOT и UNPIVOT

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

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

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

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

Консоль
Выполнить
  1. SELECT maker, type
  2. FROM product;

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

maker type
B PC
A PC
A PC
E PC
A Printer
D Printer
A Laptop
C Laptop
A Printer
A Printer
D Printer
E Printer
B Laptop
A Laptop
E PC
E PC

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

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

П
р
о
и
з
в
о
д
и
т
е
л
и
     Laptop   PC   Printer
A
B
C
D
E

Заголовками строк здесь являются уникальные имена производителей, которые берутся из столбца 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.

Особенности: ORACLE

В Oracle операции PIVOT и DRILLDOWN (UNPIVOT) были отданы на откуп средствам визуализации OLAP, т.е. MS Excel, BusinessObjects и пр.

В Oracle 11g появились pivot/unpivot, но в pivot есть "тонкость" - нужно ЯВНО указывать столбцы, если результат нужен в ТАБЛИЧНОМ виде. Получение данных для столбцов, не указанных явно, также возможно, но только в виде XML.

Автор: Кузнецов С.Н.


Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
месяц назад
мани . Создание перекрытий между этажами с помощью бетонных плит
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.