留存率怎么计算？

背景引入

留存率的计算是数据开发工作中常见中常见的字段需求，同时也是面试sql题考察的重中之重。
首先，先要明白留存率是什么，百度的解释是：其具体含义为在统计周期（周/月）内，每日活跃用户数在第N日仍启动该App的用户数占比的平均值，其实简单点来说，拿次留率来举例子的话就是今天这批在线的用户数量作为分母，明天这批用户仍在线的用户数量作为分子。

解决思路

从留存率的定义入手，我们可以知道这个指标的计算肯定是涉及到今天与其他天（比如明天）的数据之间的关系的，是否就意味着对表进行自连接之后，可以得到相应的数据帮助我们计算留存率指标呢？

方法一

注意点

注意我这里把data中的date字段数据由’20210103’的格式改为’2021-01-03’。
这种方法会有一个注意事项是：hive会认为’20210103’这样的日期是普通字符串格式，将其作为参数传入到datediff函数中会有歧义，所以datediff的传入参数一定是标准的date格式’2021-01-03’。

with data as (
  select 1 as uid, '2021-01-03' as `date` union all
  select 1 as uid, '2021-01-04' as `date` union all
  select 1 as uid, '2021-01-05' as `date` union all
  select 1 as uid, '2021-01-06' as `date` union all
  select 1 as uid, '2021-01-07' as `date` union all
  select 2 as uid, '2021-01-03' as `date` union all
  select 2 as uid, '2021-01-04' as `date` union all
  select 2 as uid, '2021-01-05' as `date` union alls
  select 2 as uid, '2021-01-06' as `date` union all
  select 2 as uid, '2021-01-07' as `date` union all
  select 2 as uid, '2021-01-08' as `date` union all
  select 2 as uid, '2021-01-09' as `date` union all
  select 2 as uid, '2021-01-10' as `date`
),
data2 as (
  select
  uid,
  concat(substring(`date`,1,4),'-',substring(`date`,5,2),'-',substring(`date`,7,2)) as `date`
  from data
)
select
    a.`date` as d ,
    sum((case when datediff(b.`date`,a.`date`) = 1 then 1 else 0 end))/ count(distinct a.uid) as liucunlv_1,
    sum((case when datediff(b.`date`,a.`date`) = 7 then 1 else 0 end))/ count(distinct a.uid) as liucunlv_7
from data a left join data b on a.uid = b.uid and a.`date` < b.`date`
group by a.`date`;

运行结果：

方法二

这种方法更加直观易懂，同时也是我在实际工作中遇到的实际解决方法。
但是带来的一点思考是，这里多了个left join，是不是性能不如方法一呢？

with data as (
  select 1 as uid, '2021-01-03' as `date` union all
  select 1 as uid, '2021-01-04' as `date` union all
  select 1 as uid, '2021-01-05' as `date` union all
  select 1 as uid, '2021-01-06' as `date` union all
  select 1 as uid, '2021-01-07' as `date` union all
  select 2 as uid, '2021-01-03' as `date` union all
  select 2 as uid, '2021-01-04' as `date` union all
  select 2 as uid, '2021-01-05' as `date` union all
  select 2 as uid, '2021-01-06' as `date` union all
  select 2 as uid, '2021-01-07' as `date` union all
  select 2 as uid, '2021-01-08' as `date` union all
  select 2 as uid, '2021-01-09' as `date` union all
  select 2 as uid, '2021-01-10' as `date`
),
data2 as (
  select
  uid,
  concat(substring(`date`,1,4),'-',substring(`date`,5,2),'-',substring(`date`,7,2)) as `date`
  from data
)
select
    a.`date`,
    count(distinct b.uid) / count(distinct a.uid) as liucunlv_1,
    count(distinct c.uid) / count(distinct a.uid) as liucunlv_7
from data a left join data b on a.uid = b.uid and date_add(a.`date`,1) = b.`date`
    left join data c on a.uid = c.uid and date_add(a.`date`,7) = c.`date`
group by a.`date`;

运行结果：