用SQL进行用户留存率计算

今天我们来分享一个常见案例，用SQL来计算用户留存率。

目录：

• 1. 案例数据

• 2. 思路分析

• 3. 完整代码

这里我们一共两份日志数据，分别是用户账号创建的日志以及用户登录的日志。

账号创建日志

用户登录日志

所谓留存，就是指某日创建的账号在后续自然日登录的比例，比如3月1日新增账号创建数为100，在3月2日这部分用户登录数为51，那么3月1日新增用户的次日留存率为51/100=51%。

注意：我这里用的MYSQL环境

基于上述的理解，我们大概就有了以下思路：

• 考虑到用户每天登录的次数不一定只有一次，为了方面后续的数据处理，可以先对登录数据按照日期和用户id进行去重DISTINCT处理

SELECT DISTINCT
 STR_TO_DATE( $part_date, '%Y-%m-%d' ) login_date,
 role_id 
FROM
 role_login

• 为了计算某条登录日志是该用户创建账号后的第几天登录，我们可以用用户登录日志和账号创建日志进行inner join（这里考虑到不在统计周期内的创建账号的用户数据也会记录在用户登录日志里，所以去掉）

SELECT
 login_log.role_id,
 create_date,
 login_date
FROM
 ((
  SELECT DISTINCT
   STR_TO_DATE( $part_date, '%Y-%m-%d' ) login_date,
   role_id 
  FROM
   role_login 
  ) login_log
 INNER JOIN ( SELECT DISTINCT STR_TO_DATE( $part_date, '%Y-%m-%d' ) create_date, role_id FROM role_create ) create_log ON ( login_log.role_id = create_log.role_id ))

• 然后用登录日期字段和创建账户字段进行差值DATEDIFF获取第几天登录

SELECT
 login_log.role_id,
 create_date,
 DATEDIFF( login_date, create_date ) day_diff 
FROM
...

• 对于第0天登录的数据则可以理解为新增用户数，第N（≥1)天登录的数据则为这批新增用户后续有登录的用户数

SELECT
 create_date 
, count((CASE WHEN (day_diff = 0) THEN role_id END)) 新增用户数
, count((CASE WHEN (day_diff = 1) THEN role_id END)) 次日留存
, count((CASE WHEN (day_diff = 2) THEN role_id END)) 3日留存
, count((CASE WHEN (day_diff = 7) THEN role_id END)) 7日留存
FROM
temp_1
GROUP BY
 create_date

• 用第N天登录的数据 / 新增用户数  就是对应第N天留存率

SELECT
  create_date
, 新增用户数
, concat(CAST(ROUND((100 * 次日留存) / 新增用户数,2) AS char), '%') 次日留存率
, concat(CAST(ROUND((100 * 3日留存) / 新增用户数,2) AS char), '%') 3日留存率
, concat(CAST(ROUND((100 * 7日留存) / 新增用户数,2) AS char), '%') 7日留存率
FROM
  (
   SELECT
     create_date
   , count((CASE WHEN (day_diff = 0) THEN role_id END)) 新增用户数
   , count((CASE WHEN (day_diff = 1) THEN role_id END)) 次日留存
   , count((CASE WHEN (day_diff = 2) THEN role_id END)) 3日留存
   , count((CASE WHEN (day_diff = 7) THEN role_id END)) 7日留存
   FROM
     (
      SELECT
        login_log.role_id
      , create_date
      , DATEDIFF(login_date, create_date) day_diff
      FROM
        ((
         SELECT DISTINCT
           STR_TO_DATE($part_date, '%Y-%m-%d') login_date
         , role_id
         FROM
           role_login
      )  login_log
      INNER JOIN (
         SELECT DISTINCT
           STR_TO_DATE($part_date, '%Y-%m-%d') create_date
         , role_id
         FROM
           role_create
      )  create_log ON (login_log.role_id = create_log.role_id))
   )  temp_1
   GROUP BY create_date
)  temp_2
ORDER BY create_date ASC