mysql函数实例-统计1周内未登陆的用户
CREATE FUNCTION `statics_user_unlogin_week`() RETURNS int(11) COMMENT '流失用户统计（一周内未登录的用户）'BEGIN#Routine body goes here...DECLARE stopFlag INT DEFAULT 0 ;DECLARE _shop_id INT(11) DEFAULT 0;#餐厅idDECLARE _device VARCHAR(50);#手机设备号DECLARE _a_token CHAR(64) DEFAULT '-1';#与苹果服务器会话DECLARE _s_token VARCHAR(64) DEFAULT NULL;#与点菜网服务器会话DECLARE _lost_count INT(11) DEFAULT 0;DECLARE _start_day date;DECLARE _end_day date;#统计1周前下线的用户数(1周内未登陆)DECLARE cur1 CURSOR FOR SELECT id, device, a_token, s_token FROM visitor_user WHERE DATEDIFF(NOW(), off_time) >= 8;DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;OPEN cur1;FETCH cur1 INTO _shop_id, _device, _a_token, _s_token;WHILE stopFlag = 0 doINSERT INTO report_user_unlogin_week_detail(id, shop_id, device, a_token, s_token) values (UUID(), _shop_id, _device, _a_token, _s_token);FETCH cur1 INTO _shop_id, _device, _a_token, _s_token;END WHILE;#今天之前1周的第1天(例如: 2013-07-07)SET _start_day = DATE_SUB(DATE_FORMAT(now(),'%Y-%m-%d'), INTERVAL 8 DAY);#今天之前1周的最后1天(例如: 2013-07-13)SET _end_day = DATE_SUB(DATE_FORMAT(now(),'%Y-%m-%d'), INTERVAL 1 DAY);SELECT COUNT(device) into _lost_count FROM visitor_user WHERE DATEDIFF(NOW(), off_time) >= 8;#统计1周内的未登陆的用户数INSERT INTO report_user_unlogin_week(id, start_day, end_day, lost_count, type_client) VALUES(UUID(), _start_day, _end_day, _lost_count, 0);RETURN 1;END
?
