PostgreSQL

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

投稿日:2020年4月15日 更新日:

目的

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

-PostgreSQL
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

【PostgreSQL】グループ内での順位付けを行うSQL

Contents1 目的2 テストデータ3 順位付けするSQL4 特定の順位だけを抜き出すSQL 目的 一つのテーブルに複数のグループがごちゃ混ぜに入っている場合に、グループごとに順位付けを行うSQL …

PostgreSQL 累積値を計算するSQL

Contents1 目的2 Step1 : テーブル用意3 Step2 : 累積値を計算するSQL 目的 ある値の累積値をSQLで計算する。 Step1 : テーブル用意 以下のようなテーブルを用意し …

VSCodeからPostgreSQLへ接続してSQL発行まで

Contents1 目的2 環境3 Step1 : プラグインのインストール4 Step2 : データベースとの接続5 Step3 : SQLの発行6 補足 目的 VSCodeが優秀なので、VSCod …

【PostgreSQL】前後の行を取得できるLAG LEAD関数の使い方

Contents1 概要2 環境3 準備4 LAG(), LEAD()の使い方4.1 SQL例4.2 結果4.3 SQL例4.4 結果5 便利な例5.1 SQL例5.2 結果 概要 PostgreSQ …

PostgreSQL 入出金テーブルから月締め集計ビュー作成

入出金情報が入ったテーブルから月締めの集計ビューを作る方法を試します。 Contents1 元データ2 一次処理3 二次処理 元データ 以下のデータを対象にします。 select * from acc …

言語切り替え

カテゴリー