找出在过去 7 天里同时使用过 A / B / C 三个功能的用户。

考察要点

这道题是典型的大厂高频面试题，面试官想考察你对“集合中的集合”（Set-within-Sets）问题的处理能力，也称为“关系除法”问题。具体来说，这道题想看你是否懂：

1. 聚合与筛选：能否熟练运用 GROUP BY 对用户进行分组，并结合 HAVING 子句对分组后的结果进行条件过滤。
2. 精确计数：是否能意识到需要使用 COUNT(DISTINCT ...) 来避免重复计算，确保每个功能只被计入一次。
3. SQL 逻辑严谨性：能否写出逻辑正确、没有歧义的 SQL，并考虑到性能和索引的使用。

核心概念

解决本题的核心是利用 SQL 的聚合能力，将“一个用户是否用过 A、B、C 三个功能”这个问题，转化为“对该用户7天内的行为记录进行分组，统计其使用过的不同功能数量是否为 3”。

1. 模拟数据表 user_activity 假设我们有如下的用户行为日志表：

 1CREATE TABLE user_activity (
 2  `id` INT AUTO_INCREMENT PRIMARY KEY,
 3  `user_id` BIGINT NOT NULL,
 4  `feature_name` VARCHAR(50) NOT NULL,
 5  `event_time` DATETIME NOT NULL,
 6  INDEX idx_feature_time_user (feature_name, event_time, user_id)
 7);

示例数据 (假设今天是 2023-10-27)

期望输出

2. 关键函数与子句

• GROUP BY user_id: 将数据按用户进行分组，是解决问题的基础。所有聚合函数（如 COUNT）都将在此分组内进行计算。
• WHERE event_time >= ...: 这是第一层过滤，在聚合前尽可能地减少数据量。使用 DATE_SUB(CURDATE(), INTERVAL 7 DAY) 或 NOW() - INTERVAL 7 DAY 来筛选时间范围。注意，直接对 event_time 列进行比较可以有效利用索引。
• WHERE feature_name IN ('A', 'B', 'C'): 同样是聚合前过滤，只保留我们关心的三种功能行为，进一步减少后续聚合的数据量，极大提升性能。
• HAVING COUNT(DISTINCT feature_name) = 3: 这是第二层过滤，也是解题的核心。HAVING 子句用于对 GROUP BY 之后的分组结果进行筛选。COUNT(DISTINCT feature_name) 确保了即使用户多次使用同一功能，也只计数一次，准确统计了不同功能的数量。

多种解法

解法 1：GROUP BY + HAVING（标准最优解）

这是最通用、最高效、最易读的解法，是面试官最期望看到的答案。

• 适用版本: ✅ 所有主流数据库版本（MySQL, PostgreSQL, Oracle, SQL Server 等）
• SQL 代码:
  sql

  ⧉⛶

   1SELECT
   2    user_id
   3FROM
   4    user_activity
   5WHERE
   6    event_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
   7    AND feature_name IN ('A', 'B', 'C')
   8GROUP BY
   9    user_id
  10HAVING
  11    COUNT(DISTINCT feature_name) = 3;

• 时间/空间复杂度:
  • 时间复杂度：$O(N)$，其中 N 是满足 WHERE 条件的行数。数据库通常使用哈希聚合（Hash Aggregation）来实现 GROUP BY，其时间复杂度是线性的。如果必须排序（如内存不足），则为 $O(N \log N)$。
  • 空间复杂度：$O(U_{distinct} + F_{distinct})$，其中 $U_{distinct}$ 是满足条件的独立用户数， $F_{distinct}$ 是独立功能数（这里是3），用于存储哈希表的键值。
• 索引依赖:
  • 为了最高效地执行，需要在 (feature_name, event_time) 上建立复合索引。
  • WHERE feature_name IN ('A', 'B', 'C') 可以利用索引快速定位到这三个功能的记录。
  • AND event_time >= ... 可以继续在该索引上进行范围扫描（Range Scan）。
  • 这样，WHERE 阶段就能极大地减少需要处理的数据量。

解法 2：多次 JOIN（或 INTERSECT）

这种方法将问题分解为“找到用过A的用户集合”、“找到用过B的用户集合”、“找到用过C的用户集合”，然后求这三个集合的交集。思路清晰，但在功能列表很长时会显得冗长。

