SQL Serverでクロス集計

業務系のシステムを構築しているときに、客先から製品別の売上を月別、四半期別、年別などに集計したいという要望が寄せられる。はっきり言ってこういった帳票を作るのはメンドクサイ．．．

ぱっと思いつく方法としては、製品IDをキーにした1月から12月までのカラムを持ったテーブルを作り、製品IDをキーにして各月のカラムに加算していく力技の方法。激しく萎える．．．

っで、この集計データをSQL文一発で組み立てる方法はないものかと試行錯誤した結果できあがったクエリーがこれ。いずれもSQL Server 2005のサンプルデータベースであるAdventureWorksにある2003年の注文データから検索している。

まず、第一のクエリーは製品別／月別に「売上個数」を集計するもの。

USE AdventureWorks
-- AdventureWorksデータベースから２００３年の製品別／月別売上個数をクロス集計
SELECT d.ProductID, p.Name,
-- 以下の集計を１月から１２月まで集計繰り返し
sum(case when MONTH(h.OrderDate) = 1 then d.OrderQty else 0 end) as '１月',
sum(case when MONTH(h.OrderDate) = 2 then d.OrderQty else 0 end) as '２月',
sum(case when MONTH(h.OrderDate) = 3 then d.OrderQty else 0 end) as '３月',
sum(case when MONTH(h.OrderDate) = 4 then d.OrderQty else 0 end) as '４月',
sum(case when MONTH(h.OrderDate) = 5 then d.OrderQty else 0 end) as '５月',
sum(case when MONTH(h.OrderDate) = 6 then d.OrderQty else 0 end) as '６月',
sum(case when MONTH(h.OrderDate) = 7 then d.OrderQty else 0 end) as '７月',
sum(case when MONTH(h.OrderDate) = 8 then d.OrderQty else 0 end) as '８月',
sum(case when MONTH(h.OrderDate) = 9 then d.OrderQty else 0 end) as '９月',
sum(case when MONTH(h.OrderDate) = 10 then d.OrderQty else 0 end) as '１０月',
sum(case when MONTH(h.OrderDate) = 11 then d.OrderQty else 0 end) as '１１月',
sum(case when MONTH(h.OrderDate) = 12 then d.OrderQty else 0 end) as '１２月',
-- 合計
sum(d.OrderQty) as '合計'
FROM [AdventureWorks].[Sales].[SalesOrderDetail]  as d
,[AdventureWorks].[Sales].[SalesOrderHeader] as h
,[AdventureWorks].[Production].[Product] as p
where YEAR(h.OrderDate) = '2003' and
d.SalesOrderID = h.SalesOrderID and
d.ProductID = p.ProductID
group by d.ProductID, p.Name
order by d.ProductID

第二のクエリーは製品別／月別に「注文件数」を集計するもの。

USE AdventureWorks
-- AdventureWorksデータベースから２００３年の製品別／月別注文件数をクロス集計
SELECT d.ProductID, p.Name,
-- 以下の集計を１月から１２月まで集計繰り返し
count(case when MONTH(h.OrderDate) = 1 then d.OrderQty else null end) as '１月',
count(case when MONTH(h.OrderDate) = 2 then d.OrderQty else null end) as '２月',
count(case when MONTH(h.OrderDate) = 3 then d.OrderQty else null end) as '３月',
count(case when MONTH(h.OrderDate) = 4 then d.OrderQty else null end) as '４月',
count(case when MONTH(h.OrderDate) = 5 then d.OrderQty else null end) as '５月',
count(case when MONTH(h.OrderDate) = 6 then d.OrderQty else null end) as '６月',
count(case when MONTH(h.OrderDate) = 7 then d.OrderQty else null end) as '７月',
count(case when MONTH(h.OrderDate) = 8 then d.OrderQty else null end) as '８月',
count(case when MONTH(h.OrderDate) = 9 then d.OrderQty else null end) as '９月',
count(case when MONTH(h.OrderDate) = 10 then d.OrderQty else null end) as '１０月',
count(case when MONTH(h.OrderDate) = 11 then d.OrderQty else null end) as '１１月',
count(case when MONTH(h.OrderDate) = 12 then d.OrderQty else null end) as '１２月',
-- 合計
count(d.OrderQty) as '合計'

FROM [AdventureWorks].[Sales].[SalesOrderDetail]  as d
,[AdventureWorks].[Sales].[SalesOrderHeader] as h
,[AdventureWorks].[Production].[Product] as p

where YEAR(h.OrderDate) = '2003' and
d.SalesOrderID = h.SalesOrderID and
d.ProductID = p.ProductID
group by d.ProductID, p.Name
order by d.ProductID

注文件数を集計する場合のキモは、null値はカウントされないというcount関数の特性を利用して売上がない月の注文件数ゼロにしているところ。集計結果のリザルトセットにnull値が入っていると何かと扱いづらいので。

実際のリザルトセットはこんな感じ。