我正在做一个1小时的时间间隔计数每天的新交易数量，查询显示在第一行的所有交易，应相应地拆分适当的每小时的时间。任何帮助将不胜感激。

SELECT
   date_generator.date as the_date,
   IFNULL(COUNT(transactions.edited), 0) as count
from (
   select DATE_ADD('2022-12-29 00:00:00', INTERVAL (@i:=@i+1)-1 HOUR) as `date`
   from information_schema.columns,(SELECT @i:=0) gen_sub 
   where DATE_ADD('2022-12-29 00:00:00',INTERVAL @i HOUR) BETWEEN '2022-12-29 00:00:00' AND '2022-12-29 23:59:59'
) date_generator
left join `transactions` on DATE(edited) = date_generator.date
AND status = 'NEW'
GROUP BY date

当前输出如下：

the_date    count   
2022-12-29 00:00:00     11
2022-12-29 01:00:00     0
2022-12-29 02:00:00     0
2022-12-29 03:00:00     0
2022-12-29 04:00:00     0
2022-12-29 05:00:00     0
2022-12-29 06:00:00     0
2022-12-29 07:00:00     0
2022-12-29 08:00:00     0
2022-12-29 09:00:00     0
2022-12-29 10:00:00     0
2022-12-29 11:00:00     0
2022-12-29 12:00:00     0
2022-12-29 13:00:00     0
2022-12-29 14:00:00     0
2022-12-29 15:00:00     0
2022-12-29 16:00:00     0
2022-12-29 17:00:00     0
2022-12-29 18:00:00     0
2022-12-29 19:00:00     0
2022-12-29 20:00:00     0
2022-12-29 21:00:00     0
2022-12-29 22:00:00     0
2022-12-29 23:00:00     0

但正确的输出应该如下所示：

the_date    count   
2022-12-29 00:00:00     0
2022-12-29 01:00:00     0
2022-12-29 02:00:00     0
2022-12-29 03:00:00     0
2022-12-29 04:00:00     0
2022-12-29 05:00:00     0
2022-12-29 06:00:00     0
2022-12-29 07:00:00     0
2022-12-29 08:00:00     0
2022-12-29 09:00:00     1
2022-12-29 10:00:00     10
2022-12-29 11:00:00     0
2022-12-29 12:00:00     0
2022-12-29 13:00:00     0
2022-12-29 14:00:00     0
2022-12-29 15:00:00     0
2022-12-29 16:00:00     0
2022-12-29 17:00:00     0
2022-12-29 18:00:00     0
2022-12-29 19:00:00     0
2022-12-29 20:00:00     0
2022-12-29 21:00:00     0
2022-12-29 22:00:00     0
2022-12-29 23:00:00     0