MySQL, как заполнить пропущенные часы/даты в диапазоне?

Я пытаюсь извлечь данные в соответствии с днем ​​недели, а также с часом дня в том же заявлении (чтобы я мог видеть, сколько посещений я получил в час и за неделю. Вот заявление.

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

Проблема Как видите, в data отсутствуют данные за много часов. И поскольку я создаю график, которому нужны данные в форме [x,x,x,x,x,x,x] для каждого дня, которые будут сопоставляться с 24-часовой шкалой времени, начиная с первого вывода, мне нужно отсутствующие равны «0».

Хотя я могу справиться с этим на стороне PHP с помощью циклов, зацикливать его для каждого дня недели и в течение этого времени для каждого часа довольно утомительно и определенно не чисто.

Возможно ли это без временных таблиц (например, с включением 24 цифр в сам запрос и т. д.?)?


mysql gaps-and-islands
person kouton    schedule 04.01.2012    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Не самый красивый. Но это должно сработать, если вы действительно не можете использовать временные таблицы:

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()) в свой исходный выбор, чтобы получить данные только за текущую неделю.

person Tom Mac    schedule 04.01.2012

arrow_upward
0
arrow_downward

Не уверен, почему вы не хотите использовать временные таблицы, это значительно упрощает жизнь.

решение лучше всего изложено здесь: http://www.freeopenbook.com/mysqlcookbook/mysqlckbk-chp-12-sect-10.html

По сути, вам нужно будет создать таблицу со всеми часами в день и оставить соединение с ней.

person Jarede    schedule 04.01.2012
comment
Хотя теоретически это может ответить на вопрос, было бы предпочтительнее включить сюда основные части ответа и предоставить ссылку для справки. (также: ссылка битая) - person Nanne; 25.06.2013
comment
Ссылка битая == плохой ответ - person Jesse; 22.01.2019