欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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 s 
 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 
 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 
 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();