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

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

1.数据库表

手机用户的信息保存在一张visitor_user的表中，其中包含了用户使用手机app软件时，可以获取的数据，包括： 商家id、手机设备号、下线时间等信息。

2.功能分析与设计

数据统计报表一般都采用DB的存储过程或函数进行统计，并将数据保存到数据库表中，提供前台应用查询和展示。对于实时性的报表需求，建议在非业务库上进行统计。一般主库对外提供业务服务，通过复制等机制将业务数据存储到专门的数据库，或者专门的物理节点上，这样就有效的避免了报表统计功能对正常业务的影响。

3.实现

1.首先，从用户日统计表中将前天的登录用户统计到存留明细表中。

2.然后，从用户日统计表中将昨天的登录用户统计出来，并将前天、昨天都登录的用户的存留状态设置为1。

3.然后，从存留明细表中统计昨天用户的存留数、存留率，并保存到存留表中。

执行统计采用了mysql的事件机制，定时触发调用统计函数，进行数据统计。

每天凌晨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(), &＃39;%Y-%m-%d&＃39;),INTERVAL 1 day) INTO _yesterday;      select DATE_SUB(DATE_FORMAT(NOW(), &＃39;%Y-%m-%d&＃39;),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 &＃39;-1&＃39;;      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 &＃39;-1&＃39;;              #查询昨天的登录用户      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