MySQL how to fill missing hours/dates in range?

问题:

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

Some sample data

    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

The problem As 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'.

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.

Is it possible without temporarily tables (like including the 24 digits in the query itself,etc?)?

回答1:

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; 

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.

回答2:

Not sure why you don't want to use temp tables, it makes life much easier.

the solution is best laid out here: http://www.freeopenbook.com/mysqlcookbook/mysqlckbk-chp-12-sect-10.html

Essentially you would have to create a table with all the hours in a day and left join on it.