如何用一段sql实现留存率的计算

一段sql代码实现n天的留存取数，不用写那么多得count（distinct case when 条件 a then uid end）去增加每天的留存分析情况，而且提升运算速度。

步骤一：明确分析对象

是否区分新老用户的留存情况，这里拿全部用户的留存情况举例子，具体情况还需具体分析。

步骤二：数据预处理

一般情况下，流量数据都是存放在流量日志表内的，包含每个用户多次的访问记录和埋点信息，我们只需找出每个用户在每天是否访问就够了，对于这种情况，我们需要对底表进行预先处理。

那如何处理呢？代码如下：

 

-- 方法一：直接group by 
 --如表内有明确的日期信息，格式类似：2022-12-31 或20221231
select uid ,--替换成表内的用户id名称  
	   dt
	   from liuliangrizhibiao --表名选中自己的表名
	   where pt >= '${-90d_pt}'  
	   group by uid ,dt
	   
--如表内没有有明确的日期信息，只有活跃时间，格式类似：2022-12-31 12:12:30


select uid ,--替换成表内的用户id名称  
	   ,to_date(concat_ws('-', substr(time, 1, 4), substr(time, 5, 2), substr(time, 7, 2))) AS  dt
	   from liuliangrizhibiao --表名选中自己的表名
	   where pt >= '${-90d_pt}'  
	   group by uid ,dt     



--方法二：row_number排序 
 select a.uid,a.dt
	 from (
	   select uid ,--替换成表内的用户id名称  
	   dt,row_number() OVER (PARTITION BY uid,dt ORDER BY dt ) AS rank  --找出每个用户每天第一次访问记录
	   from liuliangrizhibiao --表名选中自己的表名
	   where pt >= '${-90d_pt}'  
	 ) a
	 where a.rank =1
--如表内没有有明确的日期信息,方法和上诉一致。

一般可以将数据预处理的部分用with as子查询，相当于建立了一个临时表，提升运算效率。

步骤三：留存率计算

1、计算分母：每日的dau情况

SELECT dt
	,count(DISTINCT uid) AS uv  -- 每天活跃用户量
	FROM dau
	group by dt

2、计算分子：后续每天留存的用户数，然后用左关联连接上下两端代码，用dt做关联条件。完整代码

SELECT c.dt,c.day_diff,count(c.uid) AS retention_num --后续每天留存用户数
	from (
	select a.dt,b.uid, datediff(b.dt,a.dt) AS day_diff  
	FROM (
		SELECT uid,dt
		FROM dau
		) a
	LEFT JOIN (
		SELECT uid,dt
		FROM dau
		) b 
		ON a.uid = b.uid
		and a.dt <=b.dt
		)c
	GROUP BY c.dt,c.day_diff

完成代码如下：

	with dau as 
(   
	 select a.uid,a.dt
	 from (
	   select uid ,--替换成表内的用户id名称  
	   dt,row_number() OVER (PARTITION BY uid,dt ORDER BY dt ) AS rank  --找出每个用户每天第一次访问记录
	   from liuliangrizhibiao --表名选中自己的表名
	   where pt >= '${-90d_pt}'  
	 ) a
	 where a.rank =1
	 )
SELECT aa.dt as `first_day`
	,bb.day_diff   as `retention_daydiff`
	,date_add(aa.dt ,bb.day_diff) as `retention_day`
	,aa.uv as `first_day_dau`
	,bb.retention_num as `retention_num`
	,concat(round(bb.retention_num/aa.uv *100,4),"%")as `retention_rate` 
FROM (
	SELECT dt
		,count(DISTINCT uid) AS uv  --每天活跃用户量
	FROM dau
	group by dt
	) aa
LEFT JOIN (
	SELECT c.dt,c.day_diff,count(c.uid) AS retention_num --后续每天留存用户数
	from (
	select a.dt,b.uid, datediff(b.dt,a.dt) AS day_diff
	FROM (
		SELECT uid,dt
		FROM dau
		) a
	LEFT JOIN (
		SELECT uid,dt
		FROM dau
		) b 
		ON a.uid = b.uid
		and a.dt <=b.dt
		)c
	GROUP BY c.dt,c.day_diff
	) bb
	ON aa.dt = bb.dt
ORDER BY aa.dt,bb.day_diff 

最后结果预览

然后利用excel的数据透视表，就可以完成Cohort模型的分析，之后可以用于用户召回等方面。