SQL留存率问题

什么是留存率！

-留存率（retention_ rate）通常用来衡量用户或客户的忠诚度和粘性。
-留存率指的是在特定时间段内，有多少人保持了对某个产品、服务、平台或应用程序的使用并继续付费或进行其他有价值的操作。
-通常情况下，留存率会作为一个百分比表示，并根据不同的时间段进行计算。

• 1.直接从数据出发

指标定义

某日活跃用户数:某日活跃的去重用户数。
N日留存用户数:某日活跃的用户在之后的第N日活跃用户数。
N日活跃留存率:N日留存用户数/某日活跃用户数

• 举例：1月1日活跃用户数为100人，这批用户中仍有50人在3日登录了，所以1月1日的三日留存率是50/100=50%

select
    dt               ,
    count(distinct id) as  活跃用户数
    coun
from t
group by dt
order by dt desc

select
       t1.id,
       t1.dt,
       t2.ID,
       t2.dt
from t as t1 left join t as t2
       on t1.ID=t2.ID
       and  date(t1.dt)-date(t2.dt)=1
order by dt desc

1. 可以看到，用两个日期之差等于1来进行连接，空值代表该ID在第二天没有进行登录，用此方法更改两者之差的天数可以求出任意留存的用户数，但是太麻烦了。

select
       t1.dt,
       count(distinct t1.id) as "活跃用户数",
       count(distinct case when date(t1.dt)-date(t2.dt)=1  #相当于上面的 自连接条件
              then t2.id  else null end )                              as  "次日留存用户数"  ,
       count(distinct case when date(t1.dt)-date(t2.dt)=1  #相当于上面的 自连接条件
              then t2.id  else null end )/count(distinct t1.id)       as '次日留存率'
from t as t1 left join t as t2
       on t1.ID=t2.ID
group by t1.dt
order by dt desc

• 以此类推，我们可以求出解决任意留存率问题
  完全代码

select
       t1.dt,
       count(distinct t1.id) as "活跃用户数",
       count(distinct case when date(t1.dt)-date(t2.dt)=1  #相当于上面的 自连接条件
              then t2.id  else null end )                              as  "次日留存用户数" ,
       count(distinct case when date(t1.dt)-date(t2.dt)=2  #相当于上面的 自连接条件
              then t2.id  else null end )                              as  "三日日留存用户数" ,
       count(distinct case when date(t1.dt)-date(t2.dt)=6  #相当于上面的 自连接条件
              then t2.id  else null end )                              as  "七日留存用户数" ,


       count(distinct case when date(t1.dt)-date(t2.dt)=1  #相当于上面的 自连接条件
              then t2.id  else null end )/count(distinct t1.id)       as '次日留存率',
       count(distinct case when date(t1.dt)-date(t2.dt)=2  #相当于上面的 自连接条件
              then t2.id  else null end )/count(distinct t1.id)       as '次日留存率',
       count(distinct case when date(t1.dt)-date(t2.dt)=6  #相当于上面的 自连接条件
              then t2.id  else null end )/count(distinct t1.id)       as '次日留存率'


from t as t1 left join t as t2
       on t1.ID=t2.ID
group by t1.dt
order by dt desc