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_id`int 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。

【總結】

1）COUNT()函數

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值）