MySQL中按时间统计每个小时的记录数的示例分析

发布时间:2021-10-25 16:41:14 作者:柒染
来源:亿速云 阅读:851

这期内容当中小编将会给大家带来有关MySQL中按时间统计每个小时的记录数的示例分析,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。



抽空在stack overflow转了一圈, 看到一个有关SQL的提问, 大概转述一下:

构造语句:

点击(此处)折叠或打开

  1. CREATE TABLE `st` (

  2.   `id` varchar(8) NOT NULL,

  3.   `time` timestamp NULL DEFAULT NULL,

  4.   PRIMARY KEY (`id`)

  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


  6. insert into st values('F41','2016-08-27 00:25:58');

  7. insert into st values('F42','2016-08-27 01:15:03');

  8. insert into st values('F43','2016-08-27 02:14:30');

  9. insert into st values('F44','2016-08-27 02:24:12');

  10. insert into st values('F45','2016-08-27 03:05:46');

  11. insert into st values('F46','2016-08-27 04:08:07');

  12. insert into st values('F47','2016-08-27 05:10:35');

  13. insert into st values('F48','2016-08-27 07:12:32');

  14. insert into st values('F59','2016-08-27 08:21:19');

  15. insert into st values('F50','2016-08-27 09:19:26');

  16. insert into st values('F51','2016-08-27 11:01:56');

  17. insert into st values('F52','2016-08-27 12:09:38');

  18. insert into st values('F53','2016-08-27 13:42:48');

  19. insert into st values('F54','2016-08-27 14:47:26');

  20. insert into st values('F55','2016-08-27 15:24:12');

  21. insert into st values('F56','2016-08-27 16:22:05');

  22. insert into st values('F57','2016-08-27 18:20:47');

  23. insert into st values('F58','2016-08-27 19:11:09');

  24. insert into st values('F59','2016-08-27 19:41:47');

  25. insert into st values('F60','2016-08-27 19:57:06');

  26. insert into st values('F61','2016-08-27 20:12:45');

  27. insert into st values('F62','2016-08-27 21:55:41');

  28. insert into st values('F63','2016-08-27 22:17:38');

  29. insert into st values('F64','2016-08-27 23:15:17');


需要按小时来统计记录的数量, 结果期望如下:


点击(此处)折叠或打开

  1. ID   time   count

  2. ------------------------------------------

  3. 1  00:00:00  1

  4. 2  01:00:00  1

  5. 3  02:00:00  1

  6. 4  03:00:00  2

  7. 5  04:00:00  1

  8. 6  05:00:00  1

  9. 7  06:00:00  1

  10. 8  07:00:00  0

  11. 9  08:00:00  1

  12. 10      09:00:00  1

  13. 11      10:00:00  1

  14. 12      11:00:00  0

  15. 13      12:00:00  1

  16. 14      13:00:00  1

  17. 15      14:00:00  1

  18. 16      15:00:00  1

  19. 17      16:00:00  1

  20. 18      17:00:00  1

  21. 19      18:00:00  0

  22. 20      19:00:00  1

  23. 21      20:00:00  3

  24. 22      21:00:00  1

  25. 23      22:00:00  1

  26. 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来加工一下,
最后的语句如下:


点击(此处)折叠或打开

  1. select  @rownum := @rownum + 1 AS ID,

  2.         concat((case when t.hour = 24 then 0 else t.hour end),':00:00') as time, count(*) as count

  3. from (select id, hour(date_format(time,'%H:%i:%s'))+1 as hour from st) t,

  4.      (SELECT @rownum := 0) r

  5. group by time order by ID

效果如下:

MySQL中按时间统计每个小时的记录数的示例分析

可以看到这个语句基本满足了关键点2了:

现在还缺少关键点1的解决办法: 某个时间段, 不存在的记录, 需要输出0;

目前想到的办法, 就是在表中提前插入占位用的无效数据, 然后在count数量上-1;
PS: _(:з」∠)_水平有限, 感觉靠SQL来做的话, 没什么太好的思路了....


上述就是小编为大家分享的MySQL中按时间统计每个小时的记录数的示例分析了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注亿速云行业资讯频道。

推荐阅读:
  1. Python怎么统计时间内的并发数
  2. sqlserver/mysql如何按天、按小时、按分钟统计连续时间段数据

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:LRU缓存算法的实现方法是什么

下一篇:Pandas轻松处理超大规模数据的方法是什么

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》