基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

找出在平台购买过至少 3 种不同商品品类，但从未购买过“数码配件”品类的活跃用户 ID

面试题：找出购买过至少 3 种不同商品品类，但从未购买过“数码配件”品类的活跃用户

1. 题目背景与要求

在电商平台的数据分析中，运营团队常常需要根据用户的消费行为进行精准画像。本题旨在考察候选人对多表关联、分组聚合（GROUP BY）、去重计数（DISTINCT）以及条件过滤（尤其是“从未购买过某品类”的排除逻辑）的掌握程度。

具体要求：

  • 活跃用户：在用户信息表中标识为活跃（is_active = 1）的用户。
  • 购买品类数：该用户购买过的不同商品品类数量大于或等于 3。
  • 排除条件：该用户在历史购买记录中从未出现过“数码配件”这一品类。

2. 示例数据表

表 1：用户信息表 user_info
user_id (用户ID) is_active (是否活跃：1-是，0-否)
101 1
102 1
103 1
104 0
105 1
表 2：订单明细表 order_details
order_id (订单ID) user_id (用户ID) category (商品品类) purchase_date (购买日期)
1 101 美妆 2026-01-01
2 101 食品 2026-01-02
3 101 服饰 2026-01-03
4 101 美妆 2026-01-05
5 102 美妆 2026-01-01
6 102 数码配件 2026-01-02
7 102 服饰 2026-01-03
8 103 食品 2026-01-01
9 103 服饰 2026-01-02
10 104 美妆 2026-01-01
11 104 食品 2026-01-02
12 104 居家 2026-01-03
13 105 美妆 2026-01-01
14 105 食品 2026-01-02
15 105 图书 2026-01-03
16 105 运动 2026-01-04

3. 期望输出结果

user_id (用户ID)
101
105

结果说明：

  • 用户 101：活跃用户，购买了“美妆”、“食品”、“服饰” 3 种不同品类（订单 1 与订单 4 重复品类已去重），且未购买过“数码配件”，符合条件。
  • 用户 102：活跃用户，虽然购买了 3 种品类，但包含了“数码配件”，不符合条件。
  • 用户 103：活跃用户，仅购买了 2 种品类，数量不足，不符合条件。
  • 用户 104：购买了 3 种品类且无“数码配件”，但其为非活跃用户（is_active = 0），不符合条件。
  • 用户 105：活跃用户，购买了“美妆”、“食品”、“图书”、“运动”共 4 种品类，且无“数码配件”，符合条件。

4. SQL 解决方案

方法一：基于 HAVING 子句的条件过滤（推荐，性能较优）

这种方法通过一次分组聚合，利用 SUM(CASE WHEN...) 判定用户是否购买过特定品类，避免了多重子查询或表连接。

sql 
SELECT u.user_id
FROM user_info u
JOIN order_details o ON u.user_id = o.user_id
WHERE u.is_active = 1
GROUP BY u.user_id
HAVING COUNT(DISTINCT o.category) >= 3
   AND SUM(CASE WHEN o.category = '数码配件' THEN 1 ELSE 0 END) = 0;
方法二：使用子查询排除法（直观易懂）

利用 NOT IN 或 NOT EXISTS 提前将购买过“数码配件”的用户过滤掉，再对剩余数据进行分组统计。

sql 
SELECT u.user_id
FROM user_info u
JOIN order_details o ON u.user_id = o.user_id
WHERE u.is_active = 1
  AND u.user_id NOT IN (
      SELECT DISTINCT user_id 
      FROM order_details 
      WHERE category = '数码配件'
  )
GROUP BY u.user_id
HAVING COUNT(DISTINCT o.category) >= 3;

5. SQL 深度分析与面试应对指南

考点拆解
  1. 多表关联（JOIN）：活跃状态在 user_info 表中，而购买行为在 order_details 表中，需要通过 user_id 进行关联。面试时应注意，若存在没有订单的活跃用户，使用 INNER JOIN 会自动过滤他们，这符合本题“购买过商品”的大前提。
  2. 去重计数（DISTINCT）：题目要求购买过“至少 3 种不同商品品类”。如果用户多次购买同一品类（如示例中用户 101 购买了两次美妆），直接使用 COUNT(category) 会导致计数虚高。必须使用 COUNT(DISTINCT category) 来获取真实的品类数。
  3. “从未购买过”的逻辑表达：这是本题的难点。
    • 常见误区：在 WHERE 子句中写 WHERE category != '数码配件'。这属于典型错误，因为该条件只会过滤掉单条“数码配件”的订单明细，而该用户购买其他品类的订单仍会被保留，无法达到“排除整个用户”的目的。
    • 正确方案：
      • 方案 A（条件转数值法）：在 HAVING 中使用 SUM(CASE WHEN category = '数码配件' THEN 1 ELSE 0 END) = 0。其逻辑是，如果该用户的所有订单中包含“数码配件”，则求和结果大于 0；若等于 0，则代表从未购买过。
      • 方案 B（子查询排除法）：先查出购买过“数码配件”的所有 user_id 集合，再在主查询中用 NOT IN 排除。
性能与优化考量
  • 方案对比：方法一（推荐方案）通常优于方法二。方法一在一个查询流中完成了分组、去重计数和条件排除，减少了对 order_details 表的二次扫描。
  • 索引建议：在实际生产环境中，若 order_details 表数据量极大，建议在 (user_id, category) 上建立联合索引，以加速分组和过滤操作。
面试沟通加分项

在回答此题时，向面试官主动说明“为什么不能在 WHERE 中直接限制 category != '数码配件'”，并解释 “COUNT(DISTINCT category) 与 COUNT(category) 的区别”。这能向面试官展现出您不仅能写出 SQL，还具备严谨的边界条件思考能力与扎实的数据分析基本功。

00:00
00:00