Особенности логической обработки оператора UNPIVOT и альтернатива обратному транспонированию с помощью оператора APPLY
В этой статье мы продолжим тему логической обработки запросов. В предыдущих материалах серии были рассмотрены табличные операторы 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. Его синтаксис требует указать три упомянутых выше элемента, участвующих в задаче обратного транспонирования.
Целевое имя столбца, в котором будут храниться значения исходного столбца (в нашем случае val).
Целевое имя столбца, в котором будут храниться имена исходного столбца (в нашем случае orderyear).
Имена исходных столбцов (в нашем случае [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 и, наконец, динамическое обратное транспонирование.
Листинг 1. Построение таблицы MyPivotedOrders и заполнение ее данными
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;
Листинг 2. Альтернативное решение обратного транспонирования с использованием оператора APPLY
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;
Листинг 3. Обратное транспонирование нескольких наборов столбцов
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;
Листинг 4. Добавление столбца
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;
Листинг 5. Сведение результатов
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;
Листинг 6. Полный программный код обратного транспонирования
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;