Prestoで横持ちの期間データを縦持ちに変換する

はじめに

SQL Query EngineであるPrestoで、横持ちの期間データを縦持ちにする方法を書いてます。
Prestoが使われているサービス（Amazon Athenaとか）であれば、基本的には適用できると思いますし、もしかするとPostgresとかMySQLのRDBでも使えるかもしれません。未確認ですけど。

前提

元になっているのは下記のような契約データとします。

テーブル名：contract_table

id	name	start_date	end_date
1	契約A	2020-04-26	2020-09-03
2	契約B	2021-05-19	2022-08-15

契約期間を集計するために、下記のような縦持ちの形にしたい。

id	name	contract_month
1	契約A	2020-04
1	契約A	2020-05
1	契約A	2020-06
1	契約A	2020-07
1	契約A	2020-08
1	契約A	2020-09
2	契約B	2021-05
2	契約B	2021-06
2	契約B	2021-07
2	契約B	2021-08

まず答え

SELECT 
id,
name,
date_format(contract_month, '%Y-%m')
FROM contract_table CROSS JOIN UNNEST(
	sequence(
		date_trunc('month',start_date),
		date_trunc('month',end_date),
		interval '1' month
	)
) AS t(contract_month)

解説

SQLのロジックとしては以下のような順になっています

1. start_dateとend_dateをもとにして、対象月の配列を作成
2. 1の配列を行に展開
3. 日付をフォーマット

start_dateとend_dateをもとにして、対象月の配列を作成

これは下記の箇所になります。

sequence(
	date_trunc('month',start_date),
	date_trunc('month',end_date),
	interval '1' month
)

sequenceで返却されるのは、timestampの配列で、たとえば契約Aだと以下のような形で返却されます。

[
	2020-04-26 00:00:00.000,
	2020-05-26 00:00:00.000,
	2020-06-26 00:00:00.000,
	2020-07-26 00:00:00.000,
	2020-08-26 00:00:00.000
]

ただ、そのまま使うと、日付によっては上記のように最終月分が抜け落ちてしまうので、date_truncで月初にまるめて使っています。また、今回はintervalを1 monthで指定していますが、これを15 daysといったふうに、任意のintervalにすることも可能です。

1の配列を行に展開

unnestで配列を行に変換します。

contract_table CROSS JOIN UNNEST(
	sequence(
		date_trunc('month',start_date),
		date_trunc('month',end_date),
		interval '1' month
	)
) AS t(contract_month)

前STEPで作った配列をunnest関数の引数に渡すことで、自動的に行に展開されてテーブルが作成されます。 t(contract_month) がそれにあたります。
その後、CROSS JOINで元のテーブルとjoinさせることで、１つのレコードが配列分に複製されることになります。

日付をフォーマット

これは大した処理ではないです。
今回は下記にてフォーマットしていますが、任意の形で整形してもらえればOKです。

date_format(contract_month, '%Y-%m')

おわりに

なんでもかんでもSQLで処理しすぎるとメンテナンス性が落ちてしまうので注意ではありますが、これぐらいならさっくりSQLで処理してもよいのではないでしょうか。