SQL 商品关联购买分析：购物篮分析（拼多多面试题）

一、题目背景

这道题来自拼多多推荐算法与商品运营部门的数据分析岗面试。拼多多以"货找人"的推荐式购物体验著称，购物篮分析（Market Basket Analysis）是推荐系统的基础——"买了A的人也买了B"这个经典推荐语，背后就是商品关联购买关系的计算。运营团队通过关联分析来设计促销捆绑策略（如"洗衣液+柔顺剂"的满减组合），算法团队则将其作为协同过滤推荐的离线特征。

在实际数据仓库中，订单明细表是最基础的交易事实表之一，每行记录一个订单中的一件商品。要从这张表中发现商品之间的关联关系，核心技巧是自连接（Self Join）——将同一张表按 order_id 连接到自己，把同一订单中的不同商品两两配对。

业务场景：商品运营团队每月跑一次购物篮分析，输出"关联购买Top 100商品对"，用于大促期间的跨品类推荐和凑单满减策略设计。这道题的 SQL 逻辑就是该分析的基础查询。

二、题目

从订单明细表中，分析同一笔订单中商品之间的关联购买关系，找出最常被一起购买的Top3商品组合（两个商品为一组），输出商品A的ID、商品B的ID、以及它们共同出现的订单数。

假设有一张订单明细表 t5_order_detail，记录了每笔订单中购买的商品信息：

+-----------+-------------+------+
| order_id  | product_id  | qty  |
+-----------+-------------+------+
| 1001      | A           | 2    |
| 1001      | B           | 1    |
| 1001      | C           | 1    |
| 1002      | A           | 1    |
| 1002      | B           | 3    |
| 1003      | B           | 1    |
| 1003      | C           | 2    |
| 1004      | A           | 1    |
| 1004      | C           | 1    |
| 1004      | D           | 1    |
| 1005      | A           | 2    |
| 1005      | B           | 1    |
+-----------+-------------+------+

三、思路分析

1. 购物篮分析的核心是通过自连接将同一订单中的不同商品两两配对；
2. 使用 INNER JOIN 以 order_id 为关联键，并限制 a.product_id < b.product_id 避免重复组合；
3. 按商品组合分组统计出现次数，取 Top 5。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

1.自连接生成商品组合，统计每对组合共同出现的订单数

执行SQL

select a.product_id as product_a,
       b.product_id as product_b,
       count(distinct a.order_id) as co_order_cnt
from t5_order_detail a
inner join t5_order_detail b
  on a.order_id = b.order_id
where a.product_id < b.product_id
group by a.product_id, b.product_id

查询结果

+------------+------------+---------------+
| product_a  | product_b  | co_order_cnt  |
+------------+------------+---------------+
| B          | C          | 2             |
| A          | C          | 2             |
| C          | D          | 1             |
| A          | D          | 1             |
| A          | B          | 3             |
+------------+------------+---------------+
5 rows selected (1.292 seconds)(https://www.dwsql.com)

2.按共现次数降序取Top3

执行SQL

select product_a,
       product_b,
       co_order_cnt
from (
    select a.product_id as product_a,
           b.product_id as product_b,
           count(distinct a.order_id) as co_order_cnt
    from t5_order_detail a
    inner join t5_order_detail b
      on a.order_id = b.order_id
    where a.product_id < b.product_id
    group by a.product_id, b.product_id
) t
order by co_order_cnt desc
limit 3

查询结果

+------------+------------+---------------+
| product_a  | product_b  | co_order_cnt  |
+------------+------------+---------------+
| A          | B          | 3             |
| B          | C          | 2             |
| A          | C          | 2             |
+------------+------------+---------------+
3 rows selected (0.526 seconds)(https://www.dwsql.com)

五、常见坑点

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三

1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
多表JOIN	LEFT JOIN保留主表所有数据，COALESCE处理NULL
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

--建表语句
CREATE TABLE t5_order_detail (
    order_id   bigint COMMENT '订单ID',
    product_id string COMMENT '商品ID',
    qty        int    COMMENT '购买数量'
) COMMENT '订单明细表';

-- 插入数据
insert into t5_order_detail(order_id, product_id, qty)
values
(1001, 'A', 2),
(1001, 'B', 1),
(1001, 'C', 1),
(1002, 'A', 1),
(1002, 'B', 3),
(1003, 'B', 1),
(1003, 'C', 2),
(1004, 'A', 1),
(1004, 'C', 1),
(1004, 'D', 1),
(1005, 'A', 2),
(1005, 'B', 1);

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题