SQL次日留存率计算精讲：自连接与多字段去重的深度应用

一、问题拆解：理解次日留存率的计算逻辑

1.1 业务需求转换

题目：运营希望查看用户在某天刷题后第二天还会再来刷题的留存率。

关键分析点：

• 留存率 = （第一天刷题且第二天再次刷题的用户数） / 第一天刷题的总用户数
• 需要关联同一用户的连续两天行为
• 结果要求不去重（保留所有可能的留存行为）

1.2 数据模型假设

假设我们有用户刷题记录表question_practice_detail，包含：

• device_id：用户设备ID（唯一标识用户）
• date：刷题日期
• 其他字段：题目ID、答题结果等（与本次计算无关）

二、核心SQL解析：自连接实现留存关联

2.1 完整SQL语句

SELECT COUNT(DISTINCT q2.device_id, q2.date) / COUNT(DISTINCT q1.device_id, q1.date) AS avg_ret
FROM question_practice_detail AS q1 
LEFT JOIN question_practice_detail AS q2 
ON q1.device_id = q2.device_id AND DATEDIFF(q2.date, q1.date) = 1;

2.2 自连接设计原理

表别名技术：

• q1：作为主表，表示"第一天刷题记录"
• q2：作为关联表，表示"第二天刷题记录"

连接条件解析：

1. q1.device_id = q2.device_id：确保关联同一用户的记录
2. DATEDIFF(q2.date, q1.date) = 1：确保q2的日期比q1晚一天

左连接的意义：

• 即使某用户在次日没有刷题记录（q2为NULL），q1的记录仍会被保留
• 这保证了分母（所有第一天刷题用户）的完整性

三、COUNT(DISTINCT …) 多字段去重详解

3.1 多字段去重的内在逻辑

COUNT(DISTINCT q2.device_id, q2.date)

执行步骤：

1. 组合键生成：将device_id和date组合成复合键（如1001-2023-01-02）
2. 哈希去重：数据库内部使用哈希表对组合键进行去重
3. 计数统计：统计去重后的组合键数量

与单字段去重的区别：

表达式	统计逻辑
COUNT(DISTINCT device_id)	统计不同用户的数量
COUNT(DISTINCT date)	统计不同日期的数量
COUNT(DISTINCT device_id, date)	统计不同用户+日期的组合数量

3.2 分子与分母的统计逻辑

分子：COUNT(DISTINCT q2.device_id, q2.date)

• 统计有次日刷题记录的(用户ID, 日期)组合数
• 确保每个用户每天只被统计一次

分母：COUNT(DISTINCT q1.device_id, q1.date)

• 统计所有第一天刷题的(用户ID, 日期)组合数
• 覆盖所有可能产生留存的基础用户

四、执行流程与数据流转

4.1 示例数据与连接过程

假设我们有以下数据：

q1表（第一天刷题记录）

device_id	date
1001	2023-01-01
1002	2023-01-01
1003	2023-01-01

q2表（第二天刷题记录）

device_id	date
1001	2023-01-02
1001	2023-01-02

自连接结果

q1.device_id	q1.date	q2.device_id	q2.date
1001	2023-01-01	1001	2023-01-02
1002	2023-01-01	NULL	NULL
1003	2023-01-01	NULL	NULL

4.2 统计过程详解

1. 分子计算：

   • COUNT(DISTINCT q2.device_id, q2.date) = 1
   • 去重后只有(1001, 2023-01-02)这一个有效组合

2. 分母计算：

   • COUNT(DISTINCT q1.device_id, q1.date) = 3
   • 包含(1001, 2023-01-01)、(1002, 2023-01-01)、(1003, 2023-01-01)

3. 结果：

   • 次日留存率 = 1/3 ≈ 33.33%

五、性能优化策略

5.1 复合索引设计

-- 创建覆盖索引，同时加速连接和去重
CREATE INDEX idx_device_date ON question_practice_detail(device_id, date);

索引优化原理：

• 支持device_id的等值查询
• 支持date的范围查询（DATEDIFF本质是日期比较）
• 覆盖索引避免回表，直接在索引中完成统计

5.2 执行计划分析

使用EXPLAIN关键字分析SQL执行计划：

EXPLAIN
SELECT ... (原SQL) ...;

关键指标解读：

• type列：理想情况为ref或range，避免ALL（全表扫描）
• key列：应显示使用了idx_device_date索引
• Extra列：避免出现Using temporary和Using filesort

六、常见问题与解决方案

6.1 NULL值处理

-- 假设存在device_id=NULL的记录
COUNT(DISTINCT device_id, date)  -- 会忽略这些记录-- 如需包含NULL，需手动转换
COUNT(DISTINCT COALESCE(device_id, 0), date)

6.2 分母为零处理

当某天没有用户刷题时，直接计算会导致除零错误：

SELECT IFNULL(COUNT(DISTINCT q2.device_id, q2.date) / NULLIF(COUNT(DISTINCT q1.device_id, q1.date), 0), 0) AS avg_ret
FROM ...

6.3 时间窗口扩展

计算3日留存率：

SELECT COUNT(DISTINCT q3.device_id) / COUNT(DISTINCT q1.device_id) AS retention_3day
FROM question_practice_detail AS q1 
LEFT JOIN question_practice_detail AS q3 
ON q1.device_id = q3.device_id AND DATEDIFF(q3.date, q1.date) = 3;

七、总结与技术要点

7.1 核心技术点回顾

1. 自连接技术：通过表别名实现同一表的不同时间关联
2. COUNT(DISTINCT)：多字段组合去重统计的关键
3. LEFT JOIN：确保分母统计的完整性，包含所有可能留存的用户
4. 索引优化：复合索引显著提升大数据量下的查询性能

7.2 技术决策树

开始
│
├── 是否需要统计用户行为留存率？
│   │
│   └── 是 → 是否需要多日留存？
│       │
│       ├── 是 → 使用DATEDIFF调整时间窗口
│       │
│       └── 否 → 是否需要去重？
│           │
│           ├── 是 → 使用COUNT(DISTINCT ...)
│           │
│           └── 否 → 直接使用COUNT
│
├── 是否存在性能问题？
│   │
│   └── 是 → 创建复合索引(用户ID, 日期)
│
└── 结束

通过深入理解自连接和多字段去重的原理，结合索引优化技术，我们可以高效、准确地计算各种时间窗口的用户留存率。