【PostgreSQL】縦持ちデータを横持ち変換し前年同月との比較をする

投稿日：2020年4月15日更新日：2020年5月19日

Contents

1 目的
2 元データ
3 横持にするＳＱＬ
4 結果
5 まとめ

目的

システムから取り出したデータは正規化されており縦持ちのデータ構造が多いですが、口座の残高やカードの支払い金額など、前年の実績と比較するような場合には横持のほうが見やすい。そこで、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 行)

まとめ

システムから取り出した縦持ちデータを前年や前の月と比較分析する際はＳＱＬで縦持ちにするとわかりやすい。

執筆者：SEBONE