Mysql function instance-count the daily retention rate

Mysql function instance-count the daily retention rate 1. the database table stores the information of mobile users in a visitor_user table, which contains the data that users can obtain when using the mobile app software, including: merchant id, mobile phone device number, deprecation time, and other information. 2. Function Analysis and Design data statistical reports generally use DB stored procedures or functions for statistics, and save the data to the database table to provide front-end application query and display. For real-time report requirements, we recommend that you collect statistics on non-business databases. Generally, the primary database provides external business services, and stores business data on specialized databases or physical nodes through replication and other mechanisms, this effectively avoids the impact of the report statistics function on normal services. 3. Implementation 1. First, collect statistics on the login users of the day before yesterday from the user's daily statistical table to the retention list. 2. Then, the log-on users of yesterday are counted from the daily user statistics table, and the retention status of the users logged on the day before yesterday and yesterday is set to 1. 3. Then, count the user's retention and retention rate from the retention list, and save it to the retention table. The execution Statistics adopt the mysql event mechanism to regularly trigger calls to statistical functions for data statistics. At four o'clock every morning, the daily retention rate of yesterday is counted. Event: Image Upload error. Upload after recovery.

SQL code 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; # count the number of users the day before yesterday, insert the user of the day before yesterday to the SET _ is_success = get_user_remain_day (_ before_yesterday) table in the report_user_day_detail table; # query report_user_login_day_detail and count the users of the day before yesterday, SET the status of the users logging on to the previous day and yesterday to 1 SET _ is_success = update_user_status_remain (_ yesterday); # Count the remaining user list, store the statistical data to the statistical table report_user_remain_day SET _ is_success = insert_user_remain_day (_ yesterday); RETURN _ is_success; END SQL code CREATE FUNCTION 'get _ user_remain_day '(' _ day_time) 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; # query the Login User 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 code 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 '; # query the Login User DECLARE cur1 cursor for select device yesterday, 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 code 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 region 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 values (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