问题描述
我正在尝试根据Day&Week;也是同一条语句中的一天中的小时"(这样一来，我可以看到我每小时和一周内获得了多少次访问.这是该条语句.
I'm trying to extract data according to Day of Week & also Hour of day in the same statement (so I can see how many visits I got per hour and over a week. Here's the statement.
SELECT
count(id) as count,
HOUR(created) as hour_of_day,
WEEKDAY(created) as day_of_week,
DATE_FORMAT(created,'%W') name_of_day
FROM visitors
GROUP BY day_of_week,hour_of_day
ORDER BY day_of_week,hour_of_day ASC
一些样本数据
count hour_of_day day_of_week name_of_day
2 0 0 Monday
1 1 0 Monday
1 4 0 Monday
4 5 0 Monday
1 6 0 Monday
4 7 0 Monday
1 9 0 Monday
1 10 0 Monday
1 12 0 Monday
1 13 0 Monday
2 16 0 Monday
5 18 0 Monday
5 19 0 Monday
问题如您所见，数据中的数据丢失了许多个小时.在创建一个需要每天[x，x，x，x，x，x，x，x]形式的数据的图表时，该图表将与从第一个输出开始的24小时时间轴匹配，因此我需要缺少的为"0".
The problemAs you can see, there data missing for many hours in the data. And as I'm creating a graph which needs data in the form of [x,x,x,x,x,x,x] for each day which will be matched with a 24hr timeline starting from the first output, I need the missing ones to be '0'.
虽然我可以通过循环在PHP端处理它，但要在一周中的每一天&在此期间，每小时都很累，而且绝对不干净.
While I can handle it on the PHP end with loops, looping it for each day of the week & within that, for every hour, is rather tiresome and definitely not clean.
是否可以不使用临时表(例如查询本身包含24位数字，等等?)?
Is it possible without temporarily tables (like including the 24 digits in the query itself,etc?)?
推荐答案
不是最漂亮的.但是，如果您真的不能使用临时表，它应该可以解决问题:
Not the prettiest. But it should do the trick if you really can't use temp tables:
select ifnull(count,0) as count,dh.hour_of_day,
dh.day_of_week,date_format((date('2012-01-02') + interval dh.day_of_week day),'%W') as name_of_day
from
(
select day_of_week,hour_of_day
from
(
select 0 as day_of_week union select 1 union select 2 union select 3
union select 4 union select 5 union select 6
) d
join
(
select 0 as hour_of_day
union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8
union select 9 union select 10 union select 11 union select 12
union select 13 union select 14 union select 15 union select 16
union select 17 union select 18 union select 19 union select 20
union select 21 union select 22 union select 23
) h
) dh
left outer join
(
SELECT
count(id) as count,
HOUR(created) as hour_of_day,
WEEKDAY(created) as day_of_week,
DATE_FORMAT(created,'%W') name_of_day
FROM visitors
GROUP BY day_of_week,hour_of_day
) v on dh.day_of_week = v.day_of_week and dh.hour_of_day = v.hour_of_day
ORDER BY dh.day_of_week,dh.hour_of_day ASC;
但是请小心！如果您跨多个星期运行查询，那么一周中的多个天会加在一起.您可能需要考虑添加仅本周"谓词.例如，将where yearweek(created) = yearweek(now())添加到原始选择中即可获取当前一周的数据.
Careful with this though! If you run the query across multiple weeks then multiple days of the week will get added together. You may want to consider adding a 'only this week' predicate. For example add in where yearweek(created) = yearweek(now()) into your original select to get data just for the current week.
这篇关于MySQL如何填充范围内缺少的小时/日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！