MySQL 时间范围按 DAYNAME 分组

Question

我需要帮助来计算按自定义时间范围(无间隔)分组然后按天分组的值的摘要。例如:

Monday    (00:00-07:00, 07:00-11:00, 11:00-13:00, 13:00-19:00 and 19:00-00:00)
Tuesday   (00:00-07:00, 07:00-11:00, 11:00-13:00, 13:00-19:00 and 19:00-00:00)
Wednesday (00:00-07:00 ...

我知道按工作日分组是:

select count(values), DAYNAME(date) as Day from data group by Day;

并执行正常的非时间范围如下:

select  sum(case when clients between 0 and 30 then 1 end) as '0-30'
,sum(case when clients between 30 and 120 then 1 end) as '30-120'
,sum(case when clients between 120 and 300 then 1 end) as '120-300'
,sum(case when clients between 300 and 900 then 1 end) as '300-900'
,sum(case when clients between 900 and 1800 then 1 end) as '900-1800'
,sum(case when clients between 1800 and 3600 then 1 end) as '1800-3600'
,sum(case when clients between 3600 and 14400 then 1 end) as '3600-14400'
,sum(case when clients >= 14400 then 1 end) as '14400+'
    from data;

但是如何设置时间范围和工作日呢？

Answer 1

没关系。我自己找到了答案:

SELECT sum(value), date, DAYNAME(date) as Day, case 
when TIME(date) >= '00:00' and TIME(date)< '07:00' then '00:00-07:00'
when TIME(date) >= '07:00' and TIME(date)< '11:00' then '07:00-11:00'
when TIME(date) >= '11:00' and TIME(date)< '13:00' then '11:00-13:00'
when TIME(date) >= '13:00' and TIME(date)< '19:00' then '13:00-19:00'
else '19:00-00:00' 
end as time_period
from data group by Day, time_period;