PostgreSQL

横持ちデータを縦持ちへ変換するSQL

投稿日:

目的

BIなどでグラフを書く場合、データが横持になっているとうまくグラフ化できない、といったケースがあります。ということでSQLでデータを横持から縦持ちに変換する方法について試します。

環境

PostgreSQL 11.5

使用データ

以下のように、年だけ縦持ちで月が横持ちになっている残高データを例にします。各月のフィールドの中には金額データが格納されています。

postgres=# select * from test_kingaku_yoko;

  年  |  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

上記のようなデータ構造の場合、BIでグラフ化しようとした場合に、横軸の選択に各月がバラバラに表れてしまうため、一度にすべての月のデータをグラフ化するのが難しかったりします。

縦持ちにするSQL

次のように各月のカラム毎にデータを抽出し、それらをunion allでつなげることでデータを縦持ちにすることができます。

select
	年
	,1 as 月
	,1月 as 金額
from
	test_kingaku_yoko
union all
select
	年
	,2 as 月
	,2月 as 金額
from
	test_kingaku_yoko
union all
select
	年
	,3 as 月
	,3月 as 金額
from
	test_kingaku_yoko
union all
select
	年
	,4 as 月
	,4月 as 金額
from
	test_kingaku_yoko
union all
select
	年
	,5 as 月
	,5月 as 金額
from
	test_kingaku_yoko
union all
select
	年
	,6 as 月
	,6月 as 金額
from
	test_kingaku_yoko
union all
select
	年
	,7 as 月
	,7月 as 金額
from
	test_kingaku_yoko
union all
select
	年
	,8 as 月
	,8月 as 金額
from
	test_kingaku_yoko
union all
select
	年
	,9 as 月
	,9月 as 金額
from
	test_kingaku_yoko
union all
select
	年
	,10 as 月
	,10月 as 金額
from
	test_kingaku_yoko
union all
select
	年
	,11 as 月
	,11月 as 金額
from
	test_kingaku_yoko
union all
select
	年
	,12 as 月
	,12月 as 金額
from
	test_kingaku_yoko
  年  | 月 |  金額
------+----+--------
 2017 |  1 | 222400
 2018 |  1 | 241500
 2019 |  1 | 298500
 2017 |  2 | 237900
 2018 |  2 | 226300
 2019 |  2 | 254900
 2017 |  3 | 244100
 2018 |  3 | 211900
 2019 |  3 | 285400
 2017 |  4 | 208400
 2018 |  4 | 241000
 2019 |  4 | 214500
 2017 |  5 | 237100
 2018 |  5 | 264800
 2019 |  5 | 269700
 2017 |  6 | 208700
 2018 |  6 | 207800
 2019 |  6 | 247200
 2017 |  7 | 210600
 2018 |  7 | 241700
 2019 |  7 | 282800
 2017 |  8 | 291300
 2018 |  8 | 278200
 2019 |  8 | 270000
 2017 |  9 | 256200
 2018 |  9 | 291900
 2019 |  9 | 272400
 2017 | 10 | 232200
 2018 | 10 | 241700
 2019 | 10 | 226600
 2017 | 11 | 240000
 2018 | 11 | 204700
 2019 | 11 | 214600
 2017 | 12 | 204000
 2018 | 12 | 206500
 2019 | 12 | 236100
(36 行)

このような縦持ちの状態にするとBIでグラフ化する際、「月」という一列を横軸に持ってくることで、分かりやすいグラフを書くことができました。

-PostgreSQL

執筆者:


comment

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

関連記事

PostgreSQL カンマ付き文字列金額データを数値変換

BIツールなどで金額データを扱いたい場合に、金額データがカンマ区切りの文字列データの場合があります。 例:306,327 このような文字列データを数値に変換します。 SQL 以下のようなデータを持つテ …

PostgreSQL 累積値を計算するSQL

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

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

Contents1 目的2 元データ3 横持にするSQL4 結果5 まとめ 目的 システムから取り出したデータは正規化されており縦持ちのデータ構造が多いですが、口座の残高やカードの支払い金額など、前年 …

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

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

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

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

言語切り替え

カテゴリー