前言
在学 HQL 之前是不太了解 SQL 的，以为 SQL 只可以实现 CRUD ，直到面试的公司让我下去多了解一些 SQL ，我才最近开始再次深入学习 MySQL 和 Oracle。而且越学越发现 SQL 真的是一门很有深度的语言，我以前的使用只是皮毛而已，自惭形秽之下决定必须深入学习 SQL ，毕竟大数据工作之后主要就是 SQL 嘛。
关于留存率问题是一个面试的重点，面一个暑期实习都几乎每次都被问，每次问到留存率我都试图通过障耳法搞晕面试官，结果事实上面试官都能准确发现我回答中的问题。所以不能再拖了，留存率的问题应该好好拿出来深入理解掌握了。
1、留存率问题
1.1、留存率的定义
留存率：用户在注册之后的第 N 天仍然登录的比例，称为第 N 日留存率。
1.2、数据准备
下面我们通过 SQL 先创建一些数据（用户数据 5000条、登录数据 50w条）：
-- 留存率问题 -- 示例表 DROP TABLE IF EXISTS t_user; CREATE TABLE t_user( id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(50) NOT NULL, register_time DATETIME NOT NULL ); SET SESSION cte_max_recursion_depth=9999999; INSERT INTO t_user(user_name, register_time) WITH RECURSIVE t AS ( SELECT 1 n, '2022-01-01 00:00:00' d UNION ALL SELECT n+1, d + INTERVAL '1' MINUTE FROM t WHERE n<10000 ) SELECT concat('user', n), d FROM t; SELECT date(register_time), count(*) FROM t_user GROUP BY date(register_time); DROP TABLE IF EXISTS t_user_login; CREATE TABLE t_user_login( id INT AUTO_INCREMENT PRIMARY KEY, uid INT NOT NULL, login_time DATETIME NOT NULL ); SET SESSION cte_max_recursion_depth=9999999; SELECT @@cte_max_recursion_depth; INSERT INTO t_user_login(uid, login_time) WITH RECURSIVE t AS ( SELECT 1 n, rand()*10000 id, '2022-01-01 00:00:00' d UNION ALL SELECT n+1, rand()*10000, d + INTERVAL CEIL(n/3000) second FROM t WHERE n<500000 ) SELECT CEIL(id), d FROM t;
1.3、计算留存率
1.3.1、思路1：多表连接
我们先计算 1月1日的近一日留存率：
SELECT COUNT(DISTINCT tul.uid)/COUNT(DISTINCT tu.id) rr1 FROM t_user tu LEFT JOIN t_user_login tul ON (tul.uid = tu.id AND DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL '1' DAY) WHERE tu.register_time BETWEEN '2022-01-01 00:00:00' AND '2022-01-01 23:59:59';
根据这个思路，我们可以同样实现多日的留存率：
SELECT date(u.register_time), 100*count(DISTINCT ul1.uid)/count(DISTINCT u.id) rr1, 100*count(DISTINCT ul2.uid)/count(DISTINCT u.id) rr3, 100*count(DISTINCT ul3.uid)/count(DISTINCT u.id) rr7, 100*count(DISTINCT ul4.uid)/count(DISTINCT u.id) rr30 FROM t_user u LEFT JOIN t_user_login ul1 ON (ul1.uid = u.id AND date(ul1.login_time) = date(u.register_time) + INTERVAL '1' DAY) LEFT JOIN t_user_login ul2 ON (ul2.uid = u.id AND date(ul2.login_time) = date(u.register_time) + INTERVAL '3' DAY) LEFT JOIN t_user_login ul3 ON (ul3.uid = u.id AND date(ul3.login_time) = date(u.register_time) + INTERVAL '7' DAY) LEFT JOIN t_user_login ul4 ON (ul4.uid = u.id AND date(ul4.login_time) = date(u.register_time) + INTERVAL '30' DAY) GROUP BY date(u.register_time);
运行结果：
| date(u.register_time) | rr1 | rr3 | rr7 | rr30 |
| 2022-01-01 | 61.1806 | 45.4167 | 34.5833 | 18.9583 |
| 2022-01-02 | 50.0000 | 43.6806 | 32.2917 | 18.4028 |
| 2022-01-03 | 44.5139 | 38.7500 | 31.4583 | 18.5417 |
| 2022-01-04 | 43.0556 | 36.1111 | 29.5139 | 16.7361 |
| 2022-01-05 | 38.1944 | 34.6528 | 29.3056 | 16.3194 |
| 2022-01-06 | 35.4167 | 33.3333 | 28.1944 | 18.1944 |
| 2022-01-07 | 34.4118 | 29.9265 | 27.5735 | 17.6471 |
1.3.2、思路2：窗口函数
需要细心的地方就是每个聚合函数的粒度（group by 哪些字段），不同的粒度代表的含义是完全不一样的（比如 group by register_date,login_date 和 group by register_date 是不同的粒度，前者的最细粒度是 login_date，后者的最细粒度是 register_date）
WITH t1 AS( SELECT tu.id,tul.uid,DATE(tu.register_time) reg_date,DATE(tul.login_time) login_date, DENSE_RANK() OVER (PARTITION BY DATE(tu.register_time) ORDER BY tu.id) reg_rk, -- 按照用户注册日期进行分区 并按照用户id进行排名 得到的max(排名)就是当天注册的总人数 DENSE_RANK() OVER (PARTITION BY DATE(tu.register_time),DATE(tul.login_time) ORDER BY tul.uid) log_rk -- max(排名)就是当天登录的人数 FROM t_user tu LEFT JOIN t_user_login tul ON (tu.id = tul.uid AND(DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL 1 DAY OR DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL 3 DAY OR DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL 7 DAY OR DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL 30 DAY )) ), t2 AS ( -- 这里的 MAX(reg_rk) 并不代表 reg_date 这一天注册的人数，因为它是按照 reg_date 和 login_date 共同分组的 -- 所以最细粒度是 log_date 而不是 reg_date，所以当只按照 reg_date 分组时得到的才是 -- 所以这里得到的结果中 log_sum 可以代表reg_date注册后的在log_date登录的用户数，而 reg_sum 并不能代表reg_date注册的总人数 SELECT reg_date, login_date, MAX(reg_rk) reg_sum, MAX(log_rk) log_sum FROM t1 GROUP BY reg_date,login_date -- 因为是 left join 所以会保留所有注册信息，这里同时按照 login_date 分组就会把 login_date为null的数据排除在统计范围之外 ) SELECT reg_date, MAX(reg_sum), -- 这里的max的粒度才是reg_date,得到才是正确的注册人数 MAX(CASE WHEN login_date = reg_date + INTERVAL 1 DAY THEN log_sum END)/MAX(reg_sum) rr1, MAX(CASE WHEN login_date = reg_date + INTERVAL 3 DAY THEN log_sum END)/MAX(reg_sum) rr3, MAX(CASE WHEN login_date = reg_date + INTERVAL 7 DAY THEN log_sum END)/MAX(reg_sum) rr7, MAX(CASE WHEN login_date = reg_date + INTERVAL 30 DAY THEN log_sum END)/MAX(reg_sum) rr30 from t2 GROUP BY reg_date;
