metabaseでpostgresデータを可視化するようにした所、履歴データが格納されているテーブルから１時間毎の集計を取りたいって事で、SQL1発で出来るんかいなと試行錯誤。
今回の履歴テーブルはtimestamp型で開始時間と終了時間がセットされていて、誰が何をいつからいつまで実施していたかが記録されている、ような形になっています。これを１時間単位でその時間帯にどのくらい実施していたかをサマリたいという内容です。
具体的にはこんな感じでデータがあります。
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
これを下記のように１時間毎に時間を合計するような形で抽出したいという事になります。
時間範囲（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
履歴テーブルの開始時間と終了時間に対して、その時間帯が該当するのは、下記４つのパターンがあり、それぞれ時間の取り方が変わってくるのも面倒なトコロです。
・開始時間と終了時間がその時間範囲内
→ 開始時間から終了時間までの時間
・開始時間が時間範囲より過去だが、終了時間が時間範囲内
→ その時間範囲の開始時刻から終了時間までの時間
・開始時間が時間範囲内だが、終了時間は時間範囲より未来
→ 開始時間からその時間範囲の終了時刻までの時間
・開始時間は時間範囲より過去で、終了時間が時間範囲より未来
→ 1時間固定
調べているとコチラを発見、postgresにはgenerate_seriesという便利なものがあるようです。
generate_seriesで１時間毎のレコードを一定期間分生成出来るようなので、履歴テーブルとJoinしてSQLを作ります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
select time, sum(case when xx_history.start_time >= time and xx_history.end_time <= time + interval '1 hours' then xx_history.end_time - xx_history.start_time when xx_history.start_time < time and xx_history.end_time >= time and xx_history.end_time <= time + interval '1 hours' then xx_history.end_time - time when xx_history.start_time >= time and xx_history.start_time <= time + interval '1 hours' and xx_history.end_time > time + interval '1 hours' then time + interval '1 hours' - xx_history.start_time when xx_history.start_time < time and xx_history.end_time > time + interval '1 hours' then time + interval '1 hours' - time ELSE time - time END) from generate_series( date_trunc('day', current_timestamp) - interval '2 days' , date_trunc('day', current_timestamp), '1 hours') as time left join xx_history on (xx_history.start_time >= time and xx_history.end_time <= time + interval '1 hours') or (xx_history.start_time < time and xx_history.end_time >= time and xx_history.end_time <= time + interval '1 hours') or (xx_history.start_time >= time and xx_history.start_time <= time + interval '1 hours' and xx_history.end_time > time + interval '1 hours') or (xx_history.start_time < time and xx_history.end_time > time + interval '1 hours') group by time order by time |
whereとCaseの条件は一緒なので、Whereの方はもっとコストをかけない条件に見直しした方がよいですね。
実際はもっと複雑な形になるのですが、とりあえずSQL1発で出来そうな感じです。