Mysql定时任务统计昨天每15分钟内注册用户数量

基础数据

1.用户表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
  `register_time` datetime(0) NULL DEFAULT NULL COMMENT '注册日期'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES ('小红', '2020-12-30 02:49:11');
INSERT INTO `user` VALUES ('黎明', '2020-12-30 03:49:11');
INSERT INTO `user` VALUES ('小明', '2020-12-30 03:52:11');
INSERT INTO `user` VALUES ('Jack', '2020-12-30 04:25:11');
INSERT INTO `user` VALUES ('小马', '2020-12-30 05:36:11');
INSERT INTO `user` VALUES ('Lucci', '2020-12-30 05:39:11');
INSERT INTO `user` VALUES ('Pony', '2020-12-30 06:33:11');
INSERT INTO `user` VALUES ('刘是', '2020-12-30 01:25:11');

2.时间基表

这里只提供到06:45，后面的可自己添加到23:45即可

DROP TABLE IF EXISTS `time_base`;
CREATE TABLE `time_base`  (
  `time` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  INDEX `index_time`(`time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `time_base` VALUES ('00:00');
INSERT INTO `time_base` VALUES ('00:15');
INSERT INTO `time_base` VALUES ('00:30');
INSERT INTO `time_base` VALUES ('00:45');
INSERT INTO `time_base` VALUES ('01:00');
INSERT INTO `time_base` VALUES ('01:15');
INSERT INTO `time_base` VALUES ('01:30');
INSERT INTO `time_base` VALUES ('01:45');
INSERT INTO `time_base` VALUES ('02:00');
INSERT INTO `time_base` VALUES ('02:15');
INSERT INTO `time_base` VALUES ('02:30');
INSERT INTO `time_base` VALUES ('02:45');
INSERT INTO `time_base` VALUES ('03:00');
INSERT INTO `time_base` VALUES ('03:00');
INSERT INTO `time_base` VALUES ('03:15');
INSERT INTO `time_base` VALUES ('03:30');
INSERT INTO `time_base` VALUES ('03:45');
INSERT INTO `time_base` VALUES ('04:00');
INSERT INTO `time_base` VALUES ('04:15');
INSERT INTO `time_base` VALUES ('04:30');
INSERT INTO `time_base` VALUES ('04:45');
INSERT INTO `time_base` VALUES ('05:15');
INSERT INTO `time_base` VALUES ('05:30');
INSERT INTO `time_base` VALUES ('05:45');
INSERT INTO `time_base` VALUES ('06:00');
INSERT INTO `time_base` VALUES ('06:15');
INSERT INTO `time_base` VALUES ('06:30');
INSERT INTO `time_base` VALUES ('06:45');

3.用户注册统计表

DROP TABLE IF EXISTS `user_register_report`;
CREATE TABLE `user_register_report`  (
  `count_time` datetime(0) NULL DEFAULT NULL COMMENT '统计日期',
  `register_time` datetime(0) NULL DEFAULT NULL COMMENT '注册日期',
  `count` int(0) NULL DEFAULT NULL COMMENT '注册人数'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

创建存储过程

1.存储过程-统计昨天每15分钟内的用户注册数量

CREATE DEFINER=`admin`@`%` PROCEDURE `proc_user_register_count`(IN `v_date_start` varchar(20),IN `v_date_end` varchar(20))
BEGIN

	declare v_time_tmp timestamp;
	declare v_flag int default 0;
	declare v_cur cursor for
	select STR_TO_DATE(CONCAT(CONCAT(v_date_start,' '), t.time), '%Y-%m-%d %H:%i:%s') as DT FROM time_base t;
	declare continue handler for not found set v_flag = 1;

	delete from user_register_report t where t.register_time >= STR_TO_DATE(v_date_start, '%Y-%m-%d') and t.register_time < STR_TO_DATE(v_date_end, '%Y-%m-%d');

	open v_cur;
			repeat
			      fetch v_cur into v_time_tmp;
					if v_flag != 1 then

						INSERT INTO user_register_report(count_time, register_time, count)
						select now(), v_time_tmp, count(1) as n_user from user tu
						where  tu.register_time >= v_time_tmp
						and tu.register_time <= DATE_ADD(v_time_tmp,INTERVAL 15 MINUTE);

					end if;
				until v_flag = 1
			end repeat;
	close v_cur;
END

2.存储过程-开始调用（方便以后扩展）

CREATE DEFINER=`admin`@`%` PROCEDURE `proc_start`(IN `v_date_start` varchar(20),IN `v_date_end` varchar(20))
BEGIN
		CALL proc_user_register_count(v_date_start, v_date_end);
END

创建定时任务

1.定时任务

CALL proc_start(DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d'), DATE_FORMAT(CURRENT_DATE,'%Y-%m-%d'))

2.设置执行间隔和次数

每天执行一次，统计昨天数据

统计结果预览