Home >Database >Mysql Tutorial >mysql函数实例-统计日新增用户_MySQL

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal
2016-06-01 13:32:021303browse

bitsCN.com

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(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  

 

 

bitsCN.com
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn