MySQL-按时间统计每个小时的记录数

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: -------------------------------------------------------------------------------------------------正文-----------------------------------...
-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------

抽空在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
效果如下:



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

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

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

-------------------------------------------------------------------------------------------------后记---------------------------------------------------------------------------------------------------------------
其实把问题局限在SQL的话, 确实能锻炼自己的SQL水平;
不过很多时候, 这种事情交给应用层去加工比较好, 毕竟, 对MySQL来说, "垃圾"SQL还是少点比较好......
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
存储 SQL 关系型数据库
轻松入门MySQL:加速进销存!利用MySQL存储过程轻松优化每日销售统计(15)
轻松入门MySQL:加速进销存!利用MySQL存储过程轻松优化每日销售统计(15)
274 0
|
SQL 关系型数据库 MySQL
MySQL实战基础知识入门(2):统计一天24小时数据默认补0的sql语句
MySQL实战基础知识入门(2):统计一天24小时数据默认补0的sql语句
877 0
|
SQL 关系型数据库 MySQL
mysql一条sql查询出多个统计结果
mysql一条sql查询出多个统计结果
107 0
|
11月前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
138 0
|
关系型数据库 MySQL
mysql统计数据表中同一字段不同状态的COUNT()语句
mysql统计数据表中同一字段不同状态的COUNT()语句
166 0
|
JSON 前端开发 JavaScript
Echarts实战案例代码(15):月收入年龄分段等MYSQL分类统计PHP后台数据管理接口API数据的解决方案
Echarts实战案例代码(15):月收入年龄分段等MYSQL分类统计PHP后台数据管理接口API数据的解决方案
342 0
|
SQL 数据库 关系型数据库
MySQL设计规约问题之为什么统计表中记录数时推荐使用COUNT(*)而不是COUNT(primary_key)或COUNT(1)
MySQL设计规约问题之为什么统计表中记录数时推荐使用COUNT(*)而不是COUNT(primary_key)或COUNT(1)
|
关系型数据库 MySQL 数据库
『 MySQL数据库 』聚合统计
『 MySQL数据库 』聚合统计
|
关系型数据库 MySQL
零基础带你学习MySQL—分组统计(十二)
零基础带你学习MySQL—分组统计(十二)
|
关系型数据库 MySQL
零基础带你学习MySQL—统计函数(合计函数)(十一)
零基础带你学习MySQL—统计函数(合计函数)(十一)

推荐镜像

更多