SQL：批量获取次月留存和滚动留存

一、批量获取每月的次月留存率

问题描述：

现在有一份用户活跃数据，想要取每个月的用户在次月的留存，结果如下表

month	活跃用户数	次月留存用户数
2023-01	10000	5000
2023-02	11000	6000
2023-03	15000	7500
...

思路：

先生成每个日期对应的上个月的月份

首先用当前日期减去当前天数，如2023-05-24，就减去24，可以得到上个月的月末：2023-04-30

然后将两个相同的活跃记录表相连，用a表的月份关联b表的上月即可

代码

with tmp as 
(select 
  date,--yyyy-mm-dd格式，如果是yyyymmdd需要转换
  substr(DATE,1,7) AS month,
  SUBSTR(date_sub(date,cast(substr(date,9,2) AS INT)),1,7) AS last_month--计算当前月份的上个月
  user_id
from table_name
where is_new=1#筛选新用户
)
select
a.month,
count(distinct a.user_id) as `当月新增用户数`
count(distinct b.user_id) as `次月留存用户数`
from
  (select user_id,month,last_month
  from tmp)a
left join 
  (select user_id,month,last_month
  from tmp)b
on a.user_id=b.user_id  and a.month=b.last_month
group by a.month

二、批量获取每日滚动30日留存

问题描述：

现在有一份用户活跃数据，想要取每个日滚动30日留存，以T日为例，取T-59~T-30日的活跃用户在 T-29~T的留存率

思路：

1、先生成一段连续的日期作为主表，如果没有现成的表可用，可以参考之前的文章，

先用space()函数生成一些空格；

再用split函数将空格划分；

再用lateral view explode()函数将其转置

再用row_number()over()函数生成序列得到连续数字

再用date_add()在日期上添加连续数字得到连续日期

SQL-不依赖任何表取连续数字和连续日期_格勒王的博客-CSDN博客取连续数字和日期，涉及到的知识点包括：date_add,sapce().split().lateral view explode()，row_number()over()https://blog.csdn.net/weixin_47198715/article/details/130828423?spm=1001.2014.3001.55022、用连续日期关联活跃数据，关联条件为

分母：aa.date>=date_sub(days.dt,59) and aa.date<=date_sub(days.dt,30)
分子：bb.date>=date_sub(days.dt,29) and bb.date<=days.dt and aa.user_id=bb.user_id

代码

select 
days.dt,
count(distinct aa.user_id) ,
count(distinct bb.user_id),
round(count(distinct bb.user_id)/count(distinct aa.user_id),2) as `滚动30日留存率`
from 
  (--取连续日期
  select 
  date_add('2022-12-31',row_number() over( ) ) as dt,
  row_number() over( ) as id 
  from 
  (SELECT SPLIT(SPACE(100),'') AS x)LATERAL VIEW explode(x)a
  )days

left join 
  (--取T-59~t-30日的活跃用户
    select
  date,
  user_id
  FROM table_name)aa on aa.date>=date_sub(days.dt,59) and aa.date<=date_sub(days.dt,30)

left join 

  (--取T-60~t-31日的活跃且在T-29~T还继续活跃的用户数
  select
  date,
  user_id
  FROM table_name)bb on bb.date>=date_sub(days.dt,29) and bb.date<=days.dt and aa.user_id=bb.user_id

group by days.dt