Mysql存储过程计算留存率

涉及了 循环、参数定义、游标的使用。 最终代码 ============不区分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 s <pre class="brush:php;toolbar:false"> 1 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 s <pre class="brush:php;toolbar:false"> 1 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 s <pre class="brush:php;toolbar:false"> 1 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存储过程计算留存率, 感谢原作者分享。