postgresで履歴テーブルから1時間毎の集計をSQLで取得する

 metabaseでpostgresデータを可視化するようにした所、履歴データが格納されているテーブルから1時間毎の集計を取りたいって事で、SQL1発で出来るんかいなと試行錯誤。

 今回の履歴テーブルはtimestamp型で開始時間と終了時間がセットされていて、誰が何をいつからいつまで実施していたかが記録されている、ような形になっています。これを1時間単位でその時間帯にどのくらい実施していたかをサマリたいという内容です。

具体的にはこんな感じでデータがあります。

selet * from xx_history ;

id, start_time, end_time, user_id, task_id
10, 2020-07-27 16:15:23, 2020-07-27 18:17:43, 432, 2
11, 2020-07-27 18:43:54, 2020-07-27 19:02:04, 154, 4
12, 2020-07-27 19:14:52, 2020-07-27 19:45:21, 432, 5
13, 2020-07-27 19:02:15, 2020-07-27 19:42:53, 154, 4

 これを下記のように1時間毎に時間を合計するような形で抽出したいという事になります。

時間範囲(XX時台), 合計時間
2020-07-27 16:00, 00:44:37
2020-07-27 17:00, 01:00:00
2020-07-27 18:00, 00:33:49
2020-07-27 19:00, 01:13:11

 履歴テーブルの開始時間と終了時間に対して、その時間帯が該当するのは、下記4つのパターンがあり、それぞれ時間の取り方が変わってくるのも面倒なトコロです。

・開始時間と終了時間がその時間範囲内
 → 開始時間から終了時間までの時間
・開始時間が時間範囲より過去だが、終了時間が時間範囲内
 → その時間範囲の開始時刻から終了時間までの時間
・開始時間が時間範囲内だが、終了時間は時間範囲より未来
 → 開始時間からその時間範囲の終了時刻までの時間
・開始時間は時間範囲より過去で、終了時間が時間範囲より未来
 → 1時間固定

 調べているとコチラを発見、postgresにはgenerate_seriesという便利なものがあるようです。

 generate_seriesで1時間毎のレコードを一定期間分生成出来るようなので、履歴テーブルとJoinしてSQLを作ります。

 whereとCaseの条件は一緒なので、Whereの方はもっとコストをかけない条件に見直しした方がよいですね。
 実際はもっと複雑な形になるのですが、とりあえずSQL1発で出来そうな感じです。