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

素颜马尾好姑娘i 2023-01-02 02:21 170阅读 0赞

### Mysql存储过程定时统计15分钟内注册用户数量 ###

*  基础数据
 *   *  1.用户表
     *  2.时间基表
     *  3.用户注册统计表
 *  创建存储过程
 *   *  1.存储过程-统计昨天每15分钟内的用户注册数量
     *  2.存储过程-开始调用（方便以后扩展）
 *  创建定时任务
 *   *  1.定时任务
     *  2.设置执行间隔和次数
 *  统计结果预览

# 基础数据 #

## 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'))

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmczNTM5MzM1NDY_size_16_color_FFFFFF_t_70_pic_center]

## 2.设置执行间隔和次数 ##

每天执行一次，统计昨天数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmczNTM5MzM1NDY_size_16_color_FFFFFF_t_70_pic_center 1]

# 统计结果预览 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmczNTM5MzM1NDY_size_16_color_FFFFFF_t_70_pic_center 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmczNTM5MzM1NDY_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/8fbcd4cda42247d1ba22242973e608c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmczNTM5MzM1NDY_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/17374a26bff245599f946fdbeb1a017b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbmczNTM5MzM1NDY_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/b73b104a3f3847398c4953754b7174ed.png