拼多多大数据面试SQL-商品的关联购买分析

⚠️ 待修正

一、题目背景

这道题来自拼多多的数据分析岗面试。拼多多是社交裂变电商，数据分析师需要从海量业务数据中挖掘洞见。

业务场景：用户消费行为相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目

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

假设有一张订单明细表 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 order_detail a
inner join order_detail b
  on a.order_id = b.order_id
 and a.product_id < b.product_id
group by a.product_id, b.product_id

查询结果

+-----------+-----------+---------------+
| product_a | product_b | co_order_cnt  |
+-----------+-----------+---------------+
| A         | B         | 3             |
| A         | C         | 2             |
| A         | D         | 1             |
| B         | C         | 2             |
| C         | D         | 1             |
+-----------+-----------+---------------+

2.按共现次数降序取Top5

执行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 order_detail a
    inner join order_detail b
      on a.order_id = b.order_id
     and a.product_id < b.product_id
    group by a.product_id, b.product_id
) t
order by co_order_cnt desc
limit 5

查询结果

+-----------+-----------+---------------+
| product_a | product_b | co_order_cnt  |
+-----------+-----------+---------------+
| A         | B         | 3             |
| A         | C         | 2             |
| B         | C         | 2             |
| A         | D         | 1             |
| C         | D         | 1             |
+-----------+-----------+---------------+

五、常见坑点

坑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 order_detail (
    order_id   bigint COMMENT '订单ID',
    product_id string COMMENT '商品ID',
    qty        int    COMMENT '购买数量'
) COMMENT '订单明细表';

-- 插入数据
insert into 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真题