Contents
目的
システムから取り出したデータは正規化されており縦持ちのデータ構造が多いですが、口座の残高やカードの支払い金額など、前年の実績と比較するような場合には横持のほうが見やすい。そこで、PostgreSQLで縦持ちのデータを横持にしてみる。
元データ
以下のようなデータを例に考えます。このような縦持ちデータの場合、やはり前年度と比較するのが大変です。特にレコードが増えるにつれて、比較が困難になります。
postgres=# select * from test_kingaku; 年 | 月 | 金額 ------+----+-------- 2017 | 1 | 222400 2017 | 2 | 237900 2017 | 3 | 244100 2017 | 4 | 208400 2017 | 5 | 237100 2017 | 6 | 208700 2017 | 7 | 210600 2017 | 8 | 291300 2017 | 9 | 256200 2017 | 10 | 232200 2017 | 11 | 240000 2017 | 12 | 204000 2018 | 1 | 241500 2018 | 2 | 226300 2018 | 3 | 211900 2018 | 4 | 241000 2018 | 5 | 264800 2018 | 6 | 207800 2018 | 7 | 241700 2018 | 8 | 278200 2018 | 9 | 291900 2018 | 10 | 241700 2018 | 11 | 204700 2018 | 12 | 206500 2019 | 1 | 298500 2019 | 2 | 254900 2019 | 3 | 285400
横持にするSQL
次のようなSQLを実行します。
select 年 as 年 ,max(1月) as 1月 ,max(2月) as 2月 ,max(3月) as 3月 ,max(4月) as 4月 ,max(5月) as 5月 ,max(6月) as 6月 ,max(7月) as 7月 ,max(8月) as 8月 ,max(9月) as 9月 ,max(10月) as 10月 ,max(11月) as 11月 ,max(12月) as 12月 from ( select 年 ,case 月 when 1 then 金額 else null end as 1月 ,case 月 when 2 then 金額 else null end as 2月 ,case 月 when 3 then 金額 else null end as 3月 ,case 月 when 4 then 金額 else null end as 4月 ,case 月 when 5 then 金額 else null end as 5月 ,case 月 when 6 then 金額 else null end as 6月 ,case 月 when 7 then 金額 else null end as 7月 ,case 月 when 8 then 金額 else null end as 8月 ,case 月 when 9 then 金額 else null end as 9月 ,case 月 when 10 then 金額 else null end as 10月 ,case 月 when 11 then 金額 else null end as 11月 ,case 月 when 12 then 金額 else null end as 12月 from test_kingaku ) as t0 group by 年
ポイントしては以下になります。
- 1月から12月までのカラムを作り、各カラムでは該当する月のレコードの数字だけを格納しそれ以外はNULLを入れる(サブクエリ部)
- 年でgroup byし、各月のmaxの値を取り出す。この操作によりNULLのレコードが除外される。
結果
以下のような結果が得られます。これだと前年の同月との比較が一目瞭然です。
年 | 1月 | 2月 | 3月 | 4月 | 5月 | 6月 | 7月 | 8月 | 9月 | 10月 | 11月 | 12月 ------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+-------- 2017 | 222400 | 237900 | 244100 | 208400 | 237100 | 208700 | 210600 | 291300 | 256200 | 232200 | 240000 | 204000 2018 | 241500 | 226300 | 211900 | 241000 | 264800 | 207800 | 241700 | 278200 | 291900 | 241700 | 204700 | 206500 2019 | 298500 | 254900 | 285400 | 214500 | 269700 | 247200 | 282800 | 270000 | 272400 | 226600 | 214600 | 236100 (3 行)
まとめ
システムから取り出した縦持ちデータを前年や前の月と比較分析する際はSQLで縦持ちにするとわかりやすい。