mysql函数实例-统计日新增用户
[code ]Sql代码
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[/code]
