做网站有什么市场风险,什么是网站设计与运营,网站开发的架构,jsp网站加载慢前言 在学 HQL 之前是不太了解 SQL 的#xff0c;以为 SQL 只可以实现 CRUD #xff0c;直到面试的公司让我下去多了解一些 SQL #xff0c;我才最近开始再次深入学习 MySQL 和 Oracle。而且越学越发现 SQL 真的是一门很有深度的语言#xff0c;我以前的使用只是皮毛而已以为 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_depth9999999;INSERT INTO t_user(user_name, register_time)
WITH RECURSIVE t AS (SELECT 1 n, 2022-01-01 00:00:00 dUNION ALLSELECT n1, d INTERVAL 1 MINUTEFROM tWHERE n10000
)
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_depth9999999;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 n1, rand()*10000, d INTERVAL CEIL(n/3000) secondFROM tWHERE n500000
)
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)rr1rr3rr7rr302022-01-0161.180645.416734.583318.95832022-01-0250.000043.680632.291718.40282022-01-0344.513938.750031.458318.54172022-01-0443.055636.111129.513916.73612022-01-0538.194434.652829.305616.31942022-01-0635.416733.333328.194418.19442022-01-0734.411829.926527.573517.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;
