SQL模板-用户留存率计算

TIM33470348

已于 2023-09-19 18:12:18 修改

阅读量1k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： Python-数据开发和数据分析 Database-SQL的学习文章标签：数据库

于 2023-09-19 16:34:53 首次发布

本文链接：https://blog.csdn.net/qq_41938259/article/details/133034174

Python-数据开发和数据分析同时被 2 个专栏收录

41 篇文章

订阅专栏

Database-SQL的学习

10 篇文章

订阅专栏

本文介绍了互联网行业中的用户留存概念，区分了新增和活跃用户留存，并给出了1到90天用户留存率的计算方法，以及用SQL查询实现的示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在这段实习中，我遇到了用户留存率计算的需求，这里做个总结。

首先来讲下，什么是用户留存？

在互联网行业中，用户在某段时间内开始使用应用，经过一段时间后，仍然继续使用该应用的用户。用户留存一般区分新增用户留存和活跃用户留存。通过区分不同属性的用户留存状况，对不同用户采取精细的运营策略。在用户基础上，还可通过时间维度进一步区分次日留存、周留存、月留存。

我们需要计算1到90天的用户留存率，经过上面的介绍，第N天用户留存率的计算公式就显而易见了：

我们设“第N天的用户留存率”为 $rate$ ，“第N天登录的用户总数”为 $a_{n}$ ,“第一天创建账号的用户总数”为 $a_{1}$ ，则用户留存率的计算公式如下：

$rate=\frac{a_{n}}{a_{1}}$

于是，我们可以得到如下的代码。其中，dt是登录/创号的日期，格式是yyyy-mm-dd。open_id是唯一的用户标识。表user_create_raw是记录用户创号信息的表，role_login_in是记录用户登陆信息的表，精确到一个账号的其中一个游戏角色。大致SQL如下：

SELECT a.dt, COUNT(DISTINCT a.open_id), COUNT(DISTINCT r.open_id), cast(COUNT(DISTINCT r.open_id) as double)/COUNT(DISTINCT a.open_id)*100 rate
FROM (
  SELECT open_id, MAX(dt) dt
  FROM user_create_raw u
  WHERE dt='2023-09-15' 
  GROUP BY open_id
)a
LEFT JOIN role_login_raw r ON a.open_id=r.open_id AND DATEDIFF(r.dt, a.dt)=1
GROUP BY a.dt

以上就是这么多，希望自己再接再厉。