新增用户、活跃用户、流失用户的 SQL 怎么写？

考察要点

这道题面试官想看你是否懂以下几点：

1. 业务指标的 SQL 转化能力：能否将“新增”、“活跃”、“流失”这类抽象的业务概念，精确地转化为 SQL 查询逻辑。
2. 时间序列分析：是否熟练处理带时间戳的数据，进行按日、月等时间窗口的切割、聚合与对比。
3. 复杂查询构建：是否能熟练运用 COUNT(DISTINCT ...)、GROUP BY、子查询、CTE (Common Table Expressions) 以及 LEFT JOIN ... WHERE ... IS NULL 等技巧来处理集合之间的关系（如差集）。

核心概念

本题的核心是围绕一个用户活动日志表，定义并计算三个关键的用户状态指标。我们假设有如下的用户日志表 user_logs：

 1CREATE TABLE user_logs (
 2    user_id INT,
 3    log_time DATETIME,
 4    -- 为了高性能查询，索引至关重要
 5    INDEX idx_logtime_userid (log_time, user_id),
 6    INDEX idx_userid_logtime (user_id, log_time)
 7);

示例数据 (user_logs)

1. 活跃用户 (Active Users)

• 定义：在指定时间窗口内（如某一天、某个月）有活动记录的用户。
• 计算：对指定时间窗口内的日志记录，按 user_id 去重计数。
• SQL 核心：COUNT(DISTINCT user_id) WHERE log_time BETWEEN '...' AND '...'

2. 新增用户 (New Users)

• 定义：在指定时间窗口内首次出现的用户。即用户的第一次活动时间落在这个时间窗口内。
• 计算：首先需要确定每个用户的首次活动时间，然后筛选出首次活动时间在目标窗口内的用户。
• SQL 核心：MIN(log_time) OVER (PARTITION BY user_id) 或 GROUP BY user_id 找到首日，再进行筛选。

3. 流失用户 (Churned Users)

• 定义：这是一个相对概念，定义依赖于两个连续的时间窗口。例如，“月流失用户”指在上一个月活跃，但在本月未活跃的用户。
• 计算：找出上个月的活跃用户集合 A，再找出本月的活跃用户集合 B，A 与 B 的差集 (A - B) 即为流失用户。
• SQL 核心：LEFT JOIN ... WHERE B.user_id IS NULL 是实现集合差集的经典方法。

多种解法

假设我们需要计算 2023年5月 的新增、活跃和流失用户数。

解法 1：CTE + LEFT JOIN（最通用、最清晰）

这种方法使用公用表表达式（CTE）来分步构建逻辑，可读性强，便于调试，是面试中最稳妥的回答。

• 版本：✅ 所有现代 SQL 数据库（MySQL 5.7+, PostgreSQL, SQL Server 等）
• SQL 代码：

 1-- 定义时间范围
 2SET @current_month = '2023-05-01';
 3SET @previous_month = '2023-04-01';
 4
 5WITH
 6-- 1. 找出本月（5月）的活跃用户
 7current_month_active AS (
 8    SELECT DISTINCT user_id
 9    FROM user_logs
10    WHERE log_time >= @current_month AND log_time < @current_month + INTERVAL 1 MONTH
11),
12-- 2. 找出上个月（4月）的活跃用户
13previous_month_active AS (
14    SELECT DISTINCT user_id
15    FROM user_logs
16    WHERE log_time >= @previous_month AND log_time < @previous_month + INTERVAL 1 MONTH
17),
18-- 3. 找出所有用户的首次活跃日期
19user_first_activity AS (
20    SELECT
21        user_id,
22        MIN(log_time) AS first_log_time
23    FROM user_logs
24    GROUP BY user_id
25)
26-- 4. 聚合计算最终结果
27SELECT
28    -- 活跃用户数：直接对本月活跃用户CTE计数
29    (SELECT COUNT(*) FROM current_month_active) AS active_users,
30
31    -- 新增用户数：本月活跃且首次活跃也在本月的用户
32    (SELECT COUNT(t1.user_id)
33     FROM current_month_active t1
34     JOIN user_first_activity t2 ON t1.user_id = t2.user_id
35     WHERE t2.first_log_time >= @current_month AND t2.first_log_time < @current_month + INTERVAL 1 MONTH
36    ) AS new_users,
37
38    -- 流失用户数：上月活跃但本月不活跃的用户
39    (SELECT COUNT(t1.user_id)
40     FROM previous_month_active t1
41     LEFT JOIN current_month_active t2 ON t1.user_id = t2.user_id
42     WHERE t2.user_id IS NULL
43    ) AS churned_users;

• 复杂度：
  • 时间复杂度：主要开销在于对 user_logs 表的几次扫描和 GROUP BY。如果 (log_time, user_id) 有索引，每次扫描的复杂度约为 $O(M \log N)$ 或 $O(M)$（其中 M 为月份数据量，N 为总数据量）。user_first_activity 的 GROUP BY 复杂度为 $O(U \log U)$（U 为总用户数）。整体性能良好。
  • 空间复杂度：CTE 会物化中间结果，空间消耗取决于每月活跃用户数和总用户数。
