mysql函数实例-统计1周内未登陆的用户

搜索泥称

　　流失用户统计（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;#餐厅id
DECLARE _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 do
INSERT 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