当前位置：Gxlcms > mysql > Mysql存储过程计算留存率

Mysql存储过程计算留存率

时间：2021-07-01 10:21:17 帮助过：9人阅读

涉及了 循环、参数定义、游标的使用。 最终代码 ============不区分org的总数据===DELIMITER $$DROP PROCEDURE IF EXISTS weekly_remain $$#IN参数类型，传进的参数CREATE PROCEDURE weekly_remain(IN lobby int) BEGIN #定义参数declare i int(2);declare e

涉及了 循环、参数定义、游标的使用。

最终代码

  1. ============不区分org的总数据===
  2. DELIMITER $$
  3. DROP PROCEDURE IF EXISTS weekly_remain $$
  4. #IN参数类型，传进的参数
  5. CREATE PROCEDURE weekly_remain(IN lobby int)  
  6. BEGIN  
  7. #定义参数
  8. declare i int(2);
  9. declare ext_table varchar(20);
  10. declare uniq_no int(10);
  11. declare now_week varchar(20);
  12. declare c2 varchar(10);
  13. declare c3 varchar(10);
  14. declare c4 varchar(10);
  15. declare c5 varchar(10);
  16. declare c6 varchar(10);
  17. SET now_week = date_format(date_sub(curdate(),interval 1 week),"%Y-%u");
  18. SET i = 1;
  19. if (lobby=101) or (lobby=102) or (lobby=104) or(lobby=105) or (lobby=107) or (lobby=108) then
  20. #引入参数@c，局部变量
  21. select count(distinct(stbid)) INTO @c from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week and gate_uri=lobby;
  22. SET uniq_no= @c;
  23. 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;
  24. 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;
  25. SET c2=@c_2/uniq_no;
  26. update gp_weekly_leave set two_week=c2 where week=now_week and lobby_id=lobby;
  27. 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;
  28. SET c3=@c_3/uniq_no;
  29. update gp_weekly_leave set three_week=c3 where week=now_week and lobby_id=lobby;
  30. 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;
  31. SET c4=@c_4/uniq_no;
  32. update gp_weekly_leave set four_week=c4 where week=now_week and lobby_id=lobby;
  33. 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;
  34. SET c5=@c_5/uniq_no;
  35. update gp_weekly_leave set five_week=c5 where week=now_week and lobby_id=lobby;
  36. 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;
  37. SET c6=@c_6/uniq_no;
  38. update gp_weekly_leave set six_week=c6 where week=now_week and lobby_id=lobby;
  39. end if;
  40. if (lobby=1000) then
  41. select count(distinct(stbid)) INTO @c from gp_gate_login_recent where date_format(login_time,"%Y-%u")=now_week;
  42. SET uniq_no= @c;
  43. 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;
  44. 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;
  45. SET c2=@c_2/uniq_no;
  46. update gp_weekly_leave set two_week=c2 where week=now_week and lobby_id=lobby;
  47. 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;
  48. SET c3=@c_3/uniq_no;
  49. update gp_weekly_leave set three_week=c3 where week=now_week and lobby_id=lobby;
  50. 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;
  51. SET c4=@c_4/uniq_no;
  52. update gp_weekly_leave set four_week=c4 where week=now_week and lobby_id=lobby;
  53. 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;
  54. SET c5=@c_5/uniq_no;
  55. update gp_weekly_leave set five_week=c5 where week=now_week and lobby_id=lobby;
  56. 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;
  57. SET c6=@c_6/uniq_no;
  58. update gp_weekly_leave set six_week=c6 where week=now_week and lobby_id=lobby;
  59. end if;
  60. END
  61. $$
  62. DELIMITER ;
  63. --#====拓展--存储过程--区分orgid===#--
  64. DELIMITER $$
  65. DROP PROCEDURE IF EXISTS weekly_remain_tz $$
  66. CREATE PROCEDURE weekly_remain_tz()  
  67. BEGIN  
  68. #默认值
  69. declare lobby int(5) default 104;
  70. declare i int(2);
  71. declare ext_table varchar(20);
  72. declare uniq_no int(10);
  73. declare now_week varchar(20);
  74. declare c2 varchar(10);
  75. declare c3 varchar(10);
  76. declare c4 varchar(10);
  77. declare c5 varchar(10);
  78. declare c6 varchar(10);
  79. declare a int(10);
  80. declare b varchar(20);
  81. declare leave2 varchar(20);
  82. declare leave3 varchar(20);
  83. declare leave4 varchar(20);
  84. declare leave5 varchar(20);
  85. DECLARE leave6 varchar(20);
  86. DECLARE percent2 varchar(20);
  87. DECLARE percent3 varchar(20);
  88. DECLARE percent4 varchar(20);
  89. DECLARE percent5 varchar(20);
  90. DECLARE percent6 varchar(20);
  91. #游标定义与开始
  92. DECLARE s int default 0;
  93. #定义游标
  94. 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;
  95. #设置
  96. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;
  97. SET now_week = date_format(date_sub(curdate(),interval 1 week),"%Y-%u");
  98. SET i = 1;
  99. #先插入数据
  100. 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;
  101. #开启游标
  102. OPEN cursor_name;
  103. #对每一行的数据进行轮训
  104. fetch  cursor_name into a,b;
  105. while s <pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li> 1 do  </li><li>#进行2周、3周、4周、5周、6周留存的计算</li><li>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;</li><li>SET percent2=leave2/b;</li><li>update gp_weekly_leave set two_week=percent2 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent3=leave3/b;</li><li>update gp_weekly_leave set three_week=percent3 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent4=leave4/b;</li><li>update gp_weekly_leave set four_week=percent4 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent5=leave5/b;</li><li>update gp_weekly_leave set five_week=percent5 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent6=leave6/b;</li><li>update gp_weekly_leave set six_week=percent6 where orgid=a and lobby_id=lobby and week=now_week;                    </li><li>fetch  cursor_name into a,b;                      </li><li>end while;   </li><li>#关闭游标</li><li>CLOSE cursor_name ;</li><li>END</li><li>$$</li><li>DELIMITER ;</li><li>--#====杭州--存储过程--区分orgid===#--</li><li>DELIMITER $$</li><li>DROP PROCEDURE IF EXISTS weekly_remain_hz $$</li><li>CREATE PROCEDURE weekly_remain_hz()  </li><li>BEGIN  </li><li>declare lobby int(5) default 101;</li><li>declare i int(2);</li><li>declare ext_table varchar(20);</li><li>declare uniq_no int(10);</li><li>declare now_week varchar(20);</li><li>declare c2 varchar(10);</li><li>declare c3 varchar(10);</li><li>declare c4 varchar(10);</li><li>declare c5 varchar(10);</li><li>declare c6 varchar(10);</li><li>declare a int(10);</li><li>declare b varchar(20);</li><li>declare leave2 varchar(20);</li><li>declare leave3 varchar(20);</li><li>declare leave4 varchar(20);</li><li>declare leave5 varchar(20);</li><li>DECLARE leave6 varchar(20);</li><li>DECLARE percent2 varchar(20);</li><li>DECLARE percent3 varchar(20);</li><li>DECLARE percent4 varchar(20);</li><li>DECLARE percent5 varchar(20);</li><li>DECLARE percent6 varchar(20);</li><li>DECLARE s int default 0;</li><li>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;</li><li>DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;</li><li>SET now_week = date_format(date_sub(curdate(),interval 1 week),"%Y-%u");</li><li>SET i = 1;</li><li>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;</li><li>OPEN cursor_name;</li><li>fetch  cursor_name into a,b;</li><li>while s <pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li> 1 do  </li><li>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;</li><li>SET percent2=leave2/b;</li><li>update gp_weekly_leave set two_week=percent2 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent3=leave3/b;</li><li>update gp_weekly_leave set three_week=percent3 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent4=leave4/b;</li><li>update gp_weekly_leave set four_week=percent4 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent5=leave5/b;</li><li>update gp_weekly_leave set five_week=percent5 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent6=leave6/b;</li><li>update gp_weekly_leave set six_week=percent6 where orgid=a and lobby_id=lobby and week=now_week;                    </li><li>fetch  cursor_name into a,b;                      </li><li>end while;   </li><li>CLOSE cursor_name ;</li><li>END</li><li>$$</li><li>DELIMITER ;</li><li>--#====省网--存储过程--区分orgid===#--</li><li>DELIMITER $$</li><li>DROP PROCEDURE IF EXISTS weekly_remain_sw $$</li><li>CREATE PROCEDURE weekly_remain_sw()  </li><li>BEGIN  </li><li>declare lobby int(5) default 102;</li><li>declare i int(2);</li><li>declare ext_table varchar(20);</li><li>declare uniq_no int(10);</li><li>declare now_week varchar(20);</li><li>declare c2 varchar(10);</li><li>declare c3 varchar(10);</li><li>declare c4 varchar(10);</li><li>declare c5 varchar(10);</li><li>declare c6 varchar(10);</li><li>declare a int(10);</li><li>declare b varchar(20);</li><li>declare leave2 varchar(20);</li><li>declare leave3 varchar(20);</li><li>declare leave4 varchar(20);</li><li>declare leave5 varchar(20);</li><li>DECLARE leave6 varchar(20);</li><li>DECLARE percent2 varchar(20);</li><li>DECLARE percent3 varchar(20);</li><li>DECLARE percent4 varchar(20);</li><li>DECLARE percent5 varchar(20);</li><li>DECLARE percent6 varchar(20);</li><li>DECLARE s int default 0;</li><li>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;</li><li>DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;</li><li>SET now_week = date_format(date_sub(curdate(),interval 1 week),"%Y-%u");</li><li>SET i = 1;</li><li>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;</li><li>OPEN cursor_name;</li><li>fetch  cursor_name into a,b;</li><li>while s <pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li> 1 do  </li><li>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;</li><li>SET percent2=leave2/b;</li><li>update gp_weekly_leave set two_week=percent2 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent3=leave3/b;</li><li>update gp_weekly_leave set three_week=percent3 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent4=leave4/b;</li><li>update gp_weekly_leave set four_week=percent4 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent5=leave5/b;</li><li>update gp_weekly_leave set five_week=percent5 where orgid=a and lobby_id=lobby and week=now_week;</li><li>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;</li><li>SET percent6=leave6/b;</li><li>update gp_weekly_leave set six_week=percent6 where orgid=a and lobby_id=lobby and week=now_week;                    </li><li>fetch  cursor_name into a,b;                      </li><li>end while;   </li><li>CLOSE cursor_name ;</li><li>END</li><li>$$</li><li>DELIMITER ;</li><li>===========TIPS===</li><li>---执行方法，不区分org 则调用weekly_remain(lobbyid)  101 102 104 105 107 108：</li><li>CALL weekly_remain(1000);</li><li>---区分orgid 调用:</li><li>CALL weekly_remain_hz();</li><li>CALL weekly_remain_sw();</li><li>CALL weekly_remain_tz();</li><li></li></ol></pre></li><li>    <p class="copyright"></li><li>        原文地址：Mysql存储过程计算留存率, 感谢原作者分享。                    </p></li></ol></pre></li></ol></pre>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！