開発備忘録＆ふと思ったこと

今日はSQL SERVERのPivotに付いて使い方例を開発備忘録しておきます。

PivotはEXCELで良く集計に使われる関数です。
一般的にデータは正規化されて項目重複のない状態で保存されます。
例えば、売り上げは、日時、店舗CD、商品CD、個数、他と、店舗マスタ、商品マスターのような状態で保存されます。
人が見やすいデータは正規化されたデータと異なり、
例えば、同じ売り上げでも、日付、店舗名、商品名、個数、合計価格となります。

上の販売データの羅列を見せられても、人は売上状況を把握しづらいものです。
もっと言うなら、「森全体　⇒　森の一部　⇒　木　⇒　幹　⇒　枝　⇒　葉」のようにマクロなデータからミクロに向かう方が把握分析しやすいと言われています。

Pivotは個々の葉のレコードを寄り集めて、枝や幹、木、森の一部のような集計したデータにしてくれる関数です。

下のSQL例は、日時、商品CD、販売額のデータを

日付を行に商品を列に金額を合計した表をPivot関数を利用して返すようにしたものです。

また、PivotのIN句の中ではサブクエリが使用できないようなので、動的にSQLを作ってexecSQLで実行しています。
変数テーブルを使用しているため、execSQLでちょっと面倒になっていますが、本来はこんなことにならないので。。。勘弁してください。

declare @tbl1 table(s_date datetime, goods_cd nvarchar(9), price int)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '001', 60)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '010', 40)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '020', 20)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '030', 100)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '001', 30)

insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '001', 100)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '010', 150)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '020', 180)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '020', 200)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '030', 70)
 
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '001', 80)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '010', 120)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '030', 60)

select * from @tbl1

/*pivot*/
select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in ([001], [010], [020], [030])) T1 

--IN句を動的に作ろうとするとエラーになる
--in ([001], [010], [020], [030])が動的に作れない。。。
--select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in (select distinct goods_cd from @tbl1)) as T 


--ので、そこで変数内にSQLを書き、実行する。
--IN句を動的に作り、@goods_listに格納する
declare @goods_list nvarchar(128)
select @goods_list=replace(REPLACE((SELECT distinct goods_cd AS [kugiri] 
                   from	@tbl1
                   for xml path ('')), '</kugiri>', '],'), '<kugiri>','[') 
set @goods_list=substring(@goods_list,0,len(@goods_list))

--実行SQLクエリ作成
--execでは、変数テーブルが読めない（アクセスできない）ため、ここにも変数テーブルを追加（通常のテーブルならこんな必要はないです）
declare @sql nvarchar(2048)
set @sql =
    '
	declare @tbl1 table(s_date datetime, goods_cd nvarchar(9), price int)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''001'', 60)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''010'', 40)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''020'', 20)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''030'', 100)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''001'', 30)

	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''001'', 100)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''010'', 150)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''020'', 180)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''020'', 200)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''030'', 70)
 
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''001'', 80)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''010'', 120)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''030'', 60)

	select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in (' + @goods_list + ')) T1
	'
 
-- SQLクエリの実行
exec sp_executesql @sql;

上のデータに店舗を入れた場合は、以下のようになります。

declare @tbl1 table(s_date datetime, tnp_cd nvarchar(5), goods_cd nvarchar(9), price int)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '001', 60)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '001', 30)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '010', 40)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '020', 20)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '020', 20)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '001', '030', 100)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '002', '001', 30)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '002', '001', 30)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()-1, '002', '020', 20)
 
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE(), '001', '001', 100)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE(), '001', '010', 150)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE(), '001', '020', 180)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE(), '001', '020', 200)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE(), '001', '030', 70)
  
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()+1, '001', '001', 80)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()+1, '001', '010', 120)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()+1, '001', '030', 60)
insert into @tbl1 (s_date,tnp_cd,goods_cd,price) values (GETDATE()+1, '001', '020', 20)
 
select * from @tbl1
 
/*pivot*/
select * from (select convert(VARCHAR, s_date, 111 ) as s_date, tnp_cd, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in ([001], [010], [020], [030])) T1 
 

以上、今日はSQL SERVERのPivotに付いて使い方例を開発備忘録でした。