提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

SQL速算N日留存

计算 SQL
2023-02-25 18:27:38 时间

大家好，我是小小明。

之前才哥发布了《用SQL进行用户留存率计算》

链接：用SQL进行用户留存率计算

简化数据后得到如下结构的数据：

由于用户和登录日期被设置为主键所以不需要再进行去重，下面看看如何快速求七日留存。（阅读原文可以直达CSDN本文链接）

数据下载地址：https://gitcode.net/as604049322/blog_data/-/blob/master/role_login.sql

首先我们求每个用户的安装日期和第几天仍然再登录：

select 
 role_id,
 install_date,
 datediff(b.event_date,install_date) day_diff
from (
 select
  role_id,min(event_date) install_date
 from role_login group by role_id
) a join role_login b using(role_id);

然后我们一口气求得七日留存：

with cte as (
 select 
  role_id,
  install_date,
  datediff(b.event_date,install_date) day_diff
 from (
  select
   role_id,min(event_date) install_date
  from role_login group by role_id
 ) a join role_login b using(role_id)
)
select 
 install_date,
 sum(day_diff=0) 新增用户数,
 sum(day_diff=1) 次日留存,
 sum(day_diff=2) 2日留存,
 sum(day_diff=3) 3日留存,
 sum(day_diff=4) 4日留存,
 sum(day_diff=5) 5日留存,
 sum(day_diff=6) 6日留存,
 sum(day_diff=7) 7日留存,
 concat(round(sum(day_diff=1)*100/sum(day_diff=0),2),"%") 次日留存率,
 concat(round(sum(day_diff=2)*100/sum(day_diff=0),2),"%") 2日留存率,
 concat(round(sum(day_diff=3)*100/sum(day_diff=0),2),"%") 3日留存率,
 concat(round(sum(day_diff=4)*100/sum(day_diff=0),2),"%") 4日留存率,
 concat(round(sum(day_diff=5)*100/sum(day_diff=0),2),"%") 5日留存率,
 concat(round(sum(day_diff=6)*100/sum(day_diff=0),2),"%") 6日留存率,
 concat(round(sum(day_diff=7)*100/sum(day_diff=0),2),"%") 7日留存率
from cte where day_diff<=7
group by 1 order by 1;

可以看到，就这样轻松的计算出了7日留存率。按照上面SQL的思路可以轻松任意日的留存率。

本题相当于《SQL刷题宝典-MySQL速通力扣困难题》一文中，其他-》游戏玩法分析5的扩展。

更全面的各类SQL题，详见：

“SQL刷题宝典-MySQL速通力扣困难题 https://xxmdmst.blog.csdn.net/article/details/128509713 ”

相关文章