вторник, 23 апреля 2013 г.

TSQL – PIVOT and UNPUVOT



TSQLPIVOT and UNPUVOT


   По поводу оператора PIVOT в MSDN сказано:

«Оператор PIVOT разворачивает возвращающее табличное значение выражение, преобразуя уникальные значения одного столбца выражения в несколько выходных столбцов, а также, в случае необходимости, объединяет оставшиеся повторяющиеся значения столбца и отображает их в выходных данных».

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

Конечно, можно развернуть таблицу:

CREATE TABLE Employees(
      Name nvarchar(50),
      Age int,
      Salary int);
GO
INSERT INTO Employees VALUES
      ('Tom','40','50000'),
      ('Sam','30','40000'),
      ('Bob','25','35000');
GO
SELECT * FROM Employees;


   С помощью  небольшого шаманства (не ломайте сейчас голову над представленным кодом, он вам станет понятен к концу статьи):

SELECT * FROM (
      SELECT *
      FROM (SELECT * FROM Employees) SourceTable
      UNPIVOT(a FOR Properties  in (Age,Salary))as UnpivotResultTable) UnpivotTable
PIVOT (
    MAX(a) FOR Name in ([Tom],[Sam],[Bob])) PivotTable;

 
   Но такие задачи перед вами встанут лишь на каком-нибудь конкурсе или при решении задач на логику. Вся сила PIVOT предназначена для решения совершенно других задач, которые встречаются при разработке коммерческих систем. И полностью показать это, на примере из небольшой таблицы, так чтоб прочувствовать важность PIVOT, практически невозможно.
   Для демонстрации, будем использовать справочник по мониторам, в который входят 515 записей. Чтобы было более интересно, скажу, что это абсолютно реальные данные, одного из популярных кибер гипермаркетов, актуальные на дату написания этой статьи. Данные были получены следующим образом: Абсолютно все мониторы (515 моделей), которые предлагает этот кибер гипермаркет, были выбраны для сравнения друг с другом, и получившаяся таблица преобразована в SQL код:

Код очень большой и поэтому выведен в отдельный файл доступный для скачивания:
   Итак, мы имеем таблицу, в которой 14 столбцов и 515 строк технической информации - справочник по мониторам.

Теперь, к примеру,  мы можем узнать, какой самый дорогой или самый дешевый монитор:

SELECT Manufacturer, Model, Price
FROM Monitors
WHERE
      Price IN (
            (SELECT MIN(Price) FROM Monitors),
            (SELECT MAX(Price) FROM Monitors));

 
   Но, такой большой объем данных может выдать гораздо более разнообразную и полезную информацию.
   С помощью PIVOT в наших силах создать хороший отчет. Например, в справочнике по мониторам указан вес практически каждого из них, но было бы не некорректно уровнять все мониторы под одну гребенку, ведь разные производители используют различные по весу блоки при изготовлении, так же у мониторов разная диагональ. Совершенно естественно, что мониторы с диагональю в 24 дюйма будут тяжелее, чем мониторы с диагональю 17 дюймов.

   Следующий SQL запрос, с использованием PIVOT, предоставит в наше распоряжение отчет, который будет  рассчитывать средний вес, учитывая все описанные выше нюансы:

SELECT Manufacturer , [17],[19],[24],[27] FROM
      (     SELECT Manufacturer,Size,Weight
            FROM Monitors) AS SourceTable
PIVOT
      (AVG (Weight)
      FOR Size IN ([17],[19],[24],[27])
      ) AS PivotTable


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

 
 Порядок составления запроса с инструкций PIVOT можно мысленно разделить на четыре раздела:

1.       Составить SELECT запрос, который будет источником данных (SourceTable) для отчета и будет возвращать 2 или более столбцов (Пока мы рассматривали вариант только с тремя столбцами, чуть позже рассмотрим вариант с двумя и четырьмя). 

SELECT Manufacturer , [17],[19],[24],[27] FROM
      (     SELECT Manufacturer,Size,Weight
            FROM Monitors) AS SourceTable
PIVOT
      (AVG (Weight)
      FOR Size IN ([17],[19],[24],[27])
      ) AS PivotTable

На примере эта часть запроса выделена красным цветом

2.       Выбрать столбец в SourceTable, значения строк которого станут столбцами в отчете, и указать конкретные значения. ( К сожалению, мы обязаны заранее задать значения для столбцов, и нет возможности применить что то на подобии звездочки или ALL.)

SELECT Manufacturer , [17],[19],[24],[27] FROM
      (     SELECT Manufacturer,Size,Weight
            FROM Monitors) AS SourceTable
PIVOT
      (AVG (Weight)
      FOR Size IN ([17],[19],[24],[27])
      ) AS PivotTable

На примере эта часть запроса выделена красным цветом

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

SELECT Manufacturer , [17],[19],[24],[27] FROM
      (     SELECT Manufacturer,Size,Weight
            FROM Monitors) AS SourceTable
