mysql 统计新增用户,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