需求
传入日期,计算当天每隔2小时的统计数据,并补全没有数据的时间点。(00:00 02:00 ... 22:00)
-- 每隔2小时查询,传入日期,查询该日期每隔2小时的数据
with recursive dates as (
select
hour('2023-11-03 00:00:00') - mod(hour('2023-11-03 00:00:00'),2) as `date`
union all
select `date` + 2 from dates where `date` < hour('2023-11-03 23:59:59') - mod(hour('2023-11-03 23:59:59'),2)
)
select
CONCAT(LPAD(dates.date, 2, '0'),':00') as '统计时间',
COALESCE(count(tableA.create_time),0) as '统计数据'
from dates
-- 这里更新下,注意这里用right join查询速度会快很多,但是只会查出有数据的小时标题
left join
your_table as tableA on hour(tableA.create_time) - mod(hour(tableA.create_time),2) = dates.date
group by dates.date
order by dates.date
返回结果:
注意:
1)临时表 dates 和 统计表tableA之间,需要left join, 且不带where条件。
如果统计表需要 多表关联 且附带where条件, 请勿直接在以上SQL中加 inner join或where条件,否则会导致日期显示不全,可通过套一层临时表解决。 方法同上一篇按小时查询中写的。
2)其中date字段可以根据具体统计所需时间段变换
本文中 hour('2023-11-03 00:00:00') - mod(hour('2023-11-03 00:00:00'),2) as `date` :
为取日期的小时数- 小时数除以2取余数, 达到的效果为:
0点(时间段):统计值为0点到1点59分
2点(时间段):统计值为2点到3点59分
... 以此类推
3)时间格式变化参考:
select hour(create_time) 原小时,
concat(LPAD((hour(create_time))- mod(hour(create_time),2),2,'0'),':00') as 时间格式1,
date_format(concat(date(create_time),' ', floor((hour(create_time)/2))*2),'%Y-%m-%d %H:00:00') as 时间格式2
from your_table
WHERE date(create_time) = '2023-11-03' order by 原小时