PIVOT
      (AVG (Weight)
      FOR Size IN ([17],[19],[24],[27])
      ) AS PivotTable

Или

SELECT * FROM
      (     SELECT Manufacturer,Size,Weight
            FROM Monitors) AS SourceTable
PIVOT
      (AVG (Weight)
      FOR Size IN ([17],[19],[24],[27])
      ) AS PivotTable


На примере эта часть запроса выделена красным цветом

4.       Теперь каждой ячейке соответствует группа, состоящая из значений единственно оставшегося столбца из SourceTable, который не участвовал ни в разделении на столбцы (Size), ни в опорных данных (Manufacturer). Необходимо применить агрегатную функцию к каждой группе.

SELECT Manufacturer , [17],[19],[24],[27] FROM
      (     SELECT Manufacturer,Size,Weight
            FROM Monitors) AS SourceTable
PIVOT
      (AVG (Weight)
      FOR Size IN ([17],[19],[24],[27])
      ) AS PivotTable

На примере эта часть запроса выделена красным цветом

  У вас может возникнуть вопрос, что будет, если в третьем пункте не указывать опорных столбцов, а использовать оператор звездочку. В данном случае SQL Server пойдет «от противного» все оставшиеся столбцы, не используемые в агрегатной функции, становятся опорными столбцами.

   Перед тем как пойти дальше, рекомендую выполнить несколько упражнений с использованием PIVOT и справочником мониторов:

1.       Получите среднее значение стоимости для мониторов каждого производителя, и различными диагоналями.
2.       Получите среднее значение стоимости для мониторов с различной разрешающей способностью, и различными диагоналями.
3.       Получите среднее значение стоимости для мониторов каждого производителя, и типа матрицы.

   Составьте еще пару запросов самостоятельно.

   Ранее я упоминал, что пока мы использовали SourceTable только с тремя столбцами, но можно 2 и более, рассмотрим на примерах.

SourceTable с двумя столбцами:

SELECT * FROM
      (     SELECT Manufacturer,Price
            FROM Monitors) AS SourceTable
PIVOT
      (MIN (Price)
      FOR Manufacturer IN ([ACER],[AOC],[ASUS])
      ) AS PivotTable

 
Опорных данных нет, и все результаты идут в одну строчку.

SourceTable с четырьмя и более столбцами:

SELECT Manufacturer, Brightness, [17],[19],[24],[27] FROM
       (      SELECT Manufacturer,Size,Price, Brightness
             FROM Monitors) AS SourceTable
PIVOT
       (MIN (Price)
       FOR Size IN ([17],[19],[24],[27])
       ) AS PivotTable

 
   Таким образом мы увеличиваем количество опорных данных, к примеру значения ячейки столбца 17 в 16 строке можно прочитать как «Минимальная стоимость для производителя ASUS с диагональю монитора 17 дюймов и яркостью экрана в 250 кд/м2», а в строке 31 для яркости 300.

Оператор UNPIVOT


В MSN дано определение:

«Оператор UNPIVOT производит действия, обратные PIVOT, преобразуя столбцы возвращающего табличное значение выражения в значения столбца.»

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

Разберем на таблице Employees (которая была создана нами в самом начале статьи).

SELECT * FROM Employees;

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

SELECT *
FROM
      (SELECT * FROM Employees) SourceTable
      UNPIVOT(a FOR Properties  in (Age,Salary))as UnpivotResultTable

 
Возьмем первую строку результата из запроса SourceTable

 
   Внутри UNPIVOT, в выражении FOR - IN используются столбцы Age, Salary.  Выражение UNPIVOT сводит все значения столбцов использовавшиеся в выражении FOR IN в два столбца – значение столбца (Column1) и имя столбца (Column2). То есть значение первой строки должно разделиться на две.

 
  В обеих строках значение в столбце Name остается неиспользованным значением из первой строки SourceTable.
   Что будет, если мы будем использовать все три столбца, UNPIVOT сведет все значения в 2 столбца, значит, каждая строка SourceTable будет разделена на три.

SELECT * FROM
      (SELECT
            Name,
            CAST(Age AS nvarchar(50)) Age,
            CAST(Salary AS nvarchar(50)) Salary
      FROM Employees) SourceTable
      UNPIVOT
      (Column1 FOR Column2  in (Name,Age,Salary)) AS UnpivotResultTable

 
    Результат вполне ожидаемый. Хочется отметить, что нам пришлось воспользоваться функцией CAST, так как все столбцы, указанные в FOR IN, должны быть одного типа.

   Вернитесь к началу статьи и постарайтесь понять, каким образом я повернул таблицу по часовой стрелке и почему назвал это «шаманством».

   На этом все, удачи вам и спасибо что прочитали мою статью. Буду рад оставленным комментариям и новым постоянным читателям.


Александр Кобелев.