• 适用版本: ✅ 所有主流数据库版本
• SQL 代码:
  sql

  ⧉⛶

   1SELECT T_A.user_id
   2FROM
   3    (SELECT DISTINCT user_id FROM user_activity WHERE feature_name = 'A' AND event_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AS T_A
   4JOIN
   5    (SELECT DISTINCT user_id FROM user_activity WHERE feature_name = 'B' AND event_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AS T_B ON T_A.user_id = T_B.user_id
   6JOIN
   7    (SELECT DISTINCT user_id FROM user_activity WHERE feature_name = 'C' AND event_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AS T_C ON T_A.user_id = T_C.user_id;

  注：在支持 INTERSECT 的数据库（如 PostgreSQL, Oracle）中，可以写得更简洁，但 MySQL 8.0 才开始支持。
• 时间/空间复杂度:
  • 时间复杂度：取决于 JOIN 策略。如果每个子查询都能高效利用索引，且其中一个功能的用户基数很小，那么 JOIN 的性能可能很高。但通常情况下，它需要执行多次扫描和连接，总开销往往大于 GROUP BY。
  • 空间复杂度：每个子查询可能会产生临时表来存储中间结果（独立用户ID列表），总空间消耗可能比解法1大。
• 索引依赖:
  • 最佳索引是 (feature_name, event_time, user_id)。这样每个子查询都可以通过覆盖索引（Covering Index）完成，无需回表，性能极佳。

解法 3：使用窗口函数（现代 SQL 思维，但此题中略显繁琐）

此方法展示了对现代 SQL 功能的掌握，但对于这个特定问题，它比 GROUP BY 更复杂，性能也通常更差。

• 适用版本: ✅ MySQL 8.0+, PostgreSQL, Oracle 等支持窗口函数的版本
• SQL 代码:
  sql

  ⧉⛶

   1WITH DistinctUserFeatures AS (
   2    -- 步骤1: 筛选出7天内、ABC功能、去重后的用户-功能对
   3    SELECT DISTINCT user_id, feature_name
   4    FROM user_activity
   5    WHERE event_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
   6      AND feature_name IN ('A', 'B', 'C')
   7),
   8UserFeatureCount AS (
   9    -- 步骤2: 使用窗口函数为每个用户计算他使用了多少个不同的功能
  10    SELECT
  11        user_id,
  12        COUNT(feature_name) OVER (PARTITION BY user_id) as distinct_feature_count
  13    FROM DistinctUserFeatures
  14)
  15-- 步骤3: 筛选出功能数为3的用户，并去重
  16SELECT DISTINCT user_id
  17FROM UserFeatureCount
  18WHERE distinct_feature_count = 3;

• 时间/空间复杂度:
  • 时间复杂度：$O(N \log N)$。窗口函数的 PARTITION BY 通常需要对数据进行排序或分区，开销较大。
  • 空间复杂度：$O(N)$，WITH 子句会物化中间结果集，内存消耗较高。
• 索引依赖: 与解法1类似，user_activity 表上的 (feature_name, event_time) 索引对第一步至关重要。

常见低分回答

1. 忘记 DISTINCT：HAVING COUNT(feature_name) = 3。这是最常见的错误。如果一个用户使用了 3 次 A 功能，而没有使用 B 或 C，这个查询会错误地将他选出。
2. 在 WHERE 中使用聚合函数：WHERE COUNT(DISTINCT feature_name) = 3。这是语法错误，SQL 标准规定 WHERE 子句不能包含聚合函数，因为它在 GROUP BY 之前执行，而聚合函数需要分组后才能计算。
3. 对索引列使用函数：WHERE DATE_FORMAT(event_time, '%Y-%m-%d') BETWEEN ...。这会导致 event_time 列上的索引失效，数据库被迫进行全表扫描，在数据量大时是灾难性的。应始终保持索引列的“干净”，将函数和计算应用在常量值上。
4. 逻辑错误的 AND 条件：WHERE feature_name = 'A' AND feature_name = 'B' AND feature_name = 'C'。这表明候选人没有理解 SQL 的行处理模型。单行记录的 feature_name 列不可能同时等于三个不同的值，此查询永远返回空集。

工程实践与优化

• 索引选择是关键：对于这类查询，WHERE 子句的过滤性（Selectivity）决定了性能。一个好的复合索引 (feature_name, event_time, user_id) 是最优选择。feature_name 在前，因为 IN 查询可以转化为多个 OR 连接的范围查询；event_time 在后，用于在每个功能内部进行范围过滤。user_id 放在最后，可以让 GROUP BY 操作有可能利用索引而避免文件排序（Using index for group-by）。
• 执行计划分析：使用 EXPLAIN 分析你的查询。
  • 关注 type 列：理想情况是 range 或 ref。避免 ALL（全表扫描）。
  • 关注 key 列：确保用上了你设计的索引。
  • 关注 Extra 列：Using index condition 是好的，表示索引下推。Using where 正常。Using temporary; Using filesort 是性能瓶颈信号，说明 GROUP BY 无法利用索引，需要创建临时表并排序。
• 大数据量下的考量：当 user_activity 表达到百亿、千亿级别时，即使有索引，实时查询也会很慢。
  • 预聚合：这是最核心的优化思想。可以创建一个每日用户活跃功能表 daily_user_features_summary，通过离线任务（如 Spark/Flink）每天计算好每个用户当天使用了哪些不同的功能。查询时直接查这张小得多的结果表。
  • OLAP 引擎：对于复杂的分析查询，可以考虑将数据导入 ClickHouse、Doris 等列式存储的 OLAP 数据库。它们为聚合查询做了深度优化，性能远超 MySQL 这类 OLTP 数据库。
  • Bitmap 方案：在用户量巨大且需要频繁进行各种组合查询的场景（如人群圈选），可以为每个功能维护一个用户 ID 的 Bitmap。查询“同时使用 A/B/C 的用户”就变成了对三个 Bitmap 进行位与（AND）操作，速度极快。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍，达到百亿级别，你的 SQL 还能跑吗？怎么优化？

   • 回答要点 1 (指出瓶颈)：直接在百亿级别的原始表上运行此 SQL 会非常慢，甚至导致数据库超时或OOM。瓶颈在于需要扫描和聚合的数据量过大，即使有索引，I/O 和 CPU 开销也难以承受。
   • 回答要点 2 (核心方案：预计算)：我会引入预计算/预聚合的架构。创建一个按天聚合的中间表（Summary Table），例如 dm_user_feature_daily (user_id, feature_list, dt)。通过每日的离线批处理任务（如 Spark），计算出每个用户当天使用过的所有功能，并可以存成一个数组或字符串。查询时，只需扫描这张小得多的聚合表，例如 SELECT user_id FROM dm_user_feature_daily WHERE dt >= ... AND CONTAINS(feature_list, 'A') AND CONTAINS(feature_list, 'B') AND CONTAINS(feature_list, 'C')。
   • 回答要点 3 (技术选型)：如果需要更灵活的即席查询，我会建议将数据导入专门的 OLAP 分析引擎，如 ClickHouse 或 Apache Doris。它们采用列式存储和向量化执行，对于大规模聚合查询性能比 MySQL 高出几个数量级。

2. 追问：如果要找的是“在过去7天，只使用了 A, B, C 这三个功能，且必须都使用过，没有使用任何其他功能”的用户，SQL 该怎么改？

   • 回答要点 1 (修改 HAVING 子句)：这个问题比原问题更严格。我们需要在解法1的基础上，增加一个排除条件。
   • 回答要点 2 (给出 SQL)：可以在 HAVING 子句中增加一个条件，确保该用户分组内的所有功能都在 ('A', 'B', 'C') 集合中。
     sql

     ⧉⛶

      1SELECT user_id
      2FROM user_activity
      3WHERE event_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
      4GROUP BY user_id
      5HAVING
      6    -- 条件1: 确保 A, B, C 都使用了
      7    COUNT(DISTINCT CASE WHEN feature_name IN ('A', 'B', 'C') THEN feature_name ELSE NULL END) = 3
      8    -- 条件2: 确保没有使用 A, B, C 之外的任何功能
      9    AND COUNT(DISTINCT CASE WHEN feature_name NOT IN ('A', 'B', 'C') THEN feature_name ELSE NULL END) = 0;

     或者一个更简洁但可读性稍差的写法：
     sql

     ⧉⛶

      1HAVING COUNT(DISTINCT feature_name) = 3 -- 总功能数是3
      2   AND MAX(CASE WHEN feature_name NOT IN ('A', 'B', 'C') THEN 1 ELSE 0 END) = 0 -- 没有任何一个功能在列表外

3. 追问：如果功能列表是动态的，比如由业务方传入 ['A', 'B', 'C', 'D']，你的查询如何适应？

   • 回答要点 1 (应用层拼接)：这不是一个纯 SQL 问题，而是 SQL 与应用代码如何协作的问题。最佳实践是在应用层动态生成 SQL 语句。应用接收一个功能列表（如 features = ['A', 'B', 'C', 'D']），然后生成对应的 SQL。
   • 回答要点 2 (代码示例逻辑)：
     python

     ⧉⛶

      1# Python 伪代码
      2def find_users_with_features(features: list):
      3    feature_placeholders = ', '.join(['%s'] * len(features)) # -> '%s, %s, %s, %s'
      4    count = len(features)
      5    sql = f"""
      6        SELECT user_id
      7        FROM user_activity
      8        WHERE event_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
      9          AND feature_name IN ({feature_placeholders})
     10        GROUP BY user_id
     11        HAVING COUNT(DISTINCT feature_name) = %s;
     12    """
     13    params = features + [count]
     14    # execute_sql(sql, params)

   • 回答要点 3 (避免反模式)：要特别指出，应避免将动态列表拼接成一个逗号分隔的字符串，然后传入 SQL 使用 FIND_IN_SET() 或 LIKE 等函数进行匹配。例如 WHERE FIND_IN_SET(feature_name, 'A,B,C,D')，这样做会导致索引失效，性能极差，是严重的反模式。必须使用 IN 列表和参数化查询。