SQL 面试系列（一）【留存率问题】

前言

        在学 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' dUNION ALLSELECT n+1, d + INTERVAL '1' MINUTEFROM tWHERE 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' dUNION ALLSELECT n+1, rand()*10000, d + INTERVAL CEIL(n/3000) secondFROM tWHERE 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.uidAND(DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL 1 DAYOR DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL 3 DAYOR DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL 7 DAYOR 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_sumFROM t1GROUP 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;