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.设置执行间隔和次数
每天执行一次,统计昨天数据