いろいろやってみよう

完璧を目指すより、まず終わらせろ

【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」内の処理は、必要に応じて行ってください。