ピボット

現代の情報システムでは、多くの場合、意思決定プロセスは統合された情報に基づいています。 実際には、このような情報を操作するビジネスロジックを開発する場合、行を列に変換することが非常に頻繁に必要です。

T-SQL構文は、この変換を実行するための個別のPIVOT構造を提供します。 SQL Server 2000ではPIVOTがまだサポートされていなかったため、複数のCASE WHENを通じて同様のタスクが解決されたことは注目に値します 。

実際、 PIVOTがある場合、なぜCASE WHENに言及したのですか？ 実際、定義上、 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の実行時間：
CPU時間= 5662ミリ秒、経過時間= 8075ミリ秒。

計画では、演算子SortおよびHash Matchを見ることができます。 これらの効果的な動作は、これらの同じデータを処理するために、着信データのサイズと物理メモリの利用可能な量に大きく依存します。

必要な量のメモリを割り当てることができない場合、結果の処理はtempdbデータベース（感嘆符）で行われます。これにより、ディスクサブシステムに顕著な負荷がかかり、クエリの実行時間が長くなる可能性があります。



SQL Serverの実行時間：
CPU時間= 6193ミリ秒、経過時間= 9571ミリ秒。

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の実行時間：
CPU時間= 5201ミリ秒、経過時間= 8400ミリ秒。

メモリ不足の状態では、次の結果が得られます。



SQL Serverの実行時間：
CPU時間= 6006ミリ秒、経過時間= 13883ミリ秒。

得られたデータから少し観察することができます-単一の列でデータを集約する場合、 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の実行時間：
CPU時間= 5569ミリ秒、経過時間= 9200ミリ秒。

 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の実行時間：
CPU時間= 5454ミリ秒、経過時間= 8878ミリ秒。

プランを比較すると、 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の実行時間：
CPU時間= 6365ミリ秒、経過時間= 9979ミリ秒。

 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の実行時間：
CPU時間= 6193ミリ秒、経過時間= 9861ミリ秒。

実際のところ、状況は繰り返されます。SQLServerはPIVOT構造をより高価であると評価します 。

しかし、ランタイムはすべてを適切な場所に配置します。

これから小さな結論を導き出すことができます。圧倒的多数の状況で、 PIVOT構造を使用して、列を行にすばやく変換できます。

この場合の小さな注意点は次のとおりです。集約が進む列の数が増えると、PIVOTとCASE WHENの実行時間の差は小さくなり、特定の時点で測定エラーの範囲内になります。

すべての実験は、 SQL Server 2012 SP1（11.00.3128）で実施されました。