Оператор UNPIVOT
Оператор UNPIVOT по смыслу противоположен оператору PIVOT. Как было описано в предыдущей статье, оператор PIVOT преобразует нормализованную таблицу в таблицу, у которой столбцами являются значения в исходной таблице. Оператор UNPIVOT берет сводную таблицу и преобразует ее обратно к нормализованной форме с одной строкой на запись данных, используя в результате имена столбцов в качестве значений. Например, предположим, что у нас есть следующая таблица с данными:
CREATE TABLE PIVOT_Sales(EmpId INT, [2005] MONEY, [2006] MONEY, [2007] MONEY)
INSERT PIVOT_Sales VALUES(1, 12000, 18000, 25000)
INSERT PIVOT_Sales VALUES(2, 15000, 6000, NULL)
INSERT PIVOT_Sales VALUES(3, NULL, 20000, 24000)
Это похоже на результат операции PIVOT из предыдущих двух статей. Для каждого сотрудника имеется по одной строке данных о продажах за три года, каждый год в своей колонке. Если не было данных о продажах для конкретного сотрудника за конкретный год, просто вставляется значение NULL. Мы можем преобразовать эту таблицу обратно к её виду в предыдущей статье, где имеется по одной строке для сотрудника за каждый год его продаж, используя следующую инструкцию UNPIVOT:
SELECT EmpId, CAST (Yr AS INT) AS Yr, Sales
FROM (SELECT EmpId, [2005], [2006], [2007] FROM PIVOT_Sales) AS p
UNPIVOT (Sales FOR Yr IN ([2005], [2006], [2007])) AS s
Здесь столбец Yr явно приведён к целому числу. По умолчанию тип для сводного столбца был бы NVARCHAR(128); длинна выбрана такой, какой может быть максимальная длинна имени столбца - 128 символов.
В результате получается:
EmpId Yr Sales
----------- ----------- ---------------------
1 2005 12000.00
1 2006 18000.00
1 2007 25000.00
2 2005 15000.00
2 2006 6000.00
3 2006 20000.00
3 2007 24000.00
В отличие от операций PIVOT, которые могут быть необратимыми, все операции UNPIVOT обратимы (при условии сохранения всех входных данных). То есть мы всегда можем преобразовать выходные данные операции UNPIVOT обратно в исходную таблицу, используя соответствующую операцию PIVOT. Также оператор UNPIVOT не требует и не поддерживает функции агрегирования.
Давайте посмотрим на план приведенного выше запроса:
|--Compute Scalar(DEFINE:([Expr1010]=CONVERT(int,[Expr1009],0)))
|--Filter(WHERE:([Expr1008] IS NOT NULL))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([PIVOT_Sales].[2005], [PIVOT_Sales].[2006], [PIVOT_Sales].[2007]))
|--Compute Scalar(DEFINE:([PIVOT_Sales].[EmpId]=[PIVOT_Sales].[EmpId]))
| |--Table Scan(OBJECT:([PIVOT_Sales]))
|--Constant Scan(VALUES:((N'2005',[PIVOT_Sales].[2005]),(N'2006',[PIVOT_Sales].[2006]),(N'2007',[PIVOT_Sales].[2007])))
Этот план запроса каждую строку входной таблицы объединяет со считанными константами, создавая строки — по одной для каждого из столбцов, перечисленных в предложении UNPIVOT IN. Затем отфильтровываются все строки, содержащие NULL (обратите внимание, что [Expr1008] это столбец Sales, а [ Expr1009 ] это столбец Yr). В этом плане запроса стоит отметить несколько моментов. Во-первых, тут должно быть соединение Nested Loops, поскольку оператор сканирования констант для создания строк использует коррелированные параметры со внешней стороной соединения. Невозможно получить эти строки без этих коррелированных параметров. Во-вторых, соединение не обязательно должно быть левым внешним соединением. Постоянное сканирование всегда производит ровно три строки, и, таким образом, внешние строки всегда будут участвовать в соединении и никогда не приведут к появлению NULL. Тем не менее внешнее соединение в этом контексте безвредно и ведет себя как внутреннее соединение.
Обратите внимание, что мы можем переписать запрос так:
SELECT p.EmpId, Yr, Sales
FROM PIVOT_Sales AS p CROSS APPLY
(
SELECT EmpId, 2005 AS Yr, [2005] AS Sales UNION ALL
SELECT EmpId, 2006, [2006] UNION ALL
SELECT EmpId, 2007, [2007]
) AS s
WHERE Sales IS NOT NULL
Этот запрос имеет почти такой же план запроса. Синтаксис UNION ALL дает результат, аналогичный сканированию констант, за исключением того, что теперь есть три считывания констант и оператор конкатенации:
|--Filter(WHERE:([Union1007] IS NOT NULL))
|--Nested Loops(Inner Join, OUTER REFERENCES:([p].[2005], [p].[2006], [p].[2007]))
|--Table Scan(OBJECT:([tempdb].[dbo].[PIVOT_Sales] AS [p]))
|--Concatenation
|--Constant Scan(VALUES:(((2005))))
|--Constant Scan(VALUES:(((2006))))
|--Constant Scan(VALUES:(((2007))))
В этом плане [Union1007] — это столбец Sale. На самом деле мы можем увидеть определение [Union1007] из столбца DefinedValues оператора конкатенации при использовании SET SHOWPLAN_ALL ON. Значения для [Union1007] на самом деле получены непосредственно из коррелированных параметров CROSS APPLY (из таблицы PIVOT_Sales), а не из считанных констант. [Union1006] — это столбец Yr, и значения получены в результате сканирования констант.