Mysql function instance-count the daily retention rate _ MySQL

Mysql function instance-count the daily retention rate bitsCN.com

Mysql function instance-count the daily retention rate

1. database tables

The information of the mobile phone user is saved in a visitor_user table, which contains the data that the user can obtain when using the mobile phone app, including the Merchant id, mobile phone device number, and deprecation time.

2. function analysis and design

Statistical reports generally use DB stored procedures or functions for statistics and save the data to database tables for query and display of foreground applications. 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, log on users from the day before yesterday are collected to the retention table in the daily statistics table.

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. The image will be uploaded after restoration.

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

BitsCN.com