9 / 12

发现产品组合的秘密

当我们走进超市，经常会看到某些商品被摆放在一起，比如啤酒和薯片、面包和牛奶。这不是偶然的，而是基于市场篮子分析的结果。市场篮子分析（Market Basket Analysis）是一种经典的数据挖掘技术，它通过分析客户同时购买哪些产品，来发现产品之间的关联关系。在这一节课中，我们将学习如何使用SQL提取和分析客户的购物篮数据，并通过Excel来可视化这些购买组合，发现隐藏在购物篮中的商业机会。

发现产品组合的秘密

市场篮子分析

市场篮子分析的核心思想很简单：如果客户经常同时购买某些产品，说明这些产品之间存在关联关系。这种关联关系可能是有意的（比如客户确实需要这些产品一起使用），也可能是无意的（比如客户在购物时看到了某个产品，临时决定购买）。

市场篮子分析的应用

市场篮子分析在商业中有很多应用。最直接的应用是产品推荐和交叉销售。如果我们知道客户购买了产品A，而产品A和产品B经常一起购买，我们就可以向客户推荐产品B。

另一个应用是商品陈列优化。如果某些产品经常一起购买，我们可以将它们摆放在一起，方便客户购买，提高销售额。或者，我们可以将它们分开摆放，让客户在寻找产品的过程中看到更多商品，增加购买机会。

市场篮子分析还可以用于库存管理。如果某些产品经常一起购买，我们可以根据其中一个产品的销售情况来预测另一个产品的需求，从而优化库存。

市场篮子分析关注的是"同时购买"，而不是"先后购买"。虽然时间顺序也可能有意义，但市场篮子分析主要关注的是在同一笔交易中购买的产品组合。

购物篮的概念

在数据分析中，"购物篮"（Shopping Basket）指的是客户在一次交易中购买的所有产品。一个订单就是一个购物篮，一个购物篮可能包含一个或多个产品。

理解购物篮的概念很重要，因为市场篮子分析就是基于购物篮来进行的。我们需要识别每个购物篮中包含哪些产品，然后分析哪些产品经常出现在同一个购物篮中。

让我们先创建一些测试数据，帮助我们理解分析过程。假设我们有一个订单详情表，记录了每个订单中购买的产品：

|
-- 创建订单详情测试表
CREATE TABLE 订单详情测试表 (
    订单ID INT,
    产品名称 VARCHAR(50),
    产品类别 VARCHAR(20),
    数量 INT,
    单价 DECIMAL(10,2)
);
 
-- 插入测试数据
INSERT INTO 订单详情测试表 VALUES
(1, '牛奶', '食品', 2, 15.00),
(1, '面包', '食品', 1, 8.00),
(1, '鸡蛋', '食品', 1, 12.00),
(2, '啤酒', '饮料', 6, 5.00),
(2, '薯片', '零食', 2, 10.00),
(2, '花生', '零食', 1, 8.00),
(3, '牛奶', '食品', 1, 15.00),
(3, '面包', '食品', 2, 8.00),
(4, '可乐', '饮料', 4, 6.00),
(4, '薯片', '零食', 1, 10.00),
(5, '啤酒', '饮料', 4, 5.00),
(5, '薯片', '零食', 3, 10.00),
(5, '花生', '零食', 2, 8.00),
(6, '牛奶', '食品', 1, 15.00),
(6, '鸡蛋', '食品', 1, 12.00),
(7, '面包', '食品', 1, 8.00),
(7, '牛奶', '食品', 1, 15.00),
(7, '鸡蛋', '食品', 1, 12.00),
(8, '可乐', '饮料', 2, 6.00),
(8, '薯片', '零食', 1, 10.00);

这个测试数据包含了8个订单，每个订单包含不同的产品组合。我们可以看到，有些产品经常一起出现，比如牛奶和面包、啤酒和薯片。通过分析这些数据，我们能够发现产品之间的关联关系。

提取购物篮数据

要进行市场篮子分析，我们首先需要提取每个购物篮中包含的产品。这看起来很简单，但实际上需要考虑很多细节。

查看每个订单的产品

最基本的查询是查看每个订单中包含了哪些产品：

|
SELECT 
    订单ID,
    产品名称,
    数量,
    单价,
    数量 * 单价 AS 小计
FROM 订单详情测试表
ORDER BY 订单ID, 产品名称;

|
+----------+--------------+--------+--------+--------+
| 订单ID   | 产品名称     | 数量   | 单价   | 小计   |
+----------+--------------+--------+--------+--------+
|        1 | 牛奶         |      2 |  15.00 |  30.00 |
|        1 | 面包         |      1 |   8.00 |   8.00 |
|        1 | 鸡蛋         |      1

这个查询会返回每个订单的详细产品信息。从结果中，我们能够看到每个购物篮中包含哪些产品。

统计每个订单的产品数量

为了更全面了解每个购物篮的丰富程度，我们可以对每个订单进行统计，包括：一是订单内包含了多少种不同的产品，二是这些产品总共购买了多少件，三是这个订单的总金额是多少。这些数据有助于我们判断每个购物篮的多样性与消费水平。

