玩家留存计算的两种SQL语句

留存简单来说就是某天的注册用户有多少人在多少天后还在继续使用，
但具体的定义各个公司可能都不太一样，
比如三日留存是在第四天还有登陆的玩家，一天是按现实的0点为分界还是一个玩家完整的24小时计算等。
在分析数据前请和运营沟通确定好具体的标准。
本篇按照现实的0点为一天的分割，次日留存为第二天登陆的玩家，三日留存为第4日登陆的玩家。
第一种sql：
直接按照定义，找到第一天的注册用户id，
然后在第二天的登陆用户中找这些注册id，还存在的即为次日留存用户，
在第四天的登陆用户中找这些注册id，还存在的为三日留存用户等。。。

WITH
-- 找出在12/1号注册的用户
  regist AS (
  SELECT
    DISTINCT uid
  FROM
    注册表
  WHERE
    DATE(ts) = '2020-12-1'),
-- 找出次日登陆的用户
  l1 AS (
  SELECT
    DISTINCT uid
  FROM
    登陆表
  WHERE
    DATE(ts) = '2020-12-2' )
SELECT
-- 统计当天注册人数
  COUNT(regist.uid) AS regist_user,
-- 统计次日登陆人数
  COUNT(l1.uid) AS retention_D1,
-- 次日登陆/当天注册得到次日留存率
  FORMAT('%.4f',COUNT(l1.uid)/COUNT(regist.uid)) AS D1_rate
FROM
-- 用注册表左连登陆表，获得次日登陆过的用户
  regist
LEFT JOIN
  l1
ON
  regist.uid = l1.uid

1. 每次查询必须连表，而且要先确定时间。
2. 一次只能查某一天的某一个留存， 比如要查12/1至7号每天的次留，3留就很麻烦。

第二种是先连表，然后再用登陆时间减去注册时间得到一个时间差，通过筛选时间差来得到固定的留存人数。

-- 获取用户uid和登陆的日期
WITH
  login AS (
      SELECT
        uid,
        DATE(ts, '+08') AS ldate
      FROM
        `登陆表`
-- 每个用户每天有多次登陆时间，每天只需要保留一个即可，可以减少连表的行数
      GROUP BY  1,2)
SELECT
      regist.ts,
      regist.uid,
-- 用登录时间减去注册时间得到天数差
      DATE_DIFF(login.ldate, DATE(regist.ts, '+08'), DAY) AS delta
FROM
      login 
INNER JOIN
      `注册表`  AS regist
ON
      login.uid = regist.uid
-- 这里其实不加group by也可以，因为上面login已经去过重了，和regist连表后也不会重复，
-- 但防止意外的情况，比如一个用户的regist时间出现两次什么的。
GROUP BY 1,2,3

这样就得到了一个有所用户的注册时间和登陆天数的表了，再在上面做查询就可以很容易的找到某天的留存

SELECT
  DATE(retention.ts),
  COUNT(DISTINCT retention.uid) AS d1_user_num,
  COUNT(DISTINCT retention.uid)/COUNT(DISTINCT regist.uid) AS d1_rate
FROM
  -- 可以单独导出为一个表或视图，也可以用with写成一个子句
  `上面的留存表` AS retention,
  `注册表` AS regist
WHERE
  DATE(retention.ts) BETWEEN '2020-12-1'
  AND '2020-12-7'
  AND delta=1
  AND DATE(regist.ts) = '2020-12-1'
GROUP BY
  1
ORDER BY
  1