应使用EXISTS而非IN判断状态流转，因IN在user_id重复或含NULL时会丢数据；需明确“7天内”等业务边界定义，避免规则嵌入子查询导致维护困难。
子查询必须用 EXISTS 而不是 IN 来判断用户是否完成某状态流转
直接用 IN 做状态存在性判断，在用户 ID 重复或含 NULL 时会意外丢数据。比如统计“注册后 7 天内完成首次下单”的用户，若用 WHERE user_id IN (SELECT user_id FROM orders WHERE created_at ，只要子查询里任意 <code>user_id 是 NULL，整行就过滤掉——这是 SQL 标准行为，不是 bug。
正确做法是用 EXISTS 配合相关子查询：
SELECT COUNT(*) AS retained_count
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.user_id
AND o.created_at BETWEEN u.register_time AND u.register_time + INTERVAL '7 days'
);
-
EXISTS不受NULL干扰，只关心是否存在匹配行 - 子查询中必须显式关联外层表（
o.user_id = u.user_id），否则变成非相关子查询，结果恒为全量或空 - 时间范围用
BETWEEN比+ <code>>=更易读，但注意闭区间语义是否符合业务（如是否包含起始秒）
多状态流转需嵌套子查询而非 JOIN，避免笛卡尔积膨胀
统计“注册 → 首单 → 二次下单 → 30 天内复购”的留存链路时，如果用 JOIN 连四张表（users、orders×3），一旦某用户有多个首单或多个复购，就会产生重复计数。子查询能天然按用户粒度控制逻辑分支。
示例：查完成“注册后 7 天内首单，且首单后 30 天内有二次下单”的用户数：
SELECT COUNT(*)
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o1
WHERE o1.user_id = u.user_id
AND o1.created_at BETWEEN u.register_time AND u.register_time + INTERVAL '7 days'
AND EXISTS (
SELECT 1 FROM orders o2
WHERE o2.user_id = u.user_id
AND o2.created_at > o1.created_at
AND o2.created_at <= o1.created_at + INTERVAL '30 days'
)
);
- 内层
EXISTS依赖外层o1的时间，实现“以首单为基准的 30 天窗口” - 不用
JOIN就规避了因多订单导致的行数爆炸 - 若需取具体首单时间做后续计算（比如算平均间隔），才考虑用
LATERAL或 CTE 提前物化，但多数留存率统计只需布尔判定
日期函数跨数据库差异大，PostgreSQL 用 INTERVAL，MySQL 用 DATE_ADD
同一段子查询逻辑，在 PostgreSQL 和 MySQL 中时间偏移写法不同，硬搬会报错。例如 “注册后第 7 天”：
- PostgreSQL：
u.register_time + INTERVAL '7 days' - MySQL：
DATE_ADD(u.register_time, INTERVAL 7 DAY) - SQL Server：
DATEADD(day, 7, u.register_time)
更隐蔽的问题是时区处理：register_time 若存的是 UTC，但业务要求按本地时区算 7 天，则子查询中需先转换时区再计算，例如 PostgreSQL 中用 (u.register_time AT TIME ZONE 'Asia/Shanghai') + INTERVAL '7 days'。忽略这点会导致凌晨注册的用户被错误排除。
性能瓶颈常在子查询未走索引，务必检查执行计划
当子查询变慢，第一反应不是加内存，而是看是否命中索引。典型漏索引场景：
- 子查询条件含函数，如
WHERE DATE(o.created_at) = ...→ 改成WHERE o.created_at >= '2024-01-01' AND o.created_at - 联合条件缺失复合索引，如子查询有
WHERE user_id = ? AND created_at BETWEEN ? AND ?，但表只有单列user_id索引 → 需建(user_id, created_at)复合索引 - 外层表
users数据量大且无有效过滤条件（如没加WHERE register_time >= '2024-01-01'），导致子查询对每个用户都执行一遍
用 EXPLAIN ANALYZE 看实际行数和循环次数，比猜更快。子查询本身不慢，慢的是它被执行了几百万次。
状态流转的边界定义比语法更重要——比如“7 天内”是指日历天、自然日，还是工作日；“完成下单”是否包含已取消订单。这些业务规则一旦写进子查询，改起来就牵一发而动全身。
