Loading [MathJax]/extensions/tex2jax.js

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

by · 公開済み · 更新済み

目的

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

元データ

以下のようなデータを例に考えます。このような縦持ちデータの場合、やはり前年度と比較するのが大変です。特にレコードが増えるにつれて、比較が困難になります。

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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を実行します。

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
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の値を取り出す。この操作によりＮＵＬＬのレコードが除外される。

結果

以下のような結果が得られます。これだと前年の同月との比較が一目瞭然です。

01
02
03
04
05
06
  年  |  １月  |  ２月  |  ３月  |  ４月  |  ５月  |  ６月  |  ７月  |  ８月  |  ９月  | １０月 | １１月 | １２月
------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------
 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 行)

まとめ

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

タグ:

おすすめ

コメントを残す

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