目的
システムから取り出したデータは正規化されており縦持ちのデータ構造が多いですが、口座の残高やカードの支払い金額など、前年の実績と比較するような場合には横持のほうが見やすい。そこで、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を実行します。
select
年 as 年
,max(１月) as １月
,max(２月) as ２月
,max(３月) as ３月
,max(４月) as ４月
,max(５月) as ５月
,max(６月) as ６月
,max(７月) as ７月
,max(８月) as ８月
,max(９月) as ９月
,max(１０月) as １０月
,max(１１月) as １１月
,max(１２月) as １２月
from
(
select
年
,case 月
when 1 then 金額
else null
end as １月
,case 月
when 2 then 金額
else null
end as ２月
,case 月
when 3 then 金額
else null
end as ３月
,case 月
when 4 then 金額
else null
end as ４月
,case 月
when 5 then 金額
else null
end as ５月
,case 月
when 6 then 金額
else null
end as ６月
,case 月
when 7 then 金額
else null
end as ７月
,case 月
when 8 then 金額
else null
end as ８月
,case 月
when 9 then 金額
else null
end as ９月
,case 月
when 10 then 金額
else null
end as １０月
,case 月
when 11 then 金額
else null
end as １１月
,case 月
when 12 then 金額
else null
end as １２月
from test_kingaku
) as t0
group by 年
ポイントしては以下になります。
- １月から１２月までのカラムを作り、各カラムでは該当する月のレコードの数字だけを格納しそれ以外はＮＵＬＬを入れる（サブクエリ部）
- 年でgroup byし、各月のmaxの値を取り出す。この操作によりＮＵＬＬのレコードが除外される。
結果
以下のような結果が得られます。これだと前年の同月との比較が一目瞭然です。
年 | １月 | ２月 | ３月 | ４月 | ５月 | ６月 | ７月 | ８月 | ９月 | １０月 | １１月 | １２月
------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------
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 行)
まとめ
システムから取り出した縦持ちデータを前年や前の月と比較分析する際はＳＱＬで縦持ちにするとわかりやすい。