|
SELECT 
    订单ID,
    COUNT(DISTINCT 产品名称) AS 产品种类数,
    SUM(数量) AS 总数量,
    SUM(数量 * 单价) AS 订单总金额
FROM 订单详情测试表
GROUP BY 订单ID
ORDER BY 产品种类数 DESC;

|
+----------+-----------------+-----------+-----------------+
| 订单ID   | 产品种类数      | 总数量    | 订单总金额      |
+----------+-----------------+-----------+-----------------+
|        1 |               3 |         4 |           50.00 |
|        2 |               3 |         9 |           58.00 |
|        5 |               3 |         9 |           66.00 |
|

这个查询统计每个订单的产品种类数、总数量和总金额。产品种类数越多，说明这个购物篮越丰富，可能包含更多的产品组合信息。

识别最常见的产品组合

现在，让我们找出哪些产品经常一起出现。我们可以通过自连接来找出所有出现在同一个订单中的产品对：

|
SELECT 
    o1.产品名称 AS 产品A,
    o2.产品名称 AS 产品B,
    COUNT(DISTINCT o1.订单ID) AS 共现次数
FROM 订单详情测试表 o1
INNER JOIN 订单详情测试表 o2 
    ON o1.订单ID = o2.订单ID 
    AND o1.产品名称 < o2.产品名称

这个查询使用自连接来找出所有出现在同一个订单中的产品对。o1.产品名称 < o2.产品名称确保每个产品对只出现一次（比如"牛奶-面包"和"面包-牛奶"只算一次）。从结果中，我们能够看到哪些产品经常一起购买。

查询结果可能类似这样：

|
+---------+---------+--------------+
| 产品A   | 产品B   | 共现次数     |
+---------+---------+--------------+
| 牛奶    | 面包    |            3 |
| 牛奶    | 鸡蛋    |            3 |
| 可乐    | 薯片    |            2 |
| 啤酒    | 花生    |            2 |
| 啤酒    | 薯片    |            2 |
| 花生    | 薯片    |            2 |
| 面包    | 鸡蛋    |            2 |
+---------+---------+--------------+

从这个结果可以看出，"牛奶-鸡蛋"和"牛奶-面包"是最常见的产品组合，各出现了3次。

分析产品关联强度

仅仅知道产品经常一起出现还不够，我们还需要知道这种关联有多强。两个产品可能经常一起出现，但如果它们各自出现的频率都很高，这种关联可能只是偶然的。

计算支持度

支持度（Support）是关联规则分析中的一个重要指标，它表示某个产品组合在所有交易中出现的频率。支持度越高，说明这个组合越常见。

