SQL164 2021年11月每天新用戶的次日留存率

思路

1. ?找出新用戶?：確定每個用戶首次活躍的日期（即新用戶）

   • 例如101用戶在11月1日首次出現

2. ?處理跨天活躍?：考慮用戶可能跨天活躍的情況（in_time和out_time不在同一天）

   • 例如用戶可能在11月1日23:50進入，11月2日00:10離開，則算作兩天都活躍

3. ?計算次日留存?：

   • 對每個新用戶，檢查他們首次活躍后的第二天是否仍然活躍
   • 使用LEAD窗口函數高效獲取用戶下一次活躍日期

4. ?計算留存率?：

   • 每天的新用戶數作為分母
   • 第二天仍然活躍的新用戶數作為分子
   • 兩者相除得到留存率，保留2位小數

最終輸出2021年11月每天新用戶的次日留存率，按日期排序。

代碼

WITH 
-- 獲取每個用戶的最早活躍日期作為其首次出現日期
first_occurrence AS (SELECT uid,DATE(MIN(in_time)) AS first_dtFROM tb_user_logGROUP BY uid
),-- 獲取每個用戶每天活躍的記錄（處理跨天情況）
user_active_dates AS (SELECT DISTINCT uid,DATE(in_time) AS active_dateFROM tb_user_logUNIONSELECT DISTINCT uid,DATE(out_time) AS active_dateFROM tb_user_log
),-- 為每個用戶按日期排序，并使用LEAD獲取下一天的活躍狀態
user_activity_sequence AS (SELECT uid,active_date,LEAD(active_date) OVER (PARTITION BY uid ORDER BY active_date) AS next_active_dateFROM user_active_dates
),-- 計算每天的新用戶數及其次日留存情況
daily_stats AS (SELECT fo.first_dt AS dt,COUNT(DISTINCT fo.uid) AS new_users,COUNT(DISTINCT CASE WHEN DATEDIFF(uas.next_active_date, fo.first_dt) = 1 THEN fo.uid END) AS retained_usersFROM first_occurrence foLEFT JOIN user_activity_sequence uas ON fo.uid = uas.uid AND fo.first_dt = uas.active_dateWHERE fo.first_dt BETWEEN '2021-11-01' AND '2021-11-30'GROUP BY fo.first_dt
)-- 計算并格式化留存率
SELECT dt,ROUND(IF(new_users = 0, 0, retained_users / new_users), 2) AS uv_left_rate
FROM daily_stats
ORDER BY dt;

逐步展示如何計算2021年11月每天新用戶的次日留存率

原始數據表 tb_user_log

id	uid	artical_id	in_time	out_time	sign_in
1	101	0	2021-11-01 10:00:00	2021-11-01 10:00:42	1
2	102	9001	2021-11-01 10:00:00	2021-11-01 10:00:09	0
3	103	9001	2021-11-01 10:00:01	2021-11-01 10:01:50	0
4	101	9002	2021-11-02 10:00:09	2021-11-02 10:00:28	0
5	103	9002	2021-11-02 10:00:51	2021-11-02 10:00:59	0
6	104	9001	2021-11-02 10:00:28	2021-11-02 10:00:50	0
7	101	9003	2021-11-03 11:00:55	2021-11-03 11:01:24	0
8	104	9003	2021-11-03 11:00:45	2021-11-03 11:00:55	0
9	105	9003	2021-11-03 11:00:53	2021-11-03 11:00:59	0
10	101	9002	2021-11-04 11:00:55	2021-11-04 11:00:59	0

步驟1：確定每個用戶的首次活躍日期

SELECT uid,DATE(MIN(in_time)) AS first_dt
FROM tb_user_log
GROUP BY uid;

結果：

uid	first_dt
101	2021-11-01
102	2021-11-01
103	2021-11-01
104	2021-11-02
105	2021-11-03

步驟2：處理跨天情況，獲取用戶活躍日期

SELECT DISTINCT uid,DATE(in_time) AS active_date
FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) AS active_date
FROM tb_user_log;

結果：

uid	active_date
101	2021-11-01
101	2021-11-02
101	2021-11-03
101	2021-11-04
102	2021-11-01
103	2021-11-01
103	2021-11-02
104	2021-11-02
104	2021-11-03
105	2021-11-03

步驟3：使用LEAD函數獲取用戶的下一次活躍日期

SELECT uid,active_date,LEAD(active_date) OVER (PARTITION BY uid ORDER BY active_date) AS next_active_date
FROM user_active_dates;

結果：

uid	active_date	next_active_date
101	2021-11-01	2021-11-02
101	2021-11-02	2021-11-03
101	2021-11-03	2021-11-04
101	2021-11-04	NULL
102	2021-11-01	NULL
103	2021-11-01	2021-11-02
103	2021-11-02	NULL
104	2021-11-02	2021-11-03
104	2021-11-03	NULL
105	2021-11-03	NULL

步驟4：計算每天的新用戶次日留存情況

SELECT fo.first_dt AS dt,COUNT(DISTINCT fo.uid) AS new_users,COUNT(DISTINCT CASE WHEN DATEDIFF(uas.next_active_date, fo.first_dt) = 1 THEN fo.uid END) AS retained_users
FROM first_occurrence fo
LEFT JOIN user_activity_sequence uas ON fo.uid = uas.uid AND fo.first_dt = uas.active_date
WHERE fo.first_dt BETWEEN '2021-11-01' AND '2021-11-30'
GROUP BY fo.first_dt;

結果：

dt	new_users	retained_users
2021-11-01	3	2
2021-11-02	1	1
2021-11-03	1	0

詳細解釋一下

這個CTE是計算每日新用戶及其次日留存情況的核心部分，詳細拆解邏輯：

1. ?數據來源?：

   • first_occurrence：包含每個用戶的首次活躍日期
   • user_activity_sequence：包含用戶每次活躍日期及下一次活躍日期(使用LEAD計算)

2. ?連接條件?：

   LEFT JOIN user_activity_sequence uas 
   ON fo.uid = uas.uid 
   AND fo.first_dt = uas.active_date

   • 按用戶ID連接
   • 只連接用戶首次活躍當天的記錄（因為我們要計算的是新用戶的次日留存）

3. ?計算字段?：

   • new_users：每天首次出現的用戶數（COUNT DISTINCT）
   • retained_users：這些新用戶中第二天仍然活躍的數量

4. ?留存判斷邏輯?：

   CASE WHEN DATEDIFF(uas.next_active_date, fo.first_dt) = 1 THEN fo.uid 
   END

   • 計算用戶首次活躍日期與下一次活躍日期的差值
   • 如果差值為1天，則表示用戶次日活躍

5. ?為什么用LEFT JOIN?：

   • 確保即使新用戶第二天不活躍，也會被計入分母（新用戶數）
   • 不活躍的用戶在CASE WHEN中會返回NULL，不會被COUNT計算

---

示例數據推演

以2021-11-01為例：

• 新用戶：101、102、103
• 檢查他們的次日活躍情況：
  • 101：11-02活躍（符合）
  • 102：11-02不活躍
  • 103：11-02活躍（符合）
• 結果：3個新用戶，2個次日活躍 → 留存率2/3=0.67

這種設計確保了：

1. 準確識別新用戶
2. 正確處理跨天活躍情況
3. 精確計算次日留存率

---

最終結果：計算留存率

SELECT dt,ROUND(IF(new_users = 0, 0, retained_users / new_users), 2) AS uv_left_rate
FROM daily_stats
ORDER BY dt;

最終輸出：

dt	uv_left_rate
2021-11-01	0.67
2021-11-02	1.00
2021-11-03	0.00