面试题：电商购物篮分析（Market Basket Analysis）之关联商品对挖掘

1. 题目背景与要求

在电商场景中，为了进行精准的交叉销售（Cross-selling）和商品推荐，数据分析师和数据工程师经常需要找出哪些商品最经常被用户在同一个订单中一起购买。

题目要求：
编写一个 SQL 查询，找出在同一个订单（order_id 相同）中经常被同时购买的商品对 (product_a, product_b)，计算它们共同出现的频次（frequency），并按频次从高到低降序排列。如果频次相同，则按商品 A 的 ID、商品 B 的 ID 升序排列。

注意事项：

1. 结果中不能包含自关联的商品（例如：Product_A 与 Product_A 组合）。
2. 结果中商品对不能重复出现。例如 (Product_A, Product_B) 与 (Product_B, Product_A) 视为同一个组合，只需输出 Product_A 的 ID 小于 Product_B 的 ID 的那一条记录。

2. 示例数据表

订单商品明细表：order_items

3. 期望输出结果

4. 核心 SQL 实现

SELECT 
    a.product_id AS product_a_id,
    a.product_name AS product_a_name,
    b.product_id AS product_b_id,
    b.product_name AS product_b_name,
    COUNT(*) AS frequency
FROM 
    order_items a
INNER JOIN 
    order_items b ON a.order_id = b.order_id
WHERE 
    a.product_id < b.product_id
GROUP BY 
    a.product_id,
    a.product_name,
    b.product_id,
    b.product_name
ORDER BY 
    frequency DESC,
    product_a_id ASC,
    product_b_id ASC;

5. SQL 深度解析与面试考点

考点一：自关联（Self-Join）的应用

本题的核心考察点在于自关联（Self-Join）。当我们需要在同一张表内部，寻找具有某种关联关系（如在同一个订单中）的不同行数据时，必须将该表与其自身进行连接。

• 通过 ON a.order_id = b.order_id，我们将同一个订单中的所有商品两两配对。

考点二：去重与避免无效组合（核心难点）

初学者最容易写成 ON a.order_id = b.order_id WHERE a.product_id <> b.product_id。这样会导致两个严重问题：

1. 自我配对：如果没有不等过滤，会出现 (iPhone, iPhone) 这种无意义组合。
2. 重复组合（镜像数据）：会出现 (iPhone, 手机壳) 和 (手机壳, iPhone) 两条频次相同的数据，导致数据量翻倍且结果冗余。

破局点：使用 a.product_id < b.product_id 作为过滤条件。

• 既排除了自身与自身的配对（因为不可能 P001 < P001）。
• 又天然地实现了去重。因为对于 P001 和 P002，只保留了 P001 < P002 这一条记录，从而过滤掉了 P002 < P001 这种镜像重复。

考点三：聚合与排序

• GROUP BY：根据两件商品的 ID（以及名称）进行分组，统计它们在不同订单中同时出现的次数。
• COUNT(*)：由于自关联已经限制了 order_id 相同，这里的 COUNT(*) 代表的就是这两个商品同时出现在同一个订单中的总频次。
• ORDER BY：多级排序。首要指标是频次降序（DESC），次要指标是商品 ID 升序（ASC），确保输出结果的唯一性与整洁性。

考点四：大数据下的性能优化（高薪面试加分项）

在实际大厂海量订单数据（如亿级数据量）场景下，直接进行自关联会导致数据倾斜或 OOM (Out Of Memory)。面试官可能会追问：“如果数据量极大，该如何优化？”

可以提出以下优化思路：

1. 过滤高频“热点”商品：像“购物袋”这种几乎每个订单都有的商品，会产生笛卡尔积爆炸。可以在关联前先将这类超高频商品过滤掉。
2. 限制订单商品数：过滤掉商品件数极多（如超过100件）的异常批发订单，这些订单通常不具备零售推荐参考价值。
3. 先计算频次再 Join 名称：不要直接在 JOIN 中携带 product_name（字符串比较慢）。应该先用 product_id 进行自关联和聚合统计，算出最频繁的商品对后，再与商品维表关联取出中文名称。