MySQL 根据日期分段统计处理

 

 

需求：

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

表结构和表数据：

创建表结构：

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;

　

创建表数据：

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;

　　

错误的示范：

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

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

　　

处理结果

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

### 查询时间处理，把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;

　　

处理后结果

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

### 处理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;

　　

处理结果

最终处理想要的结果：