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