共计 2352 个字符，预计需要花费 6 分钟才能阅读完成。
这期内容当中丸趣 TV 小编将会给大家带来有关 MySQL 中按时间统计每个小时的记录数的示例分析，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
抽空在 stack overflow 转了一圈, 看到一个有关 SQL 的提问, 大概转述一下:
构造语句:
点击 (此处) 折叠或打开
CREATE TABLE `st` (
`id` varchar(8) NOT NULL,
`time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
insert into st values(F41 , 2016-08-27 00:25:58
insert into st values(F42 , 2016-08-27 01:15:03
insert into st values(F43 , 2016-08-27 02:14:30
insert into st values(F44 , 2016-08-27 02:24:12
insert into st values(F45 , 2016-08-27 03:05:46
insert into st values(F46 , 2016-08-27 04:08:07
insert into st values(F47 , 2016-08-27 05:10:35
insert into st values(F48 , 2016-08-27 07:12:32
insert into st values(F59 , 2016-08-27 08:21:19
insert into st values(F50 , 2016-08-27 09:19:26
insert into st values(F51 , 2016-08-27 11:01:56
insert into st values(F52 , 2016-08-27 12:09:38
insert into st values(F53 , 2016-08-27 13:42:48
insert into st values(F54 , 2016-08-27 14:47:26
insert into st values(F55 , 2016-08-27 15:24:12
insert into st values(F56 , 2016-08-27 16:22:05
insert into st values(F57 , 2016-08-27 18:20:47
insert into st values(F58 , 2016-08-27 19:11:09
insert into st values(F59 , 2016-08-27 19:41:47
insert into st values(F60 , 2016-08-27 19:57:06
insert into st values(F61 , 2016-08-27 20:12:45
insert into st values(F62 , 2016-08-27 21:55:41
insert into st values(F63 , 2016-08-27 22:17:38
insert into st values(F64 , 2016-08-27 23:15:17
需要按小时来统计记录的数量, 结果期望如下:
点击 (此处) 折叠或打开
ID time count
——————————————
1 00:00:00 1
2 01:00:00 1
3 02:00:00 1
4 03:00:00 2
5 04:00:00 1
6 05:00:00 1
7 06:00:00 1
8 07:00:00 0
9 08:00:00 1
10 09:00:00 1
11 10:00:00 1
12 11:00:00 0
13 12:00:00 1
14 13:00:00 1
15 14:00:00 1
16 15:00:00 1
17 16:00:00 1
18 17:00:00 1
19 18:00:00 0
20 19:00:00 1
21 20:00:00 3
22 21:00:00 1
23 22:00:00 1
24 23:00:00 1
这个问题有两个 关键点 :
1. 某个时间段, 不存在的记录, 需要输出 0;
2. 这个计数, 是 less than time 的计数方法, 即 19:xx:xx 的记录是记到 20:00:00 下面的, 而不是 19:00:00;
首先解决时间的提取问题;
利用 date_format 和 hour, 就可以把小时数提取出来了,
同时需要用 concat 去拼接一下字符串, 展示出 20:00:00 这种效果;
为了达成关键点 2 的要求, 需要稍微处理一下 hour 的结果, 使用 case when 来加工一下,
最后的语句如下:
点击 (此处) 折叠或打开
select @rownum := @rownum + 1 AS ID,
concat((case when t.hour = 24 then 0 else t.hour end), :00:00 ) as time, count(*) as count
from (select id, hour(date_format(time, %H:%i:%s))+1 as hour from st) t,
(SELECT @rownum := 0) r
group by time order by ID
效果如下:
可以看到这个语句基本满足了关键点 2 了:
现在还缺少关键点 1 的解决办法: 某个时间段, 不存在的记录, 需要输出 0;
目前想到的办法, 就是在表中提前插入占位用的无效数据, 然后在 count 数量上 -1;
PS: _(:з」∠)_水平有限, 感觉靠 SQL 来做的话, 没什么太好的思路了 ….
上述就是丸趣 TV 小编为大家分享的 MySQL 中按时间统计每个小时的记录数的示例分析了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注丸趣 TV 行业资讯频道。
