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

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

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() DEFAULT NULL; #与苹果服务器会话 DECLARE _s_token VARCHAR() 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