bitscn.com
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(), '%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
bitscn.com