縦から横に展開するSQL

boxcodeadmin

2021年1月1日

売上データについて特定の商品を日別に横に並べて見比べたいということございませんか？

今回は縦展開になされているデータを横展開に置き換えるSQLをご紹介いたいします。

まずは以下のデータを使います。

以下はとあるボクシングショップの2021年年明けセールの日別のカテゴリ別売上数量の集計データです。

orderdate	categoryid	categoryname	guantity
2021/01/01	1	Gloves	70
2021/01/01	2	Sandbag and Mitts	63
2021/01/01	9	Other	10
2021/01/02	1	Gloves	36
2021/01/02	9	Other	5
2021/01/03	1	Gloves	57
2021/01/03	2	Sandbag and Mitts	50
2021/01/03	9	Other	17
2021/01/04	1	Gloves	56
2021/01/04	9	Other	18
2021/01/05	2	Sandbag and Mitts	119
2021/01/05	9	Other	17

こちらのデータを以下のように日別に横展開させたく思っています。

orderdate	Gloves	Sandbag and Mitts	Other
2021/01/01	70	63	10
2021/01/02	36	0	5
2021/01/03	57	50	17
2021/01/04	56	0	18
2021/01/05	0	119	136

早速取り掛かります。

環境はPostgreSQL 9.6.2です。

データ準備は以下の通りです。

--テーブル作成
create table dailysales (
        orderdate date
       ,categoryid varchar(1)
       ,categoryname varchar(50)
       ,quantity numeric);

--データセット 
insert into dailysales 
 (orderdate, categoryid, categoryname, quantity)
 values
 ('2021/01/01','1','Gloves',70),
 ('2021/01/01','2','Sandbag and Mitts',63),
 ('2021/01/01','9','Other',10),
 ('2021/01/02','1','Gloves',36),
 ('2021/01/02','9','Other',5),
 ('2021/01/03','1','Gloves',57),
 ('2021/01/03','2','Sandbag and Mitts',50),
 ('2021/01/03','9','Other',17),
 ('2021/01/04','1','Gloves',56),
 ('2021/01/04','9','Other',18),
 ('2021/01/05','2','Sandbag and Mitts',119),
 ('2021/01/05','9','Other',17);

縦から横に展開するためにはcase式を使います。

select orderdate
       ,max(case categoryid when '1' then quantity else 0 end) as Gloves
       ,max(case categoryid when '2' then quantity else 0 end) as SandbagAndMitts
       ,max(case categoryid when '9' then quantity else 0 end) as Other
       ,sum(quantity) as amount 
   from dailysales
  group by orderdate
  order by orderdate

上記SQLを実行すると以下のような結果になります。

なんとなく合計数も加えてみました。