Mysql存储过程计算留存率
程序员文章站
2022-03-14 23:18:22
...
涉及了 循环、参数定义、游标的使用。 最终代码 ============不区分org的总数据===DELIMITER $$DROP PROCEDURE IF EXISTS weekly_remain $$#IN参数类型，传进的参数CREATE PROCEDURE weekly_remain(IN lobby int) BEGIN #定义参数declare i int(2);declare e
涉及了 循环、参数定义、游标的使用。
最终代码
============不区分org的总数据=== DELIMITER $$ DROP PROCEDURE IF EXISTS weekly_remain $$ #IN参数类型，传进的参数 CREATE PROCEDURE weekly_remain(IN lobby int) BEGIN #定义参数 declare i int(2); declare ext_table varchar(20); declare uniq_no int(10); declare now_week varchar(20); declare c2 varchar(10); declare c3 varchar(10); declare c4 varchar(10); declare c5 varchar(10); declare c6 varchar(10); SET now_week = date_format(date_sub(curdate(),interval 1 week),"%Y-%u"); SET i = 1; if (lobby=101) or (lobby=102) or (lobby=104) or(lobby=105) or (lobby=107) or (lobby=108) then #引入参数@c，局部变量 select count(distinct(stbid)) INTO @c from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week and gate_uri=lobby; SET uniq_no= @c; insert into gp_weekly_leave(uniq_login,week,lobby_id) select count(distinct(stbid)),date_format(login_time,"%Y-%u"),gate_uri from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week and gate_uri=lobby; select count(distinct(t1.stb)) INTO @c_2 from (select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 2 week),"%Y-%u") and gate_uri=lobby) as t1,(select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week and gate_uri=lobby) as t2 where t1.stb=t2.stb; SET c2=@c_2/uniq_no; update gp_weekly_leave set two_week=c2 where week=now_week and lobby_id=lobby; select count(distinct(t1.stb)) INTO @c_3 from (select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 3 week),"%Y-%u") and gate_uri=lobby) as t1,(select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week and gate_uri=lobby) as t2 where t1.stb=t2.stb; SET c3=@c_3/uniq_no; update gp_weekly_leave set three_week=c3 where week=now_week and lobby_id=lobby; select count(distinct(t1.stb)) INTO @c_4 from (select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 4 week),"%Y-%u") and gate_uri=lobby) as t1,(select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week and gate_uri=lobby) as t2 where t1.stb=t2.stb; SET c4=@c_4/uniq_no; update gp_weekly_leave set four_week=c4 where week=now_week and lobby_id=lobby; select count(distinct(t1.stb)) INTO @c_5 from (select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 5 week),"%Y-%u") and gate_uri=lobby) as t1,(select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week and gate_uri=lobby) as t2 where t1.stb=t2.stb; SET c5=@c_5/uniq_no; update gp_weekly_leave set five_week=c5 where week=now_week and lobby_id=lobby; select count(distinct(t1.stb)) INTO @c_6 from (select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 6 week),"%Y-%u") and gate_uri=lobby) as t1,(select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week and gate_uri=lobby) as t2 where t1.stb=t2.stb; SET c6=@c_6/uniq_no; update gp_weekly_leave set six_week=c6 where week=now_week and lobby_id=lobby; end if; if (lobby=1000) then select count(distinct(stbid)) INTO @c from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week; SET uniq_no= @c; insert into gp_weekly_leave(uniq_login,week,lobby_id) select count(distinct(stbid)),date_format(login_time,"%Y-%u"),'1000' from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week; select count(distinct(t1.stb)) INTO @c_2 from (select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 2 week),"%Y-%u")) as t1,(select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week) as t2 where t1.stb=t2.stb; SET c2=@c_2/uniq_no; update gp_weekly_leave set two_week=c2 where week=now_week and lobby_id=lobby; select count(distinct(t1.stb)) INTO @c_3 from (select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 3 week),"%Y-%u")) as t1,(select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week) as t2 where t1.stb=t2.stb; SET c3=@c_3/uniq_no; update gp_weekly_leave set three_week=c3 where week=now_week and lobby_id=lobby; select count(distinct(t1.stb)) INTO @c_4 from (select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 4 week),"%Y-%u")) as t1,(select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week) as t2 where t1.stb=t2.stb; SET c4=@c_4/uniq_no; update gp_weekly_leave set four_week=c4 where week=now_week and lobby_id=lobby; select count(distinct(t1.stb)) INTO @c_5 from (select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 5 week),"%Y-%u")) as t1,(select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week) as t2 where t1.stb=t2.stb; SET c5=@c_5/uniq_no; update gp_weekly_leave set five_week=c5 where week=now_week and lobby_id=lobby; select count(distinct(t1.stb)) INTO @c_6 from (select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 6 week),"%Y-%u")) as t1,(select distinct(stbid) as stb from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week) as t2 where t1.stb=t2.stb; SET c6=@c_6/uniq_no; update gp_weekly_leave set six_week=c6 where week=now_week and lobby_id=lobby; end if; END $$ DELIMITER ; --#====拓展--存储过程--区分orgid===#-- DELIMITER $$ DROP PROCEDURE IF EXISTS weekly_remain_tz $$ CREATE PROCEDURE weekly_remain_tz() BEGIN #默认值 declare lobby int(5) default 104; declare i int(2); declare ext_table varchar(20); declare uniq_no int(10); declare now_week varchar(20); declare c2 varchar(10); declare c3 varchar(10); declare c4 varchar(10); declare c5 varchar(10); declare c6 varchar(10); declare a int(10); declare b varchar(20); declare leave2 varchar(20); declare leave3 varchar(20); declare leave4 varchar(20); declare leave5 varchar(20); DECLARE leave6 varchar(20); DECLARE percent2 varchar(20); DECLARE percent3 varchar(20); DECLARE percent4 varchar(20); DECLARE percent5 varchar(20); DECLARE percent6 varchar(20); #游标定义与开始 DECLARE s int default 0; #定义游标 DECLARE cursor_name CURSOR FOR select t4.vc_org_id,count(distinct(t3.stbid)) from gp_gate_login_recent t3,tv_gp_ext_tzwasu t4 where date_format(t3.login_time,"%Y-%u")=now_week and t3.stbid=t4.vc_stb_id and t3.gate_uri=lobby group by t4.vc_org_id; #设置 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; SET now_week = date_format(date_sub(curdate(),interval 1 week),"%Y-%u"); SET i = 1; #先插入数据 insert into gp_weekly_leave(uniq_login,week,lobby_id,orgid) select count(distinct(ta.stbid)),date_format(ta.login_time,"%Y-%u"),ta.gate_uri,tb.vc_org_id from gp_gate_login_recent ta,tv_gp_ext_tzwasu tb where date_format(ta.login_time,"%Y-%u")=now_week and ta.gate_uri=lobby and ta.stbid=tb.vc_stb_id group by tb.vc_org_id; #开启游标 OPEN cursor_name; #对每一行的数据进行轮训 fetch cursor_name into a,b; while s1 do #进行2周、3周、4周、5周、6周留存的计算 select count(distinct(t3.stb)) INTO @leave2 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_tzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 2 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_tzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent2=leave2/b; update gp_weekly_leave set two_week=percent2 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave3 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_tzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 3 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_tzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent3=leave3/b; update gp_weekly_leave set three_week=percent3 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave4 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_tzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 4 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_tzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent4=leave4/b; update gp_weekly_leave set four_week=percent4 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave5 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_tzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 5 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_tzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent5=leave5/b; update gp_weekly_leave set five_week=percent5 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave6 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_tzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 6 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_tzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent6=leave6/b; update gp_weekly_leave set six_week=percent6 where orgid=a and lobby_id=lobby and week=now_week; fetch cursor_name into a,b; end while; #关闭游标 CLOSE cursor_name ; END $$ DELIMITER ; --#====杭州--存储过程--区分orgid===#-- DELIMITER $$ DROP PROCEDURE IF EXISTS weekly_remain_hz $$ CREATE PROCEDURE weekly_remain_hz() BEGIN declare lobby int(5) default 101; declare i int(2); declare ext_table varchar(20); declare uniq_no int(10); declare now_week varchar(20); declare c2 varchar(10); declare c3 varchar(10); declare c4 varchar(10); declare c5 varchar(10); declare c6 varchar(10); declare a int(10); declare b varchar(20); declare leave2 varchar(20); declare leave3 varchar(20); declare leave4 varchar(20); declare leave5 varchar(20); DECLARE leave6 varchar(20); DECLARE percent2 varchar(20); DECLARE percent3 varchar(20); DECLARE percent4 varchar(20); DECLARE percent5 varchar(20); DECLARE percent6 varchar(20); DECLARE s int default 0; DECLARE cursor_name CURSOR FOR select t4.vc_org_id,count(distinct(t3.stbid)) from gp_gate_login_recent t3,tv_gp_ext_hzwasu t4 where date_format(t3.login_time,"%Y-%u")=now_week and t3.stbid=t4.vc_stb_id and t3.gate_uri=lobby group by t4.vc_org_id; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; SET now_week = date_format(date_sub(curdate(),interval 1 week),"%Y-%u"); SET i = 1; insert into gp_weekly_leave(uniq_login,week,lobby_id,orgid) select count(distinct(ta.stbid)),date_format(ta.login_time,"%Y-%u"),ta.gate_uri,tb.vc_org_id from gp_gate_login_recent ta,tv_gp_ext_hzwasu tb where date_format(ta.login_time,"%Y-%u")=now_week and ta.gate_uri=lobby and ta.stbid=tb.vc_stb_id group by tb.vc_org_id; OPEN cursor_name; fetch cursor_name into a,b; while s1 do select count(distinct(t3.stb)) INTO @leave2 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_hzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 2 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_hzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent2=leave2/b; update gp_weekly_leave set two_week=percent2 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave3 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_hzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 3 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_hzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent3=leave3/b; update gp_weekly_leave set three_week=percent3 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave4 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_hzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 4 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_hzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent4=leave4/b; update gp_weekly_leave set four_week=percent4 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave5 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_hzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 5 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_hzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent5=leave5/b; update gp_weekly_leave set five_week=percent5 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave6 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_hzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 6 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_hzwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent6=leave6/b; update gp_weekly_leave set six_week=percent6 where orgid=a and lobby_id=lobby and week=now_week; fetch cursor_name into a,b; end while; CLOSE cursor_name ; END $$ DELIMITER ; --#====省网--存储过程--区分orgid===#-- DELIMITER $$ DROP PROCEDURE IF EXISTS weekly_remain_sw $$ CREATE PROCEDURE weekly_remain_sw() BEGIN declare lobby int(5) default 102; declare i int(2); declare ext_table varchar(20); declare uniq_no int(10); declare now_week varchar(20); declare c2 varchar(10); declare c3 varchar(10); declare c4 varchar(10); declare c5 varchar(10); declare c6 varchar(10); declare a int(10); declare b varchar(20); declare leave2 varchar(20); declare leave3 varchar(20); declare leave4 varchar(20); declare leave5 varchar(20); DECLARE leave6 varchar(20); DECLARE percent2 varchar(20); DECLARE percent3 varchar(20); DECLARE percent4 varchar(20); DECLARE percent5 varchar(20); DECLARE percent6 varchar(20); DECLARE s int default 0; DECLARE cursor_name CURSOR FOR select t4.vc_org_id,count(distinct(t3.stbid)) from gp_gate_login_recent t3,tv_gp_ext_swwasu t4 where date_format(t3.login_time,"%Y-%u")=now_week and t3.stbid=t4.vc_stb_id and t3.gate_uri=lobby group by t4.vc_org_id; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1; SET now_week = date_format(date_sub(curdate(),interval 1 week),"%Y-%u"); SET i = 1; insert into gp_weekly_leave(uniq_login,week,lobby_id,orgid) select count(distinct(ta.stbid)),date_format(ta.login_time,"%Y-%u"),ta.gate_uri,tb.vc_org_id from gp_gate_login_recent ta,tv_gp_ext_swwasu tb where date_format(ta.login_time,"%Y-%u")=now_week and ta.gate_uri=lobby and ta.stbid=tb.vc_stb_id group by tb.vc_org_id; OPEN cursor_name; fetch cursor_name into a,b; while s1 do select count(distinct(t3.stb)) INTO @leave2 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_swwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 2 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_swwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent2=leave2/b; update gp_weekly_leave set two_week=percent2 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave3 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_swwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 3 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_swwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent3=leave3/b; update gp_weekly_leave set three_week=percent3 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave4 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_swwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 4 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_swwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent4=leave4/b; update gp_weekly_leave set four_week=percent4 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave5 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_swwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 5 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_swwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent5=leave5/b; update gp_weekly_leave set five_week=percent5 where orgid=a and lobby_id=lobby and week=now_week; select count(distinct(t3.stb)) INTO @leave6 from (select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_swwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=date_format(date_sub(curdate(),interval 6 week),"%Y-%u")) as t3,(select distinct(t1.stbid) as stb from gp_gate_login_recent t1,tv_gp_ext_swwasu t2 where t1.gate_uri=lobby and t1.stbid=t2.vc_stb_id and t2.vc_org_id=a and date_format(t1.login_time,"%Y-%u")=now_week) as t4 where t3.stb=t4.stb; SET percent6=leave6/b; update gp_weekly_leave set six_week=percent6 where orgid=a and lobby_id=lobby and week=now_week; fetch cursor_name into a,b; end while; CLOSE cursor_name ; END $$ DELIMITER ; ===========TIPS=== ---执行方法，不区分org 则调用weekly_remain(lobbyid) 101 102 104 105 107 108： CALL weekly_remain(1000); ---区分orgid 调用: CALL weekly_remain_hz(); CALL weekly_remain_sw(); CALL weekly_remain_tz();原文地址：Mysql存储过程计算留存率, 感谢原作者分享。
上一篇： php去除前后空格的实现方法