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

В этой статье мы продолжим тему логической обработки запросов. В предыдущих материалах серии были рассмотрены табличные операторы JOIN, APPLY и PIVOT предложения FROM. На этот раз речь пойдет о четвертом и последнем табличном операторе предложения FROM — UNPIVOT.

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

USE TSQLV4;

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

Обратное транспонирование данных

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

В ходе операции обратного транспонирования обрабатывается любое число исходных столбцов, превышающее единицу. Они преобразуются в два целевых столбца, в одном из которых содержатся значения исходного столбца (values column), в другом — имена исходных столбцов (names column).

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

Аналогично оператору PIVOT, T-SQL поддерживает оператор, именуемый UNPIVOT, который также используется в предложении FROM. Как и PIVOT, оператор UNPIVOT выполняет последовательность логических шагов. Я обозначаю шаги формулой 1-UN, где 1 отражает то обстоятельство, что предложение FROM представляет собой первое важное предложение, обрабатываемое логически, U — сокращение от UNPIVOT, а N — номер шага в операторе UNPIVOT.

Оператор UNPIVOT применяет три логических шага:

• 1-U1. Создание копий.
• 1-U2. Извлечение элемента.
• 1-U3. Удаление значений NULL.

В следующих разделах показано применение этих шагов для нашей тестовой задачи.

1-U1. Создание копий

Первый шаг к обратному транспонированию данных заключается в формировании отдельного экземпляра каждой исходной строки для каждого столбца без транспонирования. Наши столбцы без транспонирования представляют годы заказов: 2014, 2015 и 2016, поэтому на данном шаге формируется копия для каждого года. В таблице 3 показан результат этого шага для нашей тестовой задачи.

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

1-U2. Извлечение элемента

На втором шаге, по сути, создается столбец значений (в нашем примере ему назначено имя val). Извлекается элемент из исходных столбцов, соответствующих именам, которые представлены текущей копией. В нашем примере извлекается значение из исходного столбца года, который соответствует текущему значению orderyear. Если orderyear — 2014, val возвращает значение из столбца [2014], если orderyear — 2015, val возвращает значение из [2015], а когда orderyear — 2016, val возвращает значение из [2016]. В таблице 4 показан желаемый результат этого шага для нашей тестовой задачи.

1-U3. Удаление значений NULL

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

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

Оператор UNPIVOT

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

1. Целевое имя столбца, в котором будут храниться значения исходного столбца (в нашем случае val).
2. Целевое имя столбца, в котором будут храниться имена исходного столбца (в нашем случае orderyear).
3. Имена исходных столбцов (в нашем случае [2014], [2015], [2016]).

Вы указываете эти элементы в операторе UNPIVOT с использованием следующего синтаксиса:

SELECT *
   FROM 
           UNPIVOT( 1 FOR 2 IN (3) )
           AS ;

В применении к нашим данным полный запрос с оператором UNPIVOT выглядит следующим образом:

SELECT empid, orderyear, val
FROM Sales.MyPivotedOrders
  UNPIVOT( val FOR orderyear
  IN ([2014],[2015],[2016]) ) AS U;

На рисунке 1 показано, как элементы, участвующие в операторе UNPIVOT, соответствуют входным и выходным данным оператора.

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

Обратите внимание, что столбец имен (orderyear) вводится как NVARCHAR (128). Если требуется другой тип, например INT, следует выполнить его приведение.

На рисунке 2 показана блок-схема, описывающая логическую обработку запроса предложения FROM, со всеми четырьмя табличными операторами, в том числе оператором UNPIVOT.

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

Альтернативное решение с использованием APPLY

Оператор UNPIVOT построен очень изящно и отличается лаконичностью, но у него есть недостатки. Один из них — невозможность (по крайней мере, простым способом) обратного транспонирования нескольких наборов исходных столбцов в несколько целевых столбцов значений. Представьте, например, что у вас есть один набор столбцов, представляющих значения заказов, и другой, представляющий количества заказов. С другой стороны, вы можете быть не заинтересованы в удалении строк со значениями NULL в столбце значений. В листинге 2 приводится альтернативное решение обратного транспонирования с использованием оператора APPLY для нашей тестовой задачи.

В решении используется оператор CROSS APPLY с конструктором табличного значения для определения трех строк для трех лет заказов, чтобы формировать копии (шаг 1).

Во второй столбец в каждой из трех строк извлекается элемент из исходной строки года, соответствующий текущему году заказа (шаг 2). Вы можете выполнить первый шаг с помощью CROSS JOIN, но не второй. Помните, что объединение воспринимает свои два списка входных данных как набор, поэтому нельзя ссылаться с одной стороны на элементы другой. И наоборот, CROSS APPLY оценивает входные данные слева направо и потому позволяет входным данным справа ссылаться на данные слева. Чтобы обеспечить поддержку нескольких величин (например, val и qty), просто укажите больше столбцов в каждой строке.

Наконец, поскольку APPLY создает псевдоним столбца значений (в нашем случае val) в предложении FROM, можно ссылаться на этот псевдоним в предложении WHERE, если вы хотите удалить строки со значениями NULL. Очевидно, что удалять значения NULL не обязательно.

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

В таблице 5 показано содержимое MyPivotedOrders2.

Требуется создать два столбца значений: из val2014, val2015, val2016 необходимо создать целевой столбец с именем val, а из qty2014, qty2015, qty2016 — целевой столбец с именем qty. Как отмечалось, для каждой целевой величины просто добавьте в каждую строку в конструкторе табличных значений столбец, соответствующий этой величине, например, как в листинге 4.

Выходные данные, формируемые этим запросом, показаны в таблице 6.

Сочетание операторов

