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

页面导航:首页 > 数据库 > Mysql > mysql函数实例-统计日新增用户

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

来源: 作者: 时间:2016-02-18 10:28 【

mysql函数实例-统计日新增用户Sql代码 CREATE FUNCTION `statics_user_new`() RETURNS int(11) COMMENT #39;统计新增用户#39; BEGIN #Routine body goes here... ...
函数实例-统计日新增用户
 
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  

 

 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<