Как вывести данные по часам?

Question

[dfhusfhgsuo3]

Здравствуйте.

Есть запрос типа

"SELECT DATE_FORMAT(time, '%H') H, COUNT(*) N FROM `statistics` WHERE user_id=70 AND time>=CURDATE() GROUP BY DATE_FORMAT(time, '%Y-%m-%d %H') ORDER BY
  date ASC"

Нужно выводить статистику активности по часам.

В таком подходе проблем с пользователем, у кого высокая активность проблем нет (то есть если с полуночи 00 до текущего времени была активность каждый час, то все выведет корректно):

for($i = 0; $i < 24; $i = $i + 1){
    $data = mysql_fetch_assoc($sql);
       echo $chartdata['N'] .', '; 
};

Но если у пользователя активность маленькая (из 24 часов только в нескольких была активность), то появляются проблемы.

Пробовал так исправить:

for($i = 0; $i < 24; $i = $i + 1){
    $data = mysql_fetch_assoc($sql);

        $hourg = $chartdata['H'];
        $d = $chartdata['N'];

        if($hourg==$i) {
            echo $d .', '; 
        } else {
            echo '0, ';
        }
    ;}

Допустим, если у пользователя активность только в один час за весь день (и это было в 18:00) все равно данные выводятся только при первой итерации, далее становятся недоступны для использования. Здесь явно мало for

Буду благодарен за помощь

Comments

[Дмитрий]

Потому что надо использовать while
Отпадает надобность вычислять количество итерации

[dfhusfhgsuo3]

Дмитрий, c while такая же ситуация. Выход это создать массив на 24 часа и сверить полученные данные с запроса и вписать их в этот массив, потом уже использовать как нужно. Легче выхода не вижу

[Дмитрий]

dfhusfhgsuo3, с while не будет косяк в fetch

Answer 1

[dfhusfhgsuo3]

Получилось сделать следующим образом:

1) Был создан пустой массив "время - данные".
2) Из данных с запроса собираем второй массив "время - данные"
3) Соединяем массивы
4) Используем полученные данные в нужном формате

Возможно кому-то пригодится

Answer 2

[Андрей]

Select a.dt, ifnull(b._cnt, 0) as _cnt
From (
Select '00' as h
Union all select '01' as h
...
Union all select '23' as h
) a
Left join (select DATE_FORMAT(NOW(), '%H') as h, 100500 as _cnt) b using(h)

Comments

[dfhusfhgsuo3]

Как это использовать? Не слишком большая нагрузка от такого запроса на БД будет?

[Андрей]

dfhusfhgsuo3, нагрузка зависит от условий запроса подсчёта.
А так, и во from, и в left Join можно вставить подзапросы. Главное чтобы там не миллионы строк были.

А логика, вы выбираете нужные временные периоды (во from подзапрос), далее к этому делаете left Join, в котором подзапросом считаете статистику и объединяет все по часам.
Если данные за час есть - они выберутся. Если нет - то будет null, который заменится на 0.

Answer 3

[Vitsliputsli]

Как вариант, собрать таблицу часов рекурсивно:

WITH recursive hours (n) AS (
    SELECT (@q:=0) n FROM dual
    UNION ALL
    SELECT (@q:=@q+1) n FROM hours WHERE @q<24
)
SELECT hours.n, ваш_запрос.* FROM ваш_запрос
    RIGHT JOIN hours ON ваш_запрос.час = hours.n

или если часы строки, то:

WITH recursive hours (n) AS (
    SELECT CONCAT("0",CAST(@q:=0 as varchar(1))) n FROM dual
    UNION ALL
    SELECT LPAD(CAST(@q:=@q+1 as varchar(2)),2,"0") n FROM hours WHERE @q<24
)
SELECT hours.n, ваш_запрос.* FROM ваш_запрос
    RIGHT JOIN hours ON  ваш_запрос.час = hours.n