【Athena】開始・終了日時から、そこに含まれる時間帯を全て取得したい
結論
以下のクエリで出来ます。
WITH temp AS ( SELECT user_id , DATE_PARSE(start_datetime, '%Y-%m-%d %H:%i:%S') AS start_datetime , DATE_PARSE(end_datetime, '%Y-%m-%d %H:%i:%S') AS end_datetime FROM tb_01 ) SELECT user_id , start_datetime , end_datetime , get_time FROM temp CROSS JOIN UNNEST(sequence(DATE_TRUNC('HOUR', start_datetime), DATE_TRUNC('HOUR', end_datetime), INTERVAL '1' HOUR)) AS t(get_time)
説明
開始時刻と終了日時から、そこに含まれる時間帯を全て取得したい
以下のようなテーブルが存在していたとします。
これは、各ユーザー毎に処理の開始・終了日時を記録したログテーブルみたいなものだと考えてください。
user_id | start_datetime | end_datetime |
A | 2023-04-15 13:30:00 | 2023-04-15 17:27:00 |
B | 2023-04-20 22:10:00 | 2023-04-20 23:40:00 |
このテーブルを元に、どの時間帯で処理をしているユーザーが多いかを集計したいとします。
ただ、start_datetime か end_datetime を基準に user_id をカウントしてもやりたい事が出来ないかもしれません。
例えば、user A は、13:30~17:27 迄4時間ほど処理をしていますが、13時or17時しかカウントできなくなります。
以下のようにすれば、開始・終了日時に含まれる時間帯を全て取得できる。
その場合、冒頭で示したクエリを実行すれば、以下の結果を得られます。
user_id | start_datetime | end_datetime | get_time |
A | 2023-04-15 13:30:00 | 2023-04-15 17:27:00 | 2023-04-15 13:00:00 |
A | 2023-04-15 13:30:00 | 2023-04-15 17:27:00 | 2023-04-15 14:00:00 |
A | 2023-04-15 13:30:00 | 2023-04-15 17:27:00 | 2023-04-15 15:00:00 |
A | 2023-04-15 13:30:00 | 2023-04-15 17:27:00 | 2023-04-15 16:00:00 |
A | 2023-04-15 13:30:00 | 2023-04-15 17:27:00 | 2023-04-15 17:00:00 |
B | 2023-04-20 22:10:00 | 2023-04-20 23:40:00 | 2023-04-20 22:00:00 |
B | 2023-04-20 22:10:00 | 2023-04-20 23:40:00 | 2023-04-20 23:00:00 |
get_time 部分を基準にカウントすれば、
開始・終了日時に含まれる全ての時間帯を基準として集計できます。
WITH句「temp」内の処理は、必要に応じて行ってください。