• 索引依赖：idx_logtime_userid (log_time, user_id) 对 CTE 1 和 2 至关重要。idx_userid_logtime (user_id, log_time) 对 CTE 3 至关重要。

解法 2：条件聚合 + 窗口函数（更紧凑、MySQL 8.0+）

此方法将多次扫描合并，利用条件聚合在一个查询中计算出多个指标，代码更紧凑，但对数据库版本有要求。

• 版本：✅ MySQL 8.0+ / PostgreSQL
• SQL 代码：

 1-- 定义时间范围
 2SET @current_month_start = '2023-05-01';
 3SET @current_month_end = '2023-06-01';
 4SET @previous_month_start = '2023-04-01';
 5SET @previous_month_end = '2023-05-01';
 6
 7WITH user_activity_summary AS (
 8    SELECT
 9        user_id,
10        -- 计算每个用户在本月和上月的活跃状态 (1 or NULL)
11        MAX(CASE WHEN log_time >= @current_month_start AND log_time < @current_month_end THEN 1 END) AS is_active_current,
12        MAX(CASE WHEN log_time >= @previous_month_start AND log_time < @previous_month_end THEN 1 END) AS is_active_previous,
13        -- 使用窗口函数计算首次活跃日期
14        MIN(log_time) OVER (PARTITION BY user_id) AS first_log_time
15    FROM user_logs
16    -- 优化：仅扫描相关月份的数据，避免全表扫描
17    WHERE log_time >= @previous_month_start AND log_time < @current_month_end
18)
19SELECT
20    -- 活跃用户：统计本月活跃的用户
21    COUNT(DISTINCT CASE WHEN is_active_current = 1 THEN user_id END) AS active_users,
22    -- 新增用户：本月活跃且首次活跃时间在本月的用户
23    COUNT(DISTINCT CASE WHEN is_active_current = 1 AND first_log_time >= @current_month_start AND first_log_time < @current_month_end THEN user_id END) AS new_users,
24    -- 流失用户：上月活跃但本月不活跃的用户
25    COUNT(DISTINCT CASE WHEN is_active_previous = 1 AND is_active_current IS NULL THEN user_id END) AS churned_users
26FROM user_activity_summary;

• 说明：这种写法在 user_activity_summary CTE 中，通过一次扫描（限定在两个月内）就为每个用户打上了“本月活跃”、“上月活跃”和“首次活跃日期”的标签。最后的 SELECT 语句利用 COUNT(DISTINCT CASE ... END) 进行条件计数，非常高效。
• 复杂度：
  • 时间复杂度：对 user_logs 表进行一次范围扫描，然后是窗口函数的计算。通常比解法 1 更快，因为它减少了表的扫描次数。复杂度约为 $O(M_{total} \log U)$，其中 $M_{total}$ 是两个月的数据量。
  • 空间复杂度：窗口函数需要内存来为每个 user_id 分区保存状态。
• 索引依赖：idx_logtime_userid (log_time, user_id) 同样至关重要，可以极大地加速 WHERE 子句的筛选。

常见低分回答

1. COUNT(user_id) vs COUNT(DISTINCT user_id) 混淆：计算活跃用户时，一个用户在一个月内可能有多条日志，必须用 DISTINCT 去重。
   sql

   ⧉⛶

    1-- 错误：会重复计算用户
    2SELECT COUNT(user_id) FROM user_logs WHERE log_time >= '2023-05-01';

2. 新增用户定义错误：误以为某月第一次出现的日志就是新增，而没有考虑该用户在历史月份是否出现过。
   sql

   ⧉⛶

    1-- 错误：这只是找到了每个用户在5月份的第一次登录，但他们可能4月份就登录过了
    2SELECT COUNT(DISTINCT user_id)
    3FROM (
    4    SELECT user_id, MIN(log_time) as first_log
    5    FROM user_logs
    6    WHERE log_time >= '2023-05-01' AND log_time < '2023-06-01'
    7    GROUP BY user_id
    8) t;

3. 流失用户计算逻辑混乱：使用 NOT IN 子查询，但子查询可能返回 NULL，导致结果不正确或性能低下。LEFT JOIN ... IS NULL 是更安全、更高效的选择。
   sql

   ⧉⛶

    1-- 风险：如果 current_month_active 子查询结果中包含 NULL，整个查询结果会是 0
    2SELECT COUNT(DISTINCT user_id)
    3FROM previous_month_active
    4WHERE user_id NOT IN (SELECT user_id FROM current_month_active);

