mysql函数实例-统计日存留率

页面导航:首页 > 数据库 > Mysql > mysql函数实例-统计日存留率

mysql函数实例-统计日存留率

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

mysql函数实例-统计日存留率1.数据库表手机用户的信息保存在一张visitor_user的表中,其中包含了用户使用手机app软件时,可以获取的数据,包括: 商家id、手机设备号、下线时间等信息。
mysql函数实例-统计日存留率
 
1.表
手机用户的信息保存在一张visitor_user的表中,其中包含了用户使用手机app软件时,可以获取的数据,包括: 商家id、手机设备号、下线时间等信息。
 
2.功能分析与设计
数据统计报表一般都采用DB的存储过程或函数进行统计,并将数据保存到数据库表中,提供前台应用查询和展示。对于实时性的报表需求,建议在非业务库上进行统计。一般主库对外提供业务服务,通过复制等机制将业务数据存储到专门的数据库,或者专门的物理节点上,这样就有效的避免了报表统计功能对正常业务的影响。
 
3.实现
1.首先,从用户日统计表中将前天的登录用户统计到存留明细表中。
2.然后,从用户日统计表中将昨天的登录用户统计出来,并将前天、昨天都登录的用户的存留状态设置为1。
3.然后,从存留明细表中统计昨天用户的存留数、存留率,并保存到存留表中。
 
执行统计采用了的事件机制,定时触发调用统计函数,进行数据统计。
每天凌晨4点钟,开始统计昨天的日存留率。
 
event:
图片上传错误,待恢复后上传。
 
Sql代码  
BEGIN  
    #Routine body goes here...  
    DECLARE _yesterday DATE DEFAULT NULL;  
    DECLARE _before_yesterday DATE DEFAULT NULL;  
    DECLARE _is_success tinyint(1) DEFAULT 0;  
      
    select DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d'),INTERVAL 1 day) INTO _yesterday;  
    select DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d'),INTERVAL 2 day) INTO _before_yesterday;  
      
    #统计前天用户数,并将前天用户插入到report_user_remain_day_detail表中  
    SET _is_success = get_user_remain_day(_before_yesterday);  
  
    #查询report_user_login_day_detail,统计昨天的用户,并将前天和昨天都登录的用户状态设置为1  
    SET _is_success = update_user_status_remain(_yesterday);  
  
    #统计存留用户明细表,将统计数据存储到统计表report_user_remain_day  
    SET _is_success = insert_user_remain_day(_yesterday);  
      
    RETURN _is_success;  
END  


Sql代码  
CREATE FUNCTION `get_user_remain_day`(`_day_time` date) RETURNS int(1)  
BEGIN  
    #Routine body goes here...   
    DECLARE stopFlag INT DEFAULT 0 ;  
    DECLARE _device VARCHAR(50) DEFAULT NULL;  
    DECLARE _a_token CHAR(64) DEFAULT '-1';  
    DECLARE _day_time date;  
    DECLARE _shop_id INT(11) DEFAULT 0;  
  
    #查询当天的登录用户  
    DECLARE cur1 CURSOR FOR  select shop_id, device, a_token from report_user_login_day_detail WHERE day_time = _day_time;  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;  
  
    OPEN cur1;  
      
    FETCH cur1 INTO _shop_id, _device, _a_token;  
    WHILE  stopFlag = 0 do  
        INSERT INTO report_user_remain_day_detail(id, shop_id, day_time, device, a_token)   
            values (UUID(), _shop_id, _day_time, _device, _a_token);  
  
        FETCH cur1 INTO _shop_id, _device, _a_token;  
    END WHILE;  
  
  CLOSE cur1;  
  
    RETURN 1;  
END  
  

Sql代码  
CREATE FUNCTION `update_user_status_remain`(`_day_time` date) RETURNS tinyint(1)  
BEGIN  
    #Routine body goes here...    
    DECLARE stopFlag INT DEFAULT 0 ;  
    DECLARE _device VARCHAR(50) DEFAULT NULL;  
    DECLARE _a_token CHAR(64) DEFAULT '-1';  
      
  
    #查询昨天的登录用户  
    DECLARE cur1 CURSOR FOR  select device, a_token from report_user_login_day_detail   
        WHERE day_time = _day_time;  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;  
  
    OPEN cur1;  
      
    FETCH cur1 INTO _device, _a_token;  
    WHILE  stopFlag = 0 do   
        UPDATE report_user_remain_day_detail SET status_remain = 1, day_time = _day_time   
            WHERE device = _device;  
  
        FETCH cur1 INTO _device, _a_token;  
    END WHILE;  
  
  CLOSE cur1;     
  
    RETURN 1;  
END  


Sql代码  
CREATE FUNCTION `insert_user_remain_day`(`_day_time` date) RETURNS tinyint(1)  
BEGIN  
    #Routine body goes here...  
    DECLARE stopFlag INT DEFAULT 0 ;  
    DECLARE _status_remain TINYINT(1);  
    DECLARE _remain_count INT DEFAULT 0;  
    DECLARE _all_count INT DEFAULT 0;  
    DECLARE _temp_count INT DEFAULT 0;  
      
  
    DECLARE cur2 CURSOR FOR SELECT status_remain, count(device) FROM report_user_remain_day_detail   
        WHERE status_remain = 1 and day_time = _day_time GROUP BY status_remain;   
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag=1;  
  
    OPEN cur2;  
      
    FETCH cur2 INTO _status_remain, _remain_count;   
    WHILE  stopFlag = 0 do                
        IF _status_remain = 1 THEN  
            SET _temp_count = _remain_count;  
            SET _all_count = _all_count + _remain_count;  
                      
        ELSE   
            SET _all_count = _all_count + _remain_count;  
        END IF;  
                  
        INSERT INTO report_user_remain_day(id, day_time, remain_count, remain_percent_day, type_client)   
            VALUES(UUID(), _day_time, _temp_count, (_temp_count / _all_count), 0);  
  
        FETCH cur2 INTO _status_remain, _remain_count;   
    END WHILE;  
      
    CLOSE cur2;   
      
    RETURN 1;  
END  

 


Tags:

文章评论

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

<