loading..
Русский    English
18:41
листать

Оператор PIVOT

Давайте рассмотрим такую задачу.

Пример 1.

Для каждого производителя из таблицы Product определить число моделей каждого типа продукции.

Задачу можно решить стандартными средствами с использованием оператора CASE:

Консоль
Выполнить
  1. SELECT maker,
  2. SUM(CASE type WHEN 'pc' THEN 1 ELSE 0 END) PC
  3. , SUM(CASE type WHEN 'laptop' THEN 1 ELSE 0 END) Laptop
  4. , SUM(CASE type WHEN 'printer' THEN 1 ELSE 0 END) Printer
  5. FROM Product
  6. GROUP BY maker


Теперь решение через PIVOT:

Консоль
Выполнить
  1. SELECT maker, -- столбец (столбцы), значения из которого формируют заголовки строк
  2. [pc], [laptop], [printer] -- значения из столбца, который указан в предложении type,
  3. -- формирующие заголовки столбцов
  4. FROM Product -- здесь может быть подзапрос
  5. PIVOT -- формирование пивот-таблицы
  6. (COUNT(model) -- агрегатная функция, формирующая содержимое сводной таблицы
  7. FOR type -- указывается столбец,
  8. -- уникальные значения в котором будут являться заголовками столбцов
  9. IN([pc], [laptop], [printer]) --указываются конкретные значения в столбце type,
  10. -- которые следует использовать в качестве заголовков,
  11. -- т.к. нам могут потребоваться не все
  12. ) pvt -- алиас для сводной таблицы


Надеюсь, что комментарии к коду достаточно понятны для того, чтобы написать оператор PIVOT без шпаргалки. Давайте попробуем.

Пример 2.

Посчитать среднюю цену на ноутбуки в зависимости от размера экрана.

Задача элементарная и решается с помощью группировки:

Консоль
Выполнить
  1. SELECT screen, AVG(price) avg_
  2. FROM Laptop
  3. GROUP BY screen

screen avg_
11 700.00
12 960.00
14 1175.00
15 1050.00

А вот как можно повернуть эту таблицу с помощью PIVOT:

Консоль
Выполнить
  1. SELECT [avg_],
  2. [11],[12],[14],[15]
  3. FROM (SELECT 'average price' AS 'avg_', screen, price FROM Laptop) x
  4. PIVOT
  5. (AVG(price)
  6. FOR screen
  7. IN([11],[12],[14],[15])
  8. ) pvt

avg_ 11 12 14 15
average price 700.00 960.00 1175.00 1050.00

В отличие от сводных таблиц, в операторе PIVOT требуется явно перечислить столбцы для вывода. Это серьезное ограничение, т.к. для этого нужно знать характер данных, а значит и применять в приложениях этот оператор мы сможем, как правило, только к справочникам (вернее, к данным, которые берутся из справочников).

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

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


Bookmark and Share
Тэги:
ALL AND AVG battles CASE CAST CHAR CHARINDEX classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MySQL NOT NOT IN NULL OR Oracle ORDER BY Больше тэгов
Учебник обновлялся
несколько дней назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100