Помните, что вы можете сочетать табличные операторы в предложении FROM. С точки зрения логической обработки запросов они обрабатываются слева направо в том порядке, в котором записаны. Это означает, что результаты одного оператора становятся входными данными для следующего. Например, предположим, что требуется транспонировать данные из таблицы Sales.MyPivotedOrders, и, вместо того чтобы показать сотрудников в строках, а годы заказов в столбцах, вы хотите возвратить годы заказов в строках, а идентификаторы сотрудников — в столбцах. В таблице 7 показаны необходимые выходные данные.

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

Динамическая операция UNPIVOT

В предыдущей статье было показано, как использовать метод на основе параметра FOR XML, чтобы построить запрос PIVOT и выполнить его динамически, избегая жесткого распределения значений. Очень похожим способом можно выполнить задачу динамического обратного транспонирования. В нашем примере с таблицей N’Sales.MyPivotedOrders вы можете получить имена столбцов, для которых нужно выполнить обратное транспонирование путем запроса к представлению sys.columns. Вы можете выбрать все столбцы, кроме столбца empid. В остальном все происходит так же, как было показано в задаче динамического транспонирования. Метод FOR XML используется для создания списка имен столбцов с разделительными запятыми, который должен появиться в предложении IN оператора UNPIVOT и конкатенации статических частей запроса перед списком столбцов и после него. Наконец используется хранимая процедура sp_executesql для запуска кода.

В листинге 6 приводится полный программный код решения.

Итак, в этой статье я остановился на логической обработке запросов табличного оператора UNPIVOT. Были описаны три этапа работы оператора: создание копий, извлечение элемента и удаление значений NULL; представлены синтаксис оператора и рекомендации, как запомнить элементы этого синтаксиса. Мы рассмотрели недостатки оператора, альтернативное решение с использованием оператора APPLY, сочетание операторов PIVOT и UNPIVOT и, наконец, динамическое обратное транспонирование.

IF OBJECT_ID(N'Sales.MyPivotedOrders', N'U') IS NOT NULL DROP TABLE Sales.MyPivotedOrders;
-- В SQL Server 2016 используйте: DROP TABLE IF EXISTS Sales.MyPivotedOrders;
GO

WITH C AS
(
  SELECT empid, YEAR(orderdate) AS orderyear, val
  FROM Sales.OrderValues
  WHERE custid = 5
) 
SELECT *
INTO Sales.MyPivotedOrders
FROM C
  PIVOT( SUM(val)
    FOR orderyear IN ([2014], [2015], [2016]) ) AS P;

ALTER TABLE Sales.MyPivotedOrders
  ADD CONSTRAINT PK_MyPivotedOrders PRIMARY KEY(empid);

SELECT *
FROM Sales.MyPivotedOrders;

SELECT empid, orderyear, val
FROM Sales.MyPivotedOrders
  CROSS APPLY ( VALUES(2014, [2014]),
                      (2015, [2015]),
                      (2016, [2016]) ) AS A(orderyear, val)
WHERE val IS NOT NULL;

IF OBJECT_ID(N'Sales.MyPivotedOrders2', N'U') IS NOT NULL
DROP TABLE Sales.MyPivotedOrders2;
-- В SQL Server 2016 используйте: DROP TABLE IF EXISTS Sales.MyPivotedOrders2;
GO

SELECT empid,
SUM(CASE WHEN orderyear = 2014 THEN val END) AS val2014,
SUM(CASE WHEN orderyear = 2015 THEN val END) AS val2015,
SUM(CASE WHEN orderyear = 2016 THEN val END) AS val2016,
SUM(CASE WHEN orderyear = 2014 THEN qty END) AS qty2014,
SUM(CASE WHEN orderyear = 2015 THEN qty END) AS qty2015,
SUM(CASE WHEN orderyear = 2016 THEN qty END) AS qty2016
INTO Sales.MyPivotedOrders2
FROM Sales.OrderValues
CROSS APPLY ( VALUES(YEAR(orderdate)) ) AS A(orderyear)
WHERE custid = 5
GROUP BY empid;
ALTER TABLE Sales.MyPivotedOrders2
ADD CONSTRAINT PK_MyPivotedOrders2 PRIMARY KEY(empid);
SELECT *
FROM Sales.MyPivotedOrders2; 

SELECT empid, orderyear, val, qty
FROM Sales.MyPivotedOrders2
  CROSS APPLY ( VALUES(2014, [val2014], [qty2014]),
                      (2015, [val2015], [qty2015]),
                      (2016, [val2016], [qty2016]) ) AS A(orderyear, val, qty)
WHERE val IS NOT NULL OR qty IS NOT NULL;

SELECT *
FROM Sales.MyPivotedOrders
  UNPIVOT( val FOR orderyear IN ([2014],[2015],[2016]) ) AS U
  PIVOT( MAX(val) FOR empid IN([1], [2], [3], [4], [5], [8], [9]) ) AS P;

DECLARE
  @cols AS NVARCHAR(1000),
  @sql  AS NVARCHAR(4000);

-- Конструирование списка столбцов для предложения IN
SET @cols = STUFF(
  (SELECT N','+ QUOTENAME(name) AS [text()]
   FROM sys.columns
   WHERE object_id = OBJECT_ID(N'Sales.MyPivotedOrders', N'U')
     AND name NOT IN(N'empid')
   ORDER BY name
   FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(MAX)'),
  1, 1, N’’);

-- Конструирование полной инструкции T-SQL
-- и динамическое выполнение
SET @sql = N’SELECT empid, orderyear, val
FROM Sales.MyPivotedOrders
  UNPIVOT(val FOR orderyear IN(' + @cols + N')) AS U;';

EXEC sys.sp_executesql @stmt = @sql;