MySQL 按小时分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10429611/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Group By Hours
提问by Burak Erdem
I'm trying to get a report from my history table by hourly usage. historytable is;
我正在尝试按小时使用情况从我的历史记录表中获取报告。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 ;
I want to group by HOURand COUNTall records in a given date range.
我想通过组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
I used a query something like this but it doesn't display all hours.
我使用了类似这样的查询，但它没有显示所有小时。
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
It displays only if there are any records.
仅当有任何记录时才显示。
Hours | Usage
------------------------------------
00:00-01:00 | 5
01:00-02:00 | 9
23:00-00:00 | 1
回答by eggyal
Your existing query can be reduced to:
您现有的查询可以简化为：
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)
To display everyhour, including those for which there is no data, you need to outer join with a table containing all the hours for which you want data. You can build such a table in your query using UNION:
要显示每一个小时，包括那些没有数据，就需要外，其所有您想要的数据小时表连接。您可以使用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
However, the treatment of groups for which no data exists is really a matter for business logic that's best placed in your data access layer rather than in the database itself: indeed it should be trivial for your application to use a zero value whenever an hour is absent.
但是，处理不存在数据的组实际上是业务逻辑的问题，最好放在数据访问层而不是数据库本身中：实际上，对于您的应用程序来说，只要一小时就使用零值应该是微不足道的缺席的。
回答by dbagnara
Given a table Logwith columns tsand value, the following will give the hourly average for the last 24 hours (assuming each hour has at least one row).
给定一个包含ts和value列的Log表，以下将给出过去 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
The column tscan be a timestamp or datetime column while valueis anything that AVG() will accept.
列ts可以是时间戳或日期时间列，而value是 AVG() 可以接受的任何内容。
