Логическая обработка запросов: предложение FROM и PIVOT

В этой статье мы продолжим обсуждение логической обработки запросов. В первой части был дан обзор концепции (опубликована в Windows IT Pro/RE № 3 за 2016 год). Затем я начал рассматривать предложение FROM, которое является первым важным предложением, обрабатываемым логически.

Во второй части было рассказано об объединениях, а в третьей — о табличном операторе APPLY (части 2 и 3 опубликованы в Windows IT Pro/RE № 5 за 2016 год). На этот раз речь пойдет о предложении FROM, в частности об аспектах логической обработки запросов табличного оператора PIVOT.

Я буду использовать ту же тестовую базу данных TSQLV4, что и в прошлых публикациях. Если вы еще не установили ее, то исходный код можно получить по адресу: http://tsql.solidq.com/SampleDatabases/TSQLV4.zip. Убедитесь, что вы подключились к базе данных TSQLV4, запустив тестовый программный код, приведенный в данной статье:

USE TSQLV4;

Сведение данных

Для демонстрации сведения используется представление Sales.MyOrders, создаваемое с помощью программного кода в листинге 1.

Запрос, показывающий содержимое представления, выдает данные, приведенные в таблице 1.

При сведении данных нередко требуется выполнить преобразование из строк в столбцы. Это делается при составлении отчетов, а также в некоторых других случаях, например при обработке настраиваемых статистических функций, реляционном делении и решении других задач. В качестве примера предположим, что нам нужно направить запрос в представление MyOrders и возвратить строку для каждого идентификатора сотрудника, столбец для каждого года заказа и сумму всех других значений заказов на пересечении каждого сотрудника и года. Желаемый результат показан в таблице 2.

Раньше в сгруппированных запросах для выполнения задач сведения использовались выражения CASE. Признавая необходимость в сведении данных, компания Microsoft на каком-то этапе ввела специальный табличный оператор PIVOT.

Оператор PIVOT

Подход Microsoft к проектированию оператора PIVOT весьма примечателен. Вместо того чтобы задействовать целый запрос, для сведения используется табличный оператор. Табличный оператор — лишь один из шагов в предложении FROM. В случае с оператором PIVOT это шаг, на котором выполняется поворот данных из строк в столбцы. Как любой другой табличный оператор, PIVOT может применяться к результирующей виртуальной таблице, полученной с использованием предыдущих табличных операторов, таких как JOIN и APPLY. Выходные данные оператора PIVOT — виртуальная таблица; она может быть использована в качестве входных данных для последующих табличных операторов.

Как и в случае с операторами JOIN и APPLY, логическая обработка запросов оператора PIVOT может быть разбита на последовательность логических шагов:

• 1-P1. Группирование (группирующий элемент empid);
• 1-P2. Распространение (распространяющий элемент orderyear IN ([2014], [2015], [2016]));
• 1-P3. Агрегирование (элемент агрегирования SUM (val)).

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

1-P1. Группирование

Первый шаг оператора PIVOT — группирование строк из входных данных. В нашем примере группирующим элементом является идентификатор сотрудника (столбец empid), поскольку нам нужна результирующая строка для каждого сотрудника. Группирующий элемент часто именуется построчным (on rows) элементом, так как он определяет, что представляет каждая строка результата. В таблице 3 показан результат шага 1-P1 для нашего примера.

1-P2. Распространение

Второй шаг в операторе PIVOT — распространение данных. Предыдущий шаг группирования определяет, что вы хотите получить по строкам; аналогично, текущий шаг распространения определяет, что вы хотите получить по столбцам. Поэтому распространяющий элемент часто является постолбцовым (on cols) элементом. Вы распространяете некоторые элементы данных из одного исход­ного столбца (в нашем случае это столбец val) на основе распространяющего элемента (в нашем случае это столбец orderyear) на несколько целевых столбцов (в нашем случае [2014], [2015], [2016]). Часть синтаксиса оператора PIVOT, определяющая распространение:

<распространяющий_элемент>
   IN (<целевые_столбцы>)

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

Целевые столбцы основаны на отдельных значениях в распространяемом столбце в исходной виртуальной таблице. На данный момент в представлении MyOrders три разных года заказов: 2014, 2015 и 2016. Эти значения становятся именами целевых столбцов, и синтаксис оператора PIVOT требует, чтобы на них ссылались именно таким образом. Если значения являются обычными идентификаторами, то разделители вводить не требуется. Если же это нестандартные идентификаторы, например если они начинаются с цифры, содержат пробел, представляют собой зарезервированное ключевое слово, то их необходимо разделить с помощью специфических для T-SQL квадратных скобок или стандартных двойных кавычек. Годы заказов начинаются с цифры, поэтому необходимы разделители: [2014], [2015], [2016].

В нашем примере часть оператора PIVOT, которая определяет распространение, имеет следующий вид:

