MySQL 根据日期分段统计处理

 

 

 

需求：

根据component_invoke_time每隔15分钟统计一次调用的数量

 

表结构和表数据：

 

 

 

创建表结构：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

DROP TABLE IF EXISTS `src_ai_component_operator`; CREATE TABLE `src_ai_component_operator` (   `id` int(50) NOT NULL AUTO_INCREMENT,   `application_id` int(50) NOT NULL COMMENT '应用ID',   `component_id` int(50) NOT NULL COMMENT '组件编号',   `component_name` varchar(256) NOT NULL COMMENT '组件名称',   `invoke_system_name` varchar(256) DEFAULT NULL COMMENT '被调用单位/应用',   `component_invoke_time` datetime DEFAULT NULL COMMENT '调用/访问时间，yyyy-MM-dd HH:mm:ss',   `component_invoke_count` int(10) DEFAULT NULL COMMENT '调用次数',   `compare_db` int(10) DEFAULT NULL COMMENT '访问的数量',   `component_invoke_day_time` datetime DEFAULT NULL COMMENT '调用时间，yyyy-MM-dd',   `statistics_datetime` datetime DEFAULT NULL COMMENT '统计时间，yyyy-MM-dd HH:mm:ss',   `statistics_datetime_min` datetime DEFAULT NULL COMMENT '统计时间（15分钟），yyyy-MM-dd HH:mm:ss',   `created_by` varchar(32) DEFAULT NULL COMMENT '创建人',   `created_time` datetime DEFAULT NULL COMMENT '创建时间',   `updated_by` varchar(32) DEFAULT NULL COMMENT '更新人',   `updated_time` datetime DEFAULT NULL COMMENT '更新时间',   PRIMARY KEY (`id`),   KEY `index_component_name` (`component_name`),   KEY `index_invoke_system_name` (`invoke_system_name`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='组件调用信息表';   SET FOREIGN_KEY_CHECKS = 1;

　

创建表数据：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

BEGIN; INSERT INTO `src_ai_component_operator` VALUES (1, 12345, 2344, '上传图片', 'aaaaa', '2020-09-12 08:32:48', 1, 1, '2020-09-17 11:32:55', '2020-09-17 11:33:03', '2020-09-17 11:15:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (2, 12345, 2344, '上传图片', 'aaaaa', '2020-09-13 11:32:48', 1, 1, '2020-08-05 11:32:55', '2020-08-05 11:33:03', '2020-08-05 11:15:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (3, 12345, 2344, '上传图片', 'aaaaa', '2020-09-12 10:32:48', 1, 1, '2020-09-12 11:32:55', '2020-09-12 11:33:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (4, 12345, 2344, '上传图片', 'aaaaa', '2020-09-12 11:37:48', 1, 1, '2020-09-12 11:37:55', '2020-09-12 11:37:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (5, 12345, 2344, '上传图片', 'aaaaa', '2020-09-12 09:20:48', 1, 1, '2020-09-12 11:37:55', '2020-09-12 11:37:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (6, 12345, 2344, '上传图片', 'aaaaa', '2020-09-12 08:20:48', 1, 1, '2020-09-12 11:37:55', '2020-09-12 11:37:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (7, 12345, 2344, '上传图片', 'aaaaa', '2020-08-12 08:20:48', 1, 1, '2020-09-12 11:37:55', '2020-09-12 11:37:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (8, 12345, 2344, '上传图片', 'aaaaa', '2020-09-25 09:20:48', 1, 1, '2020-09-12 11:37:55', '2020-09-12 11:37:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (9, 12345, 2344, '上传图片', 'aaaaa', '2020-09-25 08:20:48', 1, 1, '2020-09-12 11:37:55', '2020-09-12 11:37:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (10, 12345, 2344, '上传图片', 'aaaaa', '2020-09-25 09:10:48', 1, 1, '2020-09-12 11:37:55', '2020-09-12 11:37:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (11, 12345, 2344, '上传图片', 'aaaaa', '2020-09-25 08:32:48', 1, 1, '2020-09-12 11:37:55', '2020-09-12 11:37:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (12, 12345, 2344, '上传图片', 'aaaaa', '2020-09-25 08:17:48', 1, 1, '2020-09-12 11:37:55', '2020-09-12 11:37:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (13, 12345, 2344, '上传图片', 'aaaaa', '2020-09-25 08:09:48', 1, 1, '2020-09-12 11:37:55', '2020-09-12 11:37:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); INSERT INTO `src_ai_component_operator` VALUES (14, 12345, 2344, '上传图片', 'aaaaa', '2020-09-25 08:52:48', 1, 1, '2020-09-12 11:37:55', '2020-09-12 11:37:03', '2020-09-12 14:30:00', NULL, NULL, NULL, NULL); COMMIT;

　　

错误的示范：

格式化时间错误的时间出现的错误：

?

1 2	### 格式化后为NULL SELECT DATE_FORMAT("2020-09-25 08:60", "%Y-%m-%d");

　　

处理结果

 

 

 

首先把日期时间处理正确：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

### 查询时间处理，把component_invoke_time按照15分间隔的时间进行处理，其中15分钟可以看作N分钟，这里处理相对应的N即可，例如每隔10分钟统计一次 ### 例如 ： ### 2020-09-25 08:07 为 2020-09-25 08:15 ### 2020-09-25 08:14 为 2020-09-25 08:15 ### 2020-09-25 08:22 为 2020-09-25 08:30 ### 2020-09-25 08:50 为 2020-09-25 09:00 SELECT     DATE_FORMAT(         concat(             date( t.component_invoke_time ),             " ",             CASE WHEN (floor( MINUTE ( t.component_invoke_time ) / 15 ) * 15 + 15) = 60 THEN HOUR ( t.component_invoke_time) + 1 ELSE HOUR ( t.component_invoke_time) END,             ":",             CASE WHEN (floor( MINUTE ( t.component_invoke_time ) / 15 ) * 15 + 15) = 60 THEN "00" ELSE (floor( MINUTE ( t.component_invoke_time ) / 15 ) * 15 + 15) END         ),     "%Y-%m-%d %H:%i") FROM src_ai_component_operator AS t;

　　

处理后结果

 

 

比对查看处理的日期格式是否正确：

?

1 2 3 4 5 6 7

### 处理15分钟相加后60进1的情况 SELECT     t.component_invoke_time AS datetime,     date( t.component_invoke_time ) AS date,     CASE WHEN (floor( MINUTE ( t.component_invoke_time ) / 15 ) * 15 + 15) = 60 THEN HOUR ( t.component_invoke_time) + 1 ELSE HOUR ( t.component_invoke_time) END AS hours ,     CASE WHEN (floor( MINUTE ( t.component_invoke_time ) / 15 ) * 15 + 15) = 60 THEN "00" ELSE (floor( MINUTE ( t.component_invoke_time ) / 15 ) * 15 + 15) END AS mins FROM src_ai_component_operator AS t;

　　

处理结果

 

 

最终处理想要的结果：