4. 在列上使用函数导致索引失效：对 log_time 列使用 DATE_FORMAT 等函数进行 WHERE 过滤，会导致索引无法使用。
   sql

   ⧉⛶

    1-- 性能杀手：索引失效，将导致全表扫描
    2WHERE DATE_FORMAT(log_time, '%Y-%m') = '2023-05';
    3-- 正确方式：使用范围查询
    4WHERE log_time >= '2023-05-01' AND log_time < '2023-06-01';

工程实践与优化

• 数据预处理与数仓分层：在生产环境中，特别是大数据量下，直接从原始日志表（ODS 层）进行即席查询是不可接受的。通常会进行预聚合。
  • DWS/DWM (数据仓库服务层/明细层)：每天定时任务（如 Spark/Flink）会计算出每个用户的日活状态，存入一张 dws_user_daily_active 表，字段如 (user_id, active_date, is_new_user)。
  • ADS (应用数据层)：基于 DWS 表，进一步聚合出月度指标，存入 ads_monthly_metrics 表，字段如 (month, active_users, new_users, churn_users)。前端报表直接查询 ADS 表，实现秒级响应。
• 执行计划分析 (EXPLAIN)：
  • 高分表现：对于解法1，EXPLAIN 结果中，访问 user_logs 表的 type 应该是 range（使用了 log_time 索引），key 应该是 idx_logtime_userid。user_first_activity 的 GROUP BY 可能会用到 idx_userid_logtime 索引来优化。
  • 低分表现：type 为 ALL（全表扫描），Extra 中出现 Using filesort（无法利用索引排序）或 Using temporary（使用了临时表）都意味着性能瓶颈。
• 索引选择：
  • (log_time, user_id) 复合索引对于按时间范围筛选用户（如本题场景）是最高效的。
  • (user_id, log_time) 复合索引对于查询单个用户的活动历史（如计算 user_first_activity）是最高效的。在真实场景中，这两者可能都需要建立。
• 大数据下的特殊优化：
  • 分区表：将 user_logs 表按 log_time 进行月度或日度分区。这样查询时，数据库只需扫描相关的分区，极大减少 I/O。
  • 近似计算：对于亿级、十亿级用户，精确的 COUNT(DISTINCT) 非常耗费资源。可以使用 HyperLogLog 这类基数估计算法，以极小的内存代价和可接受的误差（如 1%）快速估算活跃用户数。

常见追问与延伸题目

1. 追问：如果要计算每日的“新增”、“活跃”、“留存”并展示成一个趋势图，SQL 怎么写？

   • 回答要点：这需要按天聚合，而不是计算单个时间点。核心思路是生成一个日期序列（可以用递归 CTE 或日历表），然后 LEFT JOIN 每日的聚合结果。
     • 活跃 (DAU)：GROUP BY log_date 对 user_logs 进行聚合。
     • 新增：先计算出每个用户的首次登录日期 user_first_activity，然后按 first_log_date 进行 GROUP BY 计数。
     • 次日留存：今天的活跃用户中，有多少人是昨天的新增用户。这需要 JOIN 今天活跃用户表和昨天新增用户表。
     • 最终将这几份数据按日期 JOIN 在一起，形成一个宽表。

2. 追问：刚才的“流失”定义是“月流失”。如果业务定义“连续30天未登录”算流失，SQL 该如何实现？

   • 回答要点：这个问题更复杂，考察对窗口函数 LAG 或 LEAD 的熟练运用。
     • 思路：首先，对每个用户的登录记录按时间排序，得到一个活动时间序列。然后，使用 LEAD(log_time, 1) OVER (PARTITION BY user_id ORDER BY log_time) 计算出下一次登录时间。
     • 判断：计算 LEAD(log_time) - log_time 的时间差。如果这个差值大于 30 天，那么在 log_time 这个时间点之后，用户就进入了一个超过30天的“沉默期”。
     • 统计：可以筛选出那些 (下次登录时间 - 本次登录时间) > 30 或者 下次登录时间 IS NULL (至今未再登录) 的记录，作为流失事件的起点。

3. 追问：如果换成 ClickHouse 这种 OLAP 数据库，你的查询会有什么不同？

   • 回答要点：ClickHouse 的设计哲学和 MySQL (OLTP) 完全不同，查询也应随之调整。
     • 物化视图：ClickHouse 强大的物化视图能力是首选。可以创建物化视图，在数据写入时就自动聚合出日活、用户首次出现时间等信息，查询时近乎零成本。
     • 专用函数：ClickHouse 提供了丰富的聚合函数。例如，uniqCombined 函数是 COUNT(DISTINCT) 的高性能版本。groupBitmap 等函数可以高效地计算集合交并补，非常适合计算留存和流失。
     • 查询语法：虽然也是 SQL，但会更多地使用 ARRAY 函数、lambda 表达式以及 ClickHouse 特有的聚合函数来写出更高效、更具表达力的查询。例如，用 groupArray 将用户ID聚合成数组，再用数组函数处理。整体思路会从 JOIN 模式向“大宽表+聚合”模式转变。