orderyear IN ([2014], [2015], [2016])

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

В таблице 4 показан результат шага 1-P2 с нашим примером.

1-P3. Агрегирование

Третий шаг в операторе PIVOT — вычислить некоторую меру, которую необходимо возвратить в каждом пересечении строки и столбца. В нашем случае мера, которую нужно вычислить — столбец val, а применяемая агрегатная функция, которую следует применить — SUM. Поэтому мы предоставляем оператор PIVOT с элементом агрегирования SUM (val). Как и в случае с распространяющим элементом, элемент агрегирования должен основываться на имени столбца, с которым не производится манипуляций. Результатом этого шага будет окончательный результат оператора PIVOT. В нашем примере это результат, показанный выше в таблице 2.

Ниже приводится синтаксис оператора PIVOT с элементами, используемыми на разных шагах (1: группирование, 2: распространение, 3: агрегирование):

SELECT *
FROM 
  PIVOT( 3 FOR 2 ) AS ;

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

  В листинге 2 приведен полный запрос для нашей задачи сведения с использованием оператора PIVOT.

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

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

   

  

    

      

        
    
    

      

        Рисунок 1. Оператор PIVOT
    
  


   

  На рисунке 2 мы видим более полную картину логической обработки запроса трех табличных операторов, описанных в этой серии статей: JOIN, APPLY и PIVOT.

   

  

    

      

        
    
    

      

        Рисунок 2. Блок-схема логической обработки запросов — предложение FROM
    
  


   

  Рассмотрите план выполнения для запроса нашего решения на приведенном экране и обратите внимание на свойства оператора Stream Aggregate.

   

  

    

      

        
    
    

      

        Экран. План для оператора PIVOT
    
  


   

  Следует отметить, что оператор правильно идентифицирует подразумеваемый группирующий элемент как столбец empid. Также заметьте, как оператор выполняет распространение и агрегирование. Для каждого отдельного значения распространения (в нашем случае это год заказа) определяется значение на основе агрегатной функции, примененной к результату выражения CASE. Например, для года заказа 2016 определено следующее значение:
Expr1040: Scalar Operator(SUM(CASE
   WHEN orderyear=(2016) THEN val
   ELSE NULL END))

  Поскольку оба столбца, orderyear и val, являются результатами вычислений в исходном представлении, план ссылается на имена выражений Expr1005 и Expr1004, соответственно. Для удобства я заменил эти имена выражений на более понятные имена столбцов.

  Явный группированный запрос

  Как было показано выше, оператор PIVOT спроектирован как табличный оператор. Другое широко применяемое решение для сведения данных — явный группированный запрос. Шаг группирования выполняется с использованием предложения GROUP BY, шаг распространения — с выражениями CASE, а шаг агрегирования — с помощью функции агрегирования, примененной к результатам выражений CASE. Как выполняется наша задача с использованием этого решения, показано в листинге 3.

  Понятно, что оператор PIVOT скрыто выполняет функцию, во многом схожую с той, которая показана в плане запроса на экране. Основное преимущество оператора PIVOT — отсутствие необходимости явно указывать длинные выражения CASE. Если вы отправляете запрос из приложения и имеется много распространяемых значений, то через сеть передаются более короткие строки запросов. Однако у явного группированного запроса есть свои преимущества. Во-первых, он стандартный. Во-вторых, для него не требуется готовить табличного выражения как входной таблицы, поскольку ни один элемент не определен неявно. В-третьих, все элементы сведения могут быть выражениями с управляемыми столбцами. В-четвертых, он позволяет при необходимости вычислять несколько агрегатов без дополнительного сканирования данных. При использовании оператора PIVOT вы ограничены лишь одним агрегатом. Например, запрос, приведенный в листинге 4, выполняет статистическую обработку SUM и AVG.

  Этот запрос формирует выходные данные, представленные в таблице 5.

   


   

  Динамический PIVOT

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

  В нашем примере распространяемые значения — это годы заказа. Следующий запрос возвращает упорядоченные годы:
SELECT N’,’ + QUOTENAME(orderyear)
FROM Sales.MyOrders
GROUP BY orderyear
ORDER BY orderyear;

  В программном коде используется функция QUOTENAME, чтобы поместить годы в квадратные скобки для разграничения, и перед каждым годом добавляется начальная запятая. Этот запрос формирует следующие выходные данные:
--------
,[2014]
,[2015]
,[2016]

  Чтобы объединить значения в одну строку, добавьте FOR XML PATH (‘’) в конце запроса и назначьте псевдоним выражения [text ()], чтобы вернуть узлы как текстовые узлы:
SELECT N’,’ + QUOTENAME(orderyear)
   AS [text()]
FROM Sales.MyOrders
GROUP BY orderyear
ORDER BY orderyear
FOR XML PATH(‘’);

  Выходные данные будут иметь следующий вид:
, [2014], [2015], [2016]

  Если среди ваших значений встречаются определенные специальные символы, такие как ‘>’, то для них в XML-представлении используются специальные теги, в частности ‘>’. Чтобы возвратить собственно символы, добавьте TYPE к элементу FOR XML, поместите запрос в круглые скобки, чтобы получить скалярное выражение, и с помощью метода. value возвратите значение как NVARCHAR:
SELECT
   (SELECT N’,’ + QUOTENAME(orderyear)
   AS [text()]
   FROM Sales.MyOrders
   GROUP BY orderyear
   ORDER BY orderyear
   FOR XML PATH(‘’), TYPE).value(‘.[1]’,
   ‘NVARCHAR(MAX)’);

  Вам потребуется удалить начальную запятую. Для этого используйте строковую функцию. Я предпочитаю функцию STUFF, так как с ее помощью можно удалить нужное число символов из указанной позиции. Программный код приведен ниже:
SELECT
  STUFF(
    (SELECT N’,’ + QUOTENAME(orderyear)
    AS [text()]
     FROM Sales.MyOrders
     GROUP BY orderyear
     ORDER BY orderyear
     FOR XML PATH(‘’), TYPE).value(‘.[1]’,
     ‘NVARCHAR(MAX)’), 1, 1, ‘’);

  Этот программный код формирует следующие выходные данные:
[2014], [2015], [2016]

  Есть строка, которую нужно поместить в предложение IN оператора PIVOT. Части, расположенные перед ней и после нее — статические. Программный код в листинге 5 показывает, как построить полный запрос, а затем выполнить его динамически с хранимой процедурой sp_executesql.

  Выходные данные показаны в таблице 2. С этим решением вам никогда не потребуется жестко задавать распространяемые значения, и данные всегда будут содержать все существующие значения.

  Итак, в этой статье мы рассмотрели логическую обработку запросов оператора PIVOT. Были описаны три шага: группирование, распространение и агрегирование, а также связанные с ними элементы. Я рассказал о классической альтернативе использованию явного группированного запроса и преимуществах и недостатках каждого подхода. В следующей статье речь пойдет о четвертом табличном операторе, UNPIVOT.

  Листинг 1. Создание представления Sales.MyOrders
IF OBJECT_ID(N'Sales.MyOrders', N'V') IS NOT NULL DROP VIEW Sales.MyOrders;
-- В SQL Server 2016 используйте: DROP VIEW IF EXISTS Sales.MyOrders;
GO

CREATE VIEW Sales.MyOrders
AS

SELECT orderid, empid, YEAR(orderdate) AS orderyear, val, qty
FROM Sales.OrderValues
WHERE custid = 5;
GO

SELECT *
FROM Sales.MyOrders;

  Листинг 2. Полный запрос сведения с использованием оператора PIVOT
WITH C AS
(
  SELECT empid, orderyear, val
  FROM Sales.MyOrders
)
SELECT *
FROM C
  PIVOT( SUM(val)
    FOR orderyear IN ([2014], [2015], [2016]) ) AS P;

  Листинг 3. Явный групповой запрос
SELECT empid,
  SUM(CASE WHEN orderyear = 2014 THEN val END) AS [2014],
  SUM(CASE WHEN orderyear = 2015 THEN val END) AS [2015],
  SUM(CASE WHEN orderyear = 2016 THEN val END) AS [2016]
FROM Sales.MyOrders
GROUP BY empid;

  Листинг 4. Статистическая обработка SUM и AVG 
SELECT empid,
  SUM(CASE WHEN orderyear = 2014 THEN val END) AS sum2014,
  AVG(CASE WHEN orderyear = 2014 THEN val END) AS avg2014,
  SUM(CASE WHEN orderyear = 2015 THEN val END) AS sum2015,
  AVG(CASE WHEN orderyear = 2015 THEN val END) AS avg2015,
  SUM(CASE WHEN orderyear = 2016 THEN val END) AS sum2016,
  AVG(CASE WHEN orderyear = 2016 THEN val END) AS avg2016
FROM Sales.MyOrders
GROUP BY empid;

  Листинг 5. Полный запрос для решения
DECLARE
  @cols AS NVARCHAR(1000),
  @sql  AS NVARCHAR(4000);

SET @cols =
  STUFF(
    (SELECT N',' + QUOTENAME(orderyear) AS [text()]
     FROM Sales.MyOrders
     GROUP BY orderyear
     ORDER BY orderyear
     FOR XML PATH(''), TYPE).value('.[1]', 'NVARCHAR(MAX)'), 1, 1, '');

SET @sql = N'WITH C AS
(
  SELECT empid, orderyear, val
  FROM Sales.MyOrders
)
SELECT *
FROM C
  PIVOT( SUM(val)
    FOR orderyear IN (' + @cols + N') ) AS P;';

EXEC sys.sp_executesql @stmt = @sql;