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

by SEBONE · 公開済み 2019年12月4日 · 更新済み 2020年5月19日

入出金情報が入ったテーブルから月締めの集計ビューを作る方法を試します。

Table of Contents

元データ

以下のデータを対象にします。

select * from account;
 
#&#32080;&#26524;
 
   dates    | in_amount | out_amount | balance
------------+-----------+------------+---------
 2019-10-01 |       100 |            |   10100
 2019-10-02 |           |       1000 |    9100
 2019-10-02 |      3000 |            |   12100
 2019-10-02 |     10000 |            |   22100
 2019-10-20 |           |       1000 |   21100
 2019-10-24 |      2000 |            |   23100
 2019-11-03 |           |       3000 |   20100
 2019-11-06 |           |       4000 |   16100
 2019-11-15 |     30000 |            |   46100
 2019-11-15 |           |       2000 |   44100
 2019-11-25 |           |        100 |   44000
 2019-11-26 |      3000 |            |   47000
 2019-11-27 |           |      25000 |   22000
 2019-11-28 |      1000 |            |   23000
(14 &#34892;)

一次処理

row_number() over()と年月を抽出した行を追加します。

select
    row_number() over()
    ,to_char(dates,'yyyymm') as year_month
    ,*
from
    account

結果

row_number | year_month |   dates    | in_amount | out_amount | balance
------------+------------+------------+-----------+------------+---------
          1 | 201910     | 2019-10-01 |       100 |            |   10100
          2 | 201910     | 2019-10-02 |           |       1000 |    9100
          3 | 201910     | 2019-10-02 |      3000 |            |   12100
          4 | 201910     | 2019-10-02 |     10000 |            |   22100
          5 | 201910     | 2019-10-20 |           |       1000 |   21100
          6 | 201910     | 2019-10-24 |      2000 |            |   23100
          7 | 201911     | 2019-11-03 |           |       3000 |   20100
          8 | 201911     | 2019-11-06 |           |       4000 |   16100
          9 | 201911     | 2019-11-15 |     30000 |            |   46100
         10 | 201911     | 2019-11-15 |           |       2000 |   44100
         11 | 201911     | 2019-11-25 |           |        100 |   44000
         12 | 201911     | 2019-11-26 |      3000 |            |   47000
         13 | 201911     | 2019-11-27 |           |      25000 |   22000
         14 | 201911     | 2019-11-28 |      1000 |            |   23000
(14 &#34892;)
 
   

二次処理

一次処理で得られたyear_monthごとにgroup_byし、row_numberが最大のレコードだけ残すクエリと一次処理の結果を結合します。

SQLとしては以下の通り。

select
    t1.year_month
    ,dates
    ,balance
from
    (
    select
        max(row_number) as row_number
        ,year_month
    from
        (
        select
            row_number() over() as row_number
            ,to_char(dates,'yyyymm') as year_month
            ,*
        from
            account
        ) as t0
    group by year_month
    ) as t1
left join
    (
    select
    row_number() over() as row_number
    ,to_char(dates,'yyyymm') as year_month
    ,*
from
    account
    ) as t2
using(row_number)

結果

year_month |   dates    | balance
------------+------------+---------
 201910     | 2019-10-24 |   23100
 201911     | 2019-11-28 |   23000
(2 &#34892;)

月末の値だけ取り出すことができました。

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

元データ

一次処理

二次処理

おすすめ

コメントを残すコメントをキャンセル

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

元データ

一次処理

二次処理

おすすめ

WindowsにPostgreSQLをインストールしてサーバー化

PostgreSQL 同一日内の入出金データのグラフ化

PostgreSQL 累積値を計算するSQL

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル