mysql 练习

需求：

首先先join获得笛卡儿积的列表：

create table tmp_number 
as
select
   a.step as stepa,a.number as num1,b.step as stepb,b.number as num2
from
   num_table a join num_table b

思路:

首先把rate和retained单独求出来，然后再将两表join，获取有用数据。

总的留存率：

找出有用数据：

select
   a.step as stepa,a.number as num1,b.step as stepb,b.number as num2
from
   num_table a join num_table b
where a.step =="step1";

select
   a.step as stepa,a.number as num1,b.step as stepb,b.number as num2,b.number/a.number as rate
from
   num_table a join num_table b
where a.step =="step1"

retained (step n/step n-1：

思路:在这些数据中，可以找到规律，以四个一组，每组中，都存在step2和step1这样step后面的数字相差1的行，这些就是我们想要的。

select
s2.stepb,s2.num2,s2.num2/s2.num1 as retained
from
(
select
   substring(a.step,5),a.step as stepa,a.number as num1,substring(b.step,5),b.step as stepb,b.number as num2
from
   num_table a join num_table b
where (substring(b.step,5)+'0')-(substring(a.step,5)+'0')==1
or
((substring(b.step,5)+'0')==1 and (substring(a.step,5)+'0')==1)
) s2

将两个查询进行join：

select
t1.stepb,t1.num2,t1.rate,t2.retained
from
(
select
s1.stepb,s1.num2,s1.num2/s1.num1 as rate
from
(
select
   a.step as stepa,a.number as num1,b.step as stepb,b.number as num2
from
   num_table a join num_table b
where a.step =="step1"
) s1
) t1
join
(
select
s2.stepb,s2.num2,s2.num2/s2.num1 as retained
from
(
select
   substring(a.step,5),a.step as stepa,a.number as num1,substring(b.step,5),b.step as stepb,b.number as num2
from
   num_table a join num_table b
where (substring(b.step,5)+'0')-(substring(a.step,5)+'0')==1
or
((substring(b.step,5)+'0')==1 and (substring(a.step,5)+'0')==1)
) s2
) t2
on
t1.num2 == t2.num2