mysql函数实例-统计日存留率_MySQL

Sql代码 BEGIN #Routine body goes here... DECLARE _yesterday DATE DEFAULT NULL; DECLARE _before_yesterday DATE DEFAULT NULL; DECLARE _is_success tinyint(1) DEFAULT 0; select DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d'),INTERVAL 1 day) INTO _yesterday; select DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d'),INTERVAL 2 day) INTO _before_yesterday; #统计前天用户数,并将前天用户插入到report_user_remain_day_detail表中 SET _is_success = get_user_remain_day(_before_yesterday); #查询report_user_login_day_detail,统计昨天的用户,并将前天和昨天都登录的用户状态设置为1 SET _is_success = update_user_status_remain(_yesterday); #统计存留用户明细表,将统计数据存储到统计表report_user_remain_day SET _is_success = insert_user_remain_day(_yesterday); RETURN _is_success; END Sql代码 CREATE FUNCTION `get_user_remain_day`(`_day_time` date) RETURNS int(1) BEGIN #Routine body goes here... DECLARE stopFlag INT DEFAULT 0 ; DECLARE _device VARCHAR(50) DEFAULT NULL; DECLARE _a_token CHAR() DEFAULT '-1'; DECLARE _day_time date; DECLARE _shop_id INT(11) DEFAULT 0; #查询当天的登录用户 DECLARE cur1 CURSOR FOR select shop_id, device, a_token from report_user_login_day_detail WHERE day_time = _day_time; DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1; OPEN cur1; FETCH cur1 INTO _shop_id, _device, _a_token; WHILE stopFlag = 0 do INSERT INTO report_user_remain_day_detail(id, shop_id, day_time, device, a_token) values (UUID(), _shop_id, _day_time, _device, _a_token); FETCH cur1 INTO _shop_id, _device, _a_token; END WHILE; CLOSE cur1; RETURN 1; END Sql代码 CREATE FUNCTION `update_user_status_remain`(`_day_time` date) RETURNS tinyint(1) BEGIN #Routine body goes here... DECLARE stopFlag INT DEFAULT 0 ; DECLARE _device VARCHAR(50) DEFAULT NULL; DECLARE _a_token CHAR() DEFAULT '-1'; #查询昨天的登录用户 DECLARE cur1 CURSOR FOR select device, a_token from report_user_login_day_detail WHERE day_time = _day_time; DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1; OPEN cur1; FETCH cur1 INTO _device, _a_token; WHILE stopFlag = 0 do UPDATE report_user_remain_day_detail SET status_remain = 1, day_time = _day_time WHERE device = _device; FETCH cur1 INTO _device, _a_token; END WHILE; CLOSE cur1; RETURN 1; END Sql代码 CREATE FUNCTION `insert_user_remain_day`(`_day_time` date) RETURNS tinyint(1) BEGIN #Routine body goes here... DECLARE stopFlag INT DEFAULT 0 ; DECLARE _status_remain TINYINT(1); DECLARE _remain_count INT DEFAULT 0; DECLARE _all_count INT DEFAULT 0; DECLARE _temp_count INT DEFAULT 0; DECLARE cur2 CURSOR FOR SELECT status_remain, count(device) FROM report_user_remain_day_detail WHERE status_remain = 1 and day_time = _day_time GROUP BY status_remain; DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1; OPEN cur2; FETCH cur2 INTO _status_remain, _remain_count; WHILE stopFlag = 0 do IF _status_remain = 1 THEN SET _temp_count = _remain_count; SET _all_count = _all_count + _remain_count; ELSE SET _all_count = _all_count + _remain_count; END IF; INSERT INTO report_user_remain_day(id, day_time, remain_count, remain_percent_day, type_client) VALUES(UUID(), _day_time, _temp_count, (_temp_count / _all_count), 0); FETCH cur2 INTO _status_remain, _remain_count; END WHILE; CLOSE cur2; RETURN 1; END