入出金情報が入ったテーブルから月締めの集計ビューを作る方法を試します。
元データ
以下のデータを対象にします。
select * from account;
#結果
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 行)
一次処理
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 行)
二次処理
一次処理で得られた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 行)
月末の値だけ取り出すことができました。