loading..
Русский    English
14:50
листать

Оператор 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 он выглядит инородным телом. Собственно, иначе и быть не может ввиду того, что поворот (транспонирование) таблицы является не реляционной операцией, а операцией работы с многомерными структурами данных.


Тэги:
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 Больше тэгов
Учебник обновлялся
несколько дней назад
https://exchangesumo.com/obmen/UNVBUAH-FRTBKZT/ . Соблюдение зазора между плитками при укладке
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.