Группа MySQL по часам

Я пытаюсь получить отчет из моей истории таблицы почасовое использование. таблица history ;

 CREATE TABLE IF NOT EXISTS `history` ( `history_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL DEFAULT '0', `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`history_id`), KEY `user_id` (`user_id`), KEY `created` (`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; 

Я хочу группировать HOUR и COUNT все записи за определенный диапазон дат.

 Hours | Usage ------------------------------------ 00:00-01:00 | 5 01:00-02:00 | 9 02:00-03:00 | 0 (or NULL) 03:00-04:00 | 20 ... ... ... 22:00-23:00 | 11 23:00-00:00 | 1 

Я использовал запрос что-то вроде этого, но он не отображает все часы.

 SELECT CASE WHEN HOUR(created) BETWEEN 0 AND 1 THEN '00:00 - 01:00' WHEN HOUR(created) BETWEEN 1 AND 2 THEN '01:00 - 02:00' WHEN HOUR(created) BETWEEN 2 AND 3 THEN '02:00 - 03:00' WHEN HOUR(created) BETWEEN 3 AND 4 THEN '03:00 - 04:00' WHEN HOUR(created) BETWEEN 4 AND 5 THEN '04:00 - 05:00' WHEN HOUR(created) BETWEEN 5 AND 6 THEN '05:00 - 06:00' WHEN HOUR(created) BETWEEN 6 AND 7 THEN '06:00 - 07:00' WHEN HOUR(created) BETWEEN 7 AND 8 THEN '07:00 - 08:00' WHEN HOUR(created) BETWEEN 8 AND 9 THEN '08:00 - 09:00' WHEN HOUR(created) BETWEEN 9 AND 10 THEN '09:00 - 10:00' WHEN HOUR(created) BETWEEN 10 AND 11 THEN '10:00 - 11:00' WHEN HOUR(created) BETWEEN 11 AND 12 THEN '11:00 - 12:00' WHEN HOUR(created) BETWEEN 12 AND 13 THEN '12:00 - 13:00' WHEN HOUR(created) BETWEEN 13 AND 14 THEN '13:00 - 14:00' WHEN HOUR(created) BETWEEN 14 AND 15 THEN '14:00 - 15:00' WHEN HOUR(created) BETWEEN 15 AND 16 THEN '15:00 - 16:00' WHEN HOUR(created) BETWEEN 16 AND 17 THEN '16:00 - 17:00' WHEN HOUR(created) BETWEEN 17 AND 18 THEN '17:00 - 18:00' WHEN HOUR(created) BETWEEN 18 AND 19 THEN '18:00 - 19:00' WHEN HOUR(created) BETWEEN 19 AND 20 THEN '19:00 - 20:00' WHEN HOUR(created) BETWEEN 20 AND 21 THEN '20:00 - 21:00' WHEN HOUR(created) BETWEEN 21 AND 22 THEN '21:00 - 23:00' WHEN HOUR(created) BETWEEN 22 AND 23 THEN '22:00 - 23:00' WHEN HOUR(created) BETWEEN 23 AND 24 THEN '23:00 - 00:00' END AS `Hours`, COUNT(*) AS `usage` FROM history WHERE (created BETWEEN '2012-02-07' AND NOW()) GROUP BY CASE WHEN HOUR(created) BETWEEN 0 AND 1 THEN 1 WHEN HOUR(created) BETWEEN 1 AND 2 THEN 2 WHEN HOUR(created) BETWEEN 2 AND 3 THEN 3 WHEN HOUR(created) BETWEEN 3 AND 4 THEN 4 WHEN HOUR(created) BETWEEN 4 AND 5 THEN 5 WHEN HOUR(created) BETWEEN 5 AND 6 THEN 6 WHEN HOUR(created) BETWEEN 6 AND 7 THEN 7 WHEN HOUR(created) BETWEEN 7 AND 8 THEN 8 WHEN HOUR(created) BETWEEN 8 AND 9 THEN 9 WHEN HOUR(created) BETWEEN 9 AND 10 THEN 10 WHEN HOUR(created) BETWEEN 10 AND 11 THEN 11 WHEN HOUR(created) BETWEEN 11 AND 12 THEN 12 WHEN HOUR(created) BETWEEN 12 AND 13 THEN 13 WHEN HOUR(created) BETWEEN 13 AND 14 THEN 14 WHEN HOUR(created) BETWEEN 14 AND 15 THEN 15 WHEN HOUR(created) BETWEEN 15 AND 16 THEN 16 WHEN HOUR(created) BETWEEN 16 AND 17 THEN 17 WHEN HOUR(created) BETWEEN 17 AND 18 THEN 18 WHEN HOUR(created) BETWEEN 18 AND 19 THEN 19 WHEN HOUR(created) BETWEEN 19 AND 20 THEN 20 WHEN HOUR(created) BETWEEN 20 AND 21 THEN 21 WHEN HOUR(created) BETWEEN 21 AND 22 THEN 22 WHEN HOUR(created) BETWEEN 22 AND 23 THEN 23 WHEN HOUR(created) BETWEEN 23 AND 24 THEN 24 END 

Он отображается только в том случае, если есть записи.

 Hours | Usage ------------------------------------ 00:00-01:00 | 5 01:00-02:00 | 9 23:00-00:00 | 1 

Ваш существующий запрос можно уменьшить до:

 SELECT CONCAT(HOUR(created), ':00-', HOUR(created)+1, ':00') AS Hours , COUNT(*) AS `usage` FROM history WHERE created BETWEEN '2012-02-07' AND NOW() GROUP BY HOUR(created) 

Чтобы отображать каждый час, в том числе те, для которых нет данных, вам необходимо выполнить внешнее соединение с таблицей, содержащей все часы, для которых вы хотите данные. Вы можете создать такую ​​таблицу в своем запросе с помощью UNION :

 SELECT CONCAT(Hour, ':00-', Hour+1, ':00') AS Hours , COUNT(created) AS `usage` FROM history RIGHT JOIN ( SELECT 0 AS Hour UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 ) AS AllHours ON HOUR(created) = Hour WHERE created BETWEEN '2012-02-07' AND NOW() OR created IS NULL GROUP BY Hour ORDER BY Hour 

Тем не менее, обработка групп, для которых не существует данных, действительно является вопросом бизнес-логики, который лучше всего подходит для вашего уровня доступа к данным, а не в самой базе данных: действительно, для вашего приложения должно быть тривиально использовать нулевое значение каждый час нет на месте.

Учитывая таблицу Log с столбцами ts и значением , следующее будет давать почасовую среднюю за последние 24 часа (при условии, что каждый час имеет хотя бы одну строку).

 SELECT FROM_UNIXTIME( TRUNCATE( UNIX_TIMESTAMP( `ts` )/3600, 0 ) * 3600 ) as h, AVG( `value` ) as v, COUNT( * ) as q FROM Log GROUP BY h ORDER BY h desc LIMIT 24 

Столбец ts может быть столбцом timestamp или datetime, а значением является то, что AVG () будет принимать.