高邮市建设局网站首页,icp备案官网查询,襄阳网站建设营销,桂林网站建设培训SQL29 计算用户的平均次日留存率
计算用户的平均次日留存率_牛客题霸_牛客网
题目#xff1a;现在运营想要查看用户在某天刷题后第二天还会再来刷题的留存率。 示例#xff1a;question_practice_detail -- 输入#xff1a;
DROP TABLE IF EXISTS question_practice_detai…SQL29 计算用户的平均次日留存率
计算用户的平均次日留存率_牛客题霸_牛客网
题目现在运营想要查看用户在某天刷题后第二天还会再来刷题的留存率。 示例question_practice_detail -- 输入
DROP TABLE IF EXISTS question_practice_detail;
CREATE TABLE question_practice_detail (id int NOT NULL,device_id int NOT NULL,question_idint NOT NULL,result varchar(32) NOT NULL,date date NOT NULL
);-- 插入数据
INSERT INTO question_practice_detail VALUES(1,2138,111,wrong,2021-05-03);
INSERT INTO question_practice_detail VALUES(2,3214,112,wrong,2021-05-09);
INSERT INTO question_practice_detail VALUES(3,3214,113,wrong,2021-06-15);
INSERT INTO question_practice_detail VALUES(4,6543,111,right,2021-08-13);
INSERT INTO question_practice_detail VALUES(5,2315,115,right,2021-08-13);
INSERT INTO question_practice_detail VALUES(6,2315,116,right,2021-08-14);
INSERT INTO question_practice_detail VALUES(7,2315,117,wrong,2021-08-15);
INSERT INTO question_practice_detail VALUES(8,3214,112,wrong,2021-05-09);
INSERT INTO question_practice_detail VALUES(9,3214,113,wrong,2021-08-15);
INSERT INTO question_practice_detail VALUES(10,6543,111,right,2021-08-13);
INSERT INTO question_practice_detail VALUES(11,2315,115,right,2021-08-13);
INSERT INTO question_practice_detail VALUES(12,2315,116,right,2021-08-14);
INSERT INTO question_practice_detail VALUES(13,2315,117,wrong,2021-08-15);
INSERT INTO question_practice_detail VALUES(14,3214,112,wrong,2021-08-16);
INSERT INTO question_practice_detail VALUES(15,3214,113,wrong,2021-08-18);
INSERT INTO question_practice_detail VALUES(16,6543,111,right,2021-08-13);-- 输出
avg_ret
0.3000
-- 方法一
SELECTCOUNT(DISTINCT t2.device_id, t2.date) / COUNT(DISTINCT t1.device_id, t1.date) AS avg_ret
FROMquestion_practice_detail AS t1
LEFT JOIN question_practice_detail AS t2
ON t1.device_id t2.device_idAND DATEDIFF(t2.date, t1.date) 1;【解题思路】 1用 datediff 区分第一天和第二天在线的 device_id 2用 left join 做自表联结 3用 distinct t2.device_id, t2.date 做双重去重找到符合条件的当天在线人数
-- 方法二
SELECT(t1.num / t2.num) AS avg_ret
FROM( -- 计算第二天再来的记录数量SELECTCOUNT(DISTINCT a.device_id, a.date) AS numFROMquestion_practice_detail aINNER JOIN question_practice_detail b ON a.device_id b.device_idAND a.date DATE_ADD(b.date, INTERVAL 1 DAY)) t1,( -- 计算总记录数量SELECTCOUNT(DISTINCT device_id, date) AS numFROMquestion_practice_detail) t2;【解题思路】 留存率(去重后的用户有连续两天刷题记录次数)/(去重日期用户后刷题记录次数) 1计算第二天再来的记录数量 (t1) 使用自连接查找在某一日期出现的设备 ID且该设备 ID 在前一天也有记录计算这些设备 ID 和日期组合的唯一数量。 2计算总记录数量 (t2) 计算 question_practice_detail 表中所有唯一的设备 ID 和日期组合数量。 3最终计算 将 t1 的结果第二天再来的设备数量除以 t2 的结果总的设备和日期组合数量得到平均值 avg_ret。 【总结】
1COUNT()函数
COUNT()函数是一个聚合函数用于计算表中行的数量或特定列的非空值数量。 COUNT(*) 计算表中的所有行包括 NULL 值 COUNT(column_name) 只计算该列中的 非NULL 值 COUNT(DISTINCT column_name) 计算该列中 唯一且 非NULL 的值即该列的不同值的数目 count(distinct a.device_id, a.date) as num 计算在 question_practice_detail 表中不同设备(device_id)和日期(date)组合的数量。
2日期加减函数
DATE_SUB(date, interval 1 day) 从给定的 date 中减去指定的时间间隔 DATE_ADD(date, interval -1 day) 向给定的 date 中加上指定的时间间隔(加上-1天即减去1天) DATEDIFF(date2, date1) 1 返回 date2 - date1 之间的天数差(天数差1天)
示例 DATE_SUB(2025-03-10, interval 1 day) 将返回 2025-03-09。 DATE_ADD(2025-03-10, interval -1 day) 也将返回 2025-03-09。 DATEDIFF(2025-03-10, 2025-03-09) 将返回 1表示两天之间相差1天。
【参考文献】 1、https://blog.csdn.net/zhanchulan/article/details/140047896 2、https://blog.csdn.net/qq_43543789/article/details/142854428 3、https://blog.csdn.net/2301_76352996/article/details/142143839
【附录】
原本日期 减1后日期 date_sub(date, interval 1 day) left join 后情况表关联后计数注意NULL值
