mysql函数实例-统计日新增用户

新增用户统计，仅用作备忘。

 

CREATE FUNCTION `statics_user_new`() RETURNS int(11)
    COMMENT '统计新增用户'
BEGIN
	#Routine body goes here...	
	DECLARE stopFlag INT DEFAULT 0 ;
	DECLARE _shop_id VARCHAR(11) DEFAULT NULL;	#餐厅id
	DECLARE _device VARCHAR(50) DEFAULT NULL;	#手机设备号
	DECLARE _a_token CHAR(64) DEFAULT NULL;		#与苹果服务器会话
	DECLARE _s_token VARCHAR(64) DEFAULT NULL;	#与点菜网服务器会话
	DECLARE _counts INT DEFAULT 0;
	
	#查询昨天新创建的用户
	DECLARE cur1 CURSOR FOR SELECT id, device, a_token, s_token FROM visitor_user 
		where DATE_FORMAT(create_time, '%Y-%m-%d') = DATE_SUB(DATE_FORMAT(now(),'%Y-%m-%d'), INTERVAL 1 DAY);
	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_new_day_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;
	
	CLOSE cur1;

	#统计日新增用户数
	SELECT COUNT(device) INTO _counts FROM visitor_user 
		where DATE_FORMAT(create_time, '%Y-%m-%d') = DATE_SUB(DATE_FORMAT(now(),'%Y-%m-%d'), INTERVAL 1 DAY);
	
	INSERT INTO report_user_new_day(id, day_time, new_counts, type_client) 
		VALUES(UUID(), DATE_SUB(DATE_FORMAT(now(),'%Y-%m-%d'), INTERVAL 1 DAY), _counts, 0);
	
	SET _counts = 1;
	RETURN _counts;

END