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来做的话, 没什么太好的思路了....

-------------------------------------------------------------------------------------------------后记---------------------------------------------------------------------------------------------------------------

其实把问题局限在SQL的话, 确实能锻炼自己的SQL水平;

不过很多时候, 这种事情交给应用层去加工比较好, 毕竟, 对MySQL来说, "垃圾"SQL还是少点比较好......