Gemeinschaft Lernen Tools-Bibliothek Freizeit
suchen
Deutsch
Heim > Datenbank > MySQL-Tutorial > Hauptteil

Mysql存储过程计算留存率

WBOY
Freigeben： 2016-06-07 16:41:39
Original
2328 Leute haben es durchsucht

涉及了 循环、参数定义、游标的使用。 最终代码 ============不区分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();
Nach dem Login kopieren

原文地址：Mysql存储过程计算留存率, 感谢原作者分享。

Verwandte Etiketten：
mysql 参数 存储 定义 循环 留存率 计算 过程
Quelle：php.cn
Vorheriger Artikel：MySQL_Timer mysql并发测试工具 Nächster Artikel：MySQLWorkbench使用SSH Tunnel连接MySQL数据库
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Neueste Artikel des Autors
Aktuelle Ausgaben
So zeigen Sie nur den niedrigsten Wert aus einer Ergebnismenge an (MYSQL) Ich habe die folgende Anweisung: selectDATE(recieved_on)asDay,round(count(*)/24)AS'average...
Aus 2024-04-06 21:44:19
0
1
603
Berechnen Sie die Summe der Felder in einer anderen Tabelle mithilfe einer MySQL-SQL-Abfrage Ich habe ein Schema wie dieses: Benutzertabelle mit den Attributen „user_id“ und „username...
Aus 2024-04-06 19:39:29
0
1
441
MySQL: „Wählen Sie Text aus... als <Variable hier oder Unterabfrage>' Ich habe die folgende Tabelle mit den folgenden Daten: id Textsprache 1 Deutscher Text Deu...
Aus 2024-04-06 19:25:54
0
2
529
Wie gruppiere und zähle ich in MySQL? Ich versuche, eine Abfrage zu schreiben, die die Gesamtzahl der nicht gelöschten Nachricht...
Aus 2024-04-06 18:30:17
0
1
353
MySQL ruft Daten aus mehreren Tabellen ab Ich habe eine Tabelle „eg_design“, die die folgenden Spalten enthält: und eine Tabelle „eg...
Aus 2024-04-06 18:42:44
0
2
479
verwandte Themen
Mehr>
Beliebte Empfehlungen
Beliebte Tutorials
Mehr>
Verwandte Tutorials
Beliebte Empfehlungen
Aktuelle Kurse
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage