【Postgresql】 重複排除と先頭取得の併用(distinct on & lead)

サンプルテーブルの構造です

ch_dateはレート(rate)の変更を行った日付ですが、

レートの適用期間を算出したい(yyyy-mm-dd〜yyyy-mm-dd　0.001%)

a=# select * from rate_history order by ch_date;

       ch_date       |   rate    

---------------------+-----------

 2021-02-15 00:00:00 | 0.0008000

 2021-03-01 00:00:00 | 0.0010000

 2021-03-02 00:00:00 | 0.0010000

 2021-03-03 00:00:00 | 0.0010000

 2021-03-04 00:00:00 | 0.0020000

 2021-03-05 00:00:00 | 0.0020000

 2021-03-06 00:00:00 | 0.0020000

 2021-03-07 00:00:00 | 0.0030000

 2021-03-08 00:00:00 | 0.0030000

 2021-03-09 00:00:00 | 0.0040000

(10 rows)

変更日付でソート＆重複除外します

# select ch_date,rate from

(select distinct on (rate) ch_date,rate from (select * from rate_history order by ch_date)a)c;

       ch_date       |   rate    

---------------------+-----------

 2021-02-15 00:00:00 | 0.0008000

 2021-03-01 00:00:00 | 0.0010000

 2021-03-04 00:00:00 | 0.0020000

 2021-03-07 00:00:00 | 0.0030000

 2021-03-09 00:00:00 | 0.0040000

(5 rows)

期間の終了日を次の変更日から１日前を計算して取得します

この行方向の移動にlead関数を利用します

# select 

ch_date,

coalesce(

 lead(ch_date,1,null) over (order by ch_date) + cast('-1 days' as interval) ,

 ch_date) as end_date,

rate 

from

(

select distinct on (rate) ch_date,rate from 

(select * from rate_history order by ch_date)a

)c;

       ch_date       |      end_date       |   rate    

---------------------+---------------------+-----------

 2021-02-15 00:00:00 | 2021-02-28 00:00:00 | 0.0008000

 2021-03-01 00:00:00 | 2021-03-03 00:00:00 | 0.0010000

 2021-03-04 00:00:00 | 2021-03-06 00:00:00 | 0.0020000

 2021-03-07 00:00:00 | 2021-03-08 00:00:00 | 0.0030000

 2021-03-09 00:00:00 | 2021-03-09 00:00:00 | 0.0040000

(5 rows)