В синтаксисе T-SQL для выполнения подобного преобразования предусмотрена отдельная конструкция PIVOT . Стоит заметить, что в SQL Server 2000 поддержки конструкции PIVOT еще не было, поэтому аналогичные задачи решались через множественные CASE WHEN.
Собственно, почему я упомянул о CASE WHEN , если есть PIVOT ? Ведь, по определению, PIVOT более элегантная конструкция и, соответственно, должна быть более эффективной.
Проверим это на практике…
Создадим таблицу, которая будет содержать информацию о выходах сотрудников на рабочие места.
IF OBJECT_ID('dbo.WorkOut', 'U') IS NOT NULL DROP TABLE dbo.WorkOut GO CREATE TABLE dbo.WorkOut ( DateOut DATETIME NOT NULL, EmployeeID INT NOT NULL, CONSTRAINT PK_WorkOut PRIMARY KEY CLUSTERED (DateOut, EmployeeID) ) GO
И заполним ее тестовыми данными:
INSERT INTO dbo.WorkOut (EmployeeID, DateOut) SELECT TOP 1500000 ao.[object_id], ao1.modify_date FROM sys.all_objects ao CROSS JOIN sys.all_objects ao1
Далее напишем PIVOT запрос, который будет возвращать количество выходов по каждому сотруднику в разрезе дней:
SELECT * FROM ( SELECT EmployeeID , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t PIVOT ( COUNT([WeekDay]) FOR [WeekDay] IN ( Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday ) ) p
При выполнении запроса мы получим следующий план и время выполнения:
SQL Server Execution Times:
CPU time = 5662 ms, elapsed time = 8075 ms.
На плане можно увидеть операторы Sort и Hash Match . Их эффективная работа очень сильно зависит от размера входящих данных и доступного объема физической памяти, чтобы эти самые данные обработать.
При невозможности выделить требуемый объем памяти, обработка результатов будет происходить в базе tempdb (восклицательный знак) - это может приводить к ощутимой нагрузке на дисковую подсистему и увеличению времени выполнения запроса:
SQL Server Execution Times:
CPU time = 6193 ms, elapsed time = 9571 ms.
Посмотрим как ведет себя аналогичная по функциональности конструкция из CASE WHEN условий:
SELECT EmployeeID , Monday = COUNT(CASE WHEN [WeekDay] = 'Monday' THEN 1 END) , Tuesday = COUNT(CASE WHEN [WeekDay] = 'Tuesday' THEN 1 END) , Wednesday = COUNT(CASE WHEN [WeekDay] = 'Wednesday' THEN 1 END) , Thursday = COUNT(CASE WHEN [WeekDay] = 'Thursday' THEN 1 END) , Friday = COUNT(CASE WHEN [WeekDay] = 'Friday' THEN 1 END) , Saturday = COUNT(CASE WHEN [WeekDay] = 'Saturday' THEN 1 END) , Sunday = COUNT(CASE WHEN [WeekDay] = 'Sunday' THEN 1 END) FROM ( SELECT EmployeeID , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t GROUP BY EmployeeID
При выполнении мы получим более простой план. При этом время выполнения будет не слишком отличатся от PIVOT (разумеется в рамках погрешности):
SQL Server Execution Times:
CPU time = 5201 ms, elapsed time = 8400 ms.
В условиях нехватки памяти мы получим следующие результаты:
SQL Server Execution Times:
CPU time = 6006 ms, elapsed time = 13883 ms.
Из полученных данных можно сделать небольшое наблюдение - при агрегации данных по одному столбцу явное преимущество за конструкцией PIVOT . Даже в ситуации, когда наблюдается нехватка памяти на обработку результатов.
Теперь посмотрим как себя ведут данные примеры при увеличении чиста столбцов по которым идет агрегация.
1. Группировка в разрезе: сотрудник + год:
SELECT EmployeeID , [Year] , Monday = COUNT(CASE WHEN [WeekDay] = 'Monday' THEN 1 END) , ... , Sunday = COUNT(CASE WHEN [WeekDay] = 'Sunday' THEN 1 END) FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t GROUP BY EmployeeID, [Year]
SQL Server Execution Times:
CPU time = 5569 ms, elapsed time = 9200 ms.
SELECT * FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t PIVOT ( COUNT([WeekDay]) FOR [WeekDay] IN ( Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday ) ) p
SQL Server Execution Times:
CPU time = 5454 ms, elapsed time = 8878 ms.
Если сравнить планы, то можно заметить, что операция Hash Match более затратна при использовании PIVOT , но время выполнения говорит об обратном.
2. Группировка в разрезе: сотрудник + год + месяц
SELECT EmployeeID , [Year] , [Month] , Monday = COUNT(CASE WHEN [WeekDay] = 'Monday' THEN 1 END) , ... , Sunday = COUNT(CASE WHEN [WeekDay] = 'Sunday' THEN 1 END) FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [Month] = MONTH(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t GROUP BY EmployeeID, [Year], [Month]
SQL Server Execution Times:
CPU time = 6365 ms, elapsed time = 9979 ms.
SELECT * FROM ( SELECT EmployeeID , [Year] = YEAR(DateOut) , [Month] = MONTH(DateOut) , [WeekDay] = DATENAME(WEEKDAY, DateOut) FROM dbo.WorkOut ) t PIVOT ( COUNT([WeekDay]) FOR [WeekDay] IN ( Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday ) ) p
SQL Server Execution Times:
CPU time = 6193 ms, elapsed time = 9861 ms.
Собственно говоря, ситуация повторяется - SQL Server оценивает PIVOT конструкцию как более затратную.
Но время выполнения опять все ставит на свои места.
Из этого можно сделать небольшие выводы: в преобладающем большинстве ситуаций с помощью конструкции PIVOT можно быстрее выполнить преобразования столбцов в строки.
Небольшое замечание при этом следующее: с увеличением числа столбцов, по которым идет агрегация, разница во времени выполнения между PIVOT и CASE WHEN будет сокращаться и в определенный момент будет в рамках погрешности измерений.
PS:
Все эксперименты проводились на SQL Server 2012 SP1 (11.00.3128) .
Планы выполнения были получены через dbForge Studio for SQL Server.