按小时分组的MySQL小组 [英] MySQL Group By Hours
本文介绍了按小时分组的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'，$ b $``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 给定日期范围内的所有记录。
小时|用法
------------------------------------
00：00-01 ：00 | 5
01：00-02：00 | 9
02：00-03：00 | 0（或NULL）
03：00-04：00 | 20
...
...
...
22：00-23：00 | 11
23：00-00：00 | 1
我使用了类似这样的查询，但它不显示所有小时。
'b$ b
选择
案例
当时间（创建时间）在0到1之间'00：00 - 01 ：00'
'1'到'2'之间的小时（创建时间）'01：00 - 02:00'
'小时（创建时）'在2和3之间'02：00 - 03:00'
当HOUR（创建）3和4之间THEN '03：00 - 04:00'
当HOUR（创建）4和5之间THEN '04：00 - 05:00'
当HOUR（创建）5和6之间THEN '05：00 - 06:00'
当HOUR（创建）6和7之间THEN '06：00 - 07:00'
当HOUR（创建） '7：00至08:00'
'8小时至9小时（创建时间）'08：00至09:00'
当小时（创建时）在9至10之间'09：00 - 10:00'
'10小时至11小时（创建时间）''10：00 - 11:00'
'11小时（创建时间） 00 - 12:00'
当小时（创建）BETWEE N 12 AND 13 THEN '12：00 - 13:00'
当13（小时）和14小时之间（创建时间）'13：00 - 14:00'
小时（创建时）14和15之间THEN '14：00 - 15:00'
当HOUR（创建）15至16 THEN '15：00 - 16:00'
当HOUR（创建）16和17之间THEN '16： 00 - 17:00'
当HOUR（创建）17与18 THEN '17：00 - 18:00'
当HOUR（创建）18和19之间THEN '18：00 - 19： 00'
当HOUR（创建）19和20之间THEN '19：00 - 20:00'
当HOUR（创建）介于20和21 THEN '20：00 - 21:00'
当21小时和22小时之间'21:00到23:00'
小时（创建时）22小时和23小时之间'22：00 - 23:00'
小时（创建）23和24之间'23：00 - 00:00'
结束为``小时`，
COUNT（*）AS`usage`
FROM history
WHERE（创建BETWEEN'2012-02-07'和NOW（））
GROUP BY
CASE
当小时（创建）在0和1之间时1
时间（创建时间）1和2之间2
时间（创建时间）2和3之间3
时间（创建时间）3和4之间4
时间HOUR（已创建）在4和5之间，然后5
当创建时在5和6之间，然后6
（创建时）在6和7之间，然后7
当创建时间7和8然后8
当8小时和9小时（创建）之间9小时$ b $小时（创建时）在9和10之间10
小时（创建时）在10和11之间11
何时（创建）在11和12之间12
当小时（创建）在12和13之间时13
何时（创建）在13和14之间14 14
当HOUR（已创建）14和15之间15
（创建时）15和16之间16（$创建）时间（创建）16和17之间17
创建时间间隔17和18那么18
当时（创建）在18和19之间，然后19
当时（创建）在19和20之间2 0
何时（创建）20和21之间21
何时（创建）21和22之间22
何时（创建）22和23之间23
当小时（已创建）在23和24之间24时24
END
只显示是任何记录。
小时|用法
------------------------------------
00：00-01 ：00 | 5
01：00-02：00 | 9
23：00-00：00 | 1
解决方案
您现有的查询可以简化为：，COUNT（*）AS`usage`
FROM历史
WHERE创建BETWEEN'2012-02-07'和NOW（）
GROUP BY HOUR（已创建）
要显示每个小时，包括那些没有数据的小时，您需要外部联接，其中包含一个包含所有需要数据的小时的表格。你可以在你的查询中使用 UNION 来构建这样的表格：
SELECT CONCAT（小时，'00-'，小时+1，'：00'）AS小时
，COUNT（已创建）AS`usage`
FROM历史
RIGHT JOIN（
选择0 AS小时
所有选择1所有选择2所有选择3
所有选择4所有选择5所有选择5所有选择6
所有选择7所有选择7所有选择8所有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（已创建）=小时
WHERE创建BETWEEN '2012-02-07'AND NOW（）或者创建IS NULL
GROUP BY小时
ORDER BY小时
然而，对于没有数据存在的群体的处理实际上是一个商业问题逻辑最好放置在数据访问层而不是数据库本身：事实上，每当一小时不存在时，应用程序应该使用零值。
I'm trying to get a report from my history table by hourly usage. history table is;
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 HOUR and COUNT all records in a given date range.
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
解决方案
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 every hour, 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:
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.
这篇关于按小时分组的MySQL小组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