|
WITH 产品组合 AS (
    SELECT 
        o1.产品名称 AS 产品A,
        o2.产品名称 AS 产品B,
        COUNT(DISTINCT o1.订单ID) AS 共现次数
    FROM 订单详情测试表 o1
    INNER JOIN 订单详情测试表 o2 
        ON o1.订单ID = o2.订单ID 
        AND o1.

|
+---------+---------+--------------+--------------+--------------------+
| 产品A   | 产品B   | 共现次数     | 总订单数     | 支持度百分比       |
+---------+---------+--------------+--------------+--------------------+
| 牛奶    | 面包    |            3 |            8 |              37.50 |
| 牛奶    | 鸡蛋    |            3 |            8 |              37.50 |
| 可乐    | 薯片    |

这个查询计算每个产品组合的支持度。支持度百分比表示这个组合在多少比例的订单中出现。比如，如果"牛奶-面包"的支持度是37.5%，说明在37.5%的订单中，客户同时购买了牛奶和面包。

计算置信度

置信度（Confidence）表示在购买了产品A的订单中，有多少比例也购买了产品B。置信度越高，说明购买A后购买B的可能性越大。

|
WITH 产品A订单 AS (
    SELECT 
        产品名称 AS 产品A,
        COUNT(DISTINCT 订单ID) AS 产品A订单数
    FROM 订单详情测试表
    GROUP BY 产品名称
),
产品组合 AS (
    SELECT 
        o1.产品名称 AS 产品A,
        o2.产品名称 AS 产品B,
        COUNT(DISTINCT o1

|
+---------+---------+--------------+------------------+--------------------+
| 产品A   | 产品B   | 共现次数     | 产品A订单数      | 置信度百分比       |
+---------+---------+--------------+------------------+--------------------+
| 可乐    | 薯片    |            2 |                2 |             100.00 |
| 啤酒    | 花生    |            2 |                2 |             100.00 |
| 啤酒    | 薯片

这个查询计算每个产品组合的置信度。比如，如果"啤酒-薯片"的置信度是100%，说明所有购买啤酒的订单都同时购买了薯片。这个信息对于产品推荐非常有用。

计算提升度

提升度（Lift）表示产品A和产品B一起出现的频率，相对于它们独立出现的频率的倍数。提升度大于1，说明两个产品正相关；提升度小于1，说明两个产品负相关；提升度等于1，说明两个产品独立。

|
WITH 产品A订单 AS (
    SELECT 
        产品名称 AS 产品A,
        COUNT(DISTINCT 订单ID) AS 产品A订单数
    FROM 订单详情测试表
    GROUP BY 产品名称
),
产品B订单 AS (
    SELECT 
        产品名称 AS 产品B,
        COUNT(DISTINCT 订单ID) AS 产品B订单数
    FROM 订单详情测试表
    GROUP BY 产品名称

|
+---------+---------+--------------+------------------+------------------+--------------+-----------+
| 产品A   | 产品B   | 共现次数     | 产品A订单数      | 产品B订单数      | 总订单数     | 提升度    |
+---------+---------+--------------+------------------+------------------+--------------+-----------+
| 啤酒    | 花生    |            2 |                2 |                2 |            8 |      4.00 |
| 可乐    | 薯片    |            2 |

这个查询计算每个产品组合的提升度。提升度大于1的组合，说明两个产品确实有关联，值得关注；提升度小于1的组合，说明两个产品可能不太相关，或者甚至相互排斥。

使用Excel进行购物篮可视化

SQL查询能够帮助我们计算各种关联指标，但要直观地观察产品关联关系，Excel的可视化功能非常有用。

准备分析数据

首先，我们将SQL查询结果导出到Excel。数据应该包括产品A、产品B、共现次数、支持度、置信度、提升度等信息。

创建产品共现矩阵

产品共现矩阵是一个很好的可视化方式。矩阵的行和列都是产品名称，单元格的值表示两个产品一起出现的次数或支持度。这样，我们就能直观地看出哪些产品经常一起出现。

在Excel中创建共现矩阵，我们可以先创建一个表格，行和列都是产品名称，然后使用VLOOKUP或INDEX+MATCH函数来填充数据。或者，我们可以使用数据透视表来快速创建这样的矩阵。

创建矩阵后，我们可以使用条件格式来突出显示高值。选中数据区域，然后在"开始"选项卡中选择"条件格式"，选择"色阶"。这样，共现次数越高的组合，颜色越深，我们一眼就能看出哪些产品组合最相关。

|
	牛奶	面包	鸡蛋	啤酒	薯片	花生	可乐
牛奶	0	3	3	0	0	0	0
面包	3	0	2	0	0	0	0
鸡蛋	3	2	0	0	0	0	0
啤酒	0	0	0	0	2	2	0
薯片	0	0	0	2	0	2	2
花生	0	0	0	2	2	0	0
可乐	0	0	0	0	2	0	0

将上面的数据复制到Excel中，第一行和第一列是产品名称，矩阵中的数值表示两个产品一起出现的次数。选中包含数据的区域（不包括第一行和第一列），然后在"开始"选项卡中选择"条件格式"，选择"色阶"，Excel会根据数值大小应用不同的颜色。从数据可以看出，牛奶和面包共现3次，牛奶和鸡蛋共现3次，啤酒和薯片共现2次，说明这些产品组合关联性较强。

创建产品共现矩阵

应用市场篮子分析结果

掌握了市场篮子分析的方法后，我们可以将分析结果应用到实际的商业决策中。

优化商品陈列

基于产品关联分析结果，我们可以优化商品陈列。对于关联度高的产品组合，我们可以将它们摆放在一起，方便客户购买。比如，如果“牛奶-面包”的关联度很高，我们可以将牛奶和面包放在相邻的货架上。

或者，我们可以将它们分开摆放，让客户在寻找产品的过程中经过更多区域，看到更多商品，增加购买机会。具体策略取决于我们的目标：是提高便利性，还是增加客户在店内的停留时间和购买机会。

制定产品推荐策略

基于置信度分析，我们可以制定产品推荐策略。如果"产品A-产品B"的置信度很高，说明购买产品A的客户很可能也会购买产品B。因此，当客户购买产品A时，我们可以推荐产品B。

这种推荐可以是在线推荐（比如电商网站的产品推荐），也可以是线下推荐（比如收银员的口头推荐，或者收银台附近的商品陈列）。

优化库存管理

基于产品关联分析，我们可以优化库存管理。如果某些产品经常一起购买，我们可以根据其中一个产品的销售情况来预测另一个产品的需求。比如，如果"牛奶-面包"的关联度很高，当牛奶的销量增加时，我们可以预测面包的需求也会增加，从而提前备货。

课程小结

从数据提取、SQL分析到Excel可视化。通过这些分析，我们能够优化商品陈列、制定个性化推荐、以及更科学地进行库存管理，切实提升了交叉销售和客户价值。

进行市场篮子分析时，要合理设定分析时间范围和确保样本量的充足，还需结合业务知识理解结果，识别背后的业务逻辑。下一节课我们将进一步深入高级关联规则分析，帮助发现更复杂的产品关联关系，挖掘多产品组合的潜在商业价值。

发现产品组合的秘密 | 自在学