SQLのグループ集計でデータがないときも表示するには？

Question

実現したいこと

日付の範囲指定をして、その間の1時間ごと、場所ごとの滞在時間を集計したいのですが、
その際にまたデータがない時間も0で表示したいです。
どのような記述だと実現できますでしょうか。

＜出力イメージ＞
日付(yyyy-mm-dd hh:00) 場所 滞在時間計
2023-09-10 01:00 場所A 10
2023-09-10 02:00 場所A 20
2023-09-10 02:00 場所B 40
2023-09-10 03:00 場所B 20
2023-09-10 04:00 0
2023-09-10 05:00 0
2023-09-10 06:00 場所B 10
2023-09-10 07:00 場所A 60
・・・
2023-09-15 23:00 場所A 50
2023-09-15 23:00 場所B 5

前提

テーブル:T_01
・日付　datehour(yyyy-mm-dd hh:mm:ss）
・場所　work(文字列)
・滞在時間　stay_time(数値)
ある処理で、n秒おきにT_01にデータを書き込んでいます。
それを時間帯、場所ごとに集計しようとしています。

該当のソースコード

SELECT
DATE_FORMAT(t.datehour,'%Y-%m-%d %H:00') AS 日付
 ,t.work AS 場所
 ,sum(t.stay_time) as 滞在時間計
FROM
  T_01 t
WHERE
  DATE_FORMAT(t.datehour,'%Y%m%d') BETWEEN '20230915' AND '20230920' 
GROUP BY 日付,場所;

試したこと

時間ごと＆場所ごとに時間を集計するところまでは確認できています。
しかし、データがある時間だけではなく、ない時間帯のデータも出力するSQL文に苦戦してます。

補足情報（FW/ツールのバージョンなど）

MySQL 8.0.31

Comments

[yambejp]

具体的に想定する結果を例示ください

[blue0524]

ご質問ありがとうございます。
＜出力イメージ＞
日付(yyyy-mm-dd hh:00) 場所 滞在時間計
2023-09-10 01:00 場所A 10
2023-09-10 02:00 場所A 20
2023-09-10 02:00 場所B 40
2023-09-10 03:00 場所B 20
2023-09-10 04:00 0
2023-09-10 05:00 0
2023-09-10 06:00 場所B 10
2023-09-10 07:00 場所A 60
・・・（省略）
2023-09-15 23:00 場所A 50
2023-09-15 23:00 場所B 5
になります。

生データは、このような感じです。
日付(yyyy-mm-dd hh:mm:ss) ,場所, 滞在時間
2023/9/10 1:10:01,場所A,5
2023/9/10 1:21:23,場所A,5
2023/9/10 2:20:06,場所A,8
2023/9/10 2:50:34,場所A,12
2023/9/10 2:10:00,場所B,30
2023/9/10 2:43:32,場所B,10
2023/9/10 3:00:33,場所B,18
2023/9/10 3:10:00,場所B,2
2023/9/10 6:11:20,場所B,1
2023/9/10 6:30:09,場所B,1
2023/9/10 6:48:09,場所B,8
2023/9/10 7:19:19,場所A,37
2023/9/10 7:19:19,場所A,23
・・・（省略）
2023/9/15 23:09:01,場所A,16
2023/9/15 23:25:51,場所A,11
2023/9/15 23:35:11,場所A,23
2023/9/15 23:02:11,場所B,5

4時と5時のデータがないので、そこは
04:00,05:00と表示しながらも
時間計は0で表示をしたいのが実現したいことになります。

Answer 1

再帰SQLを利用してカレンダーテーブルを作成し、そのカレンダーテーブルとLEFT OUTER JOINすればよいと思います。

WITH RECURSIVE cal(dt) AS (
    SELECT STR_TO_DATE('2023-09-15 00:00', '%Y-%m-%d %H:%i')
  UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 HOUR)
    FROM cal
    WHERE dt < STR_TO_DATE('2023-09-20 23:00', '%Y-%m-%d %H:%i')
)
SELECT DATE_FORMAT(C.dt,'%Y-%m-%d %H:00') AS 日付, COALESCE(T.場所, 0) AS 場所, COALESCE(T.滞在時間計, 0) AS 滞在時間計
FROM cal C
LEFT OUTER JOIN (
  SELECT DATE_FORMAT(datehour,'%Y-%m-%d %H:00') AS 日付, work AS 場所, sum(stay_time) AS 滞在時間計
  FROM T_01
  GROUP BY 日付, 場所
) T ON DATE_FORMAT(C.dt,'%Y-%m-%d %H:00') = T.日付

Comments

[blue0524]

ありがとうございます。
再帰SQLでやりたいことは実現できそうです。
ただ、条件範囲が広くなったときの対策も考えたいと思います。
とはいえ、今後も活用できる方法なので、ありがたく参考にいたします。
この度はありがとうございました。助かりました。

Answer 2

SELECT
(t.datehour),'%Y-%m-%d %H:00') AS 日付

とりあえず間違えています

SELECT
 DATE_FORMAT(t.datehour,'%Y-%m-%d %H:00') AS 日付

追記

連続データ

SELECT (select @a := @a + interval 1 hour from (select @a:='2023-09-10 00:00') as dummy) AS num
FROM information_schema.COLUMNS
where @a<='2023-09-15 23:00'

を実行し連続データを発生させ、これに集計結果をleft joinしてやるとよいでしょう
※データが大量になるとデータが途中で切れる可能性があります

Comments

[blue0524]

ご指摘ありがとうございます。
コピペをミスりました・・・。

[yambejp]

連続データの生成ロジックだけ追記しておきました

[ikedas]

こちらにもありますね。
https://teratail.com/questions/uof4og0mcdyka0

[blue0524]

ありがとうございます。仮想を使うやり方は今後に活かせそうです。