从品类贡献到商品组合

商品分析先回答“卖得怎么样”，再回答“哪些商品经常一起出现”。前者看销售额、销量和毛利，后者看购物篮中的共现关系。

比较品类的规模与效率

sql

SELECT
    category,
    COUNT(DISTINCT order_id) AS orders,
    SUM(quantity) AS units,
    ROUND(SUM(revenue), 2) AS revenue,
    ROUND(SUM(gross_profit), 2) AS gross_profit,
    ROUND(
        SUM(gross_profit) / SUM(revenue),
        4
    ) AS gross_margin
FROM v_sales_detail
GROUP BY category
ORDER BY revenue DESC;

category	orders	units	revenue	gross_profit	gross_margin
数码	212	468	68927.60	34149.60	0.4954
餐厨	253	636	60879.55	29519.55	0.4849
居家	255	552	43051.26	21234.26	0.4932
饮水	211	462	33559.81	17123.81	0.5102
出行	212	425	23491.23	11269.23	0.4797

数码销售额最高，饮水毛利率最高。两个结论并不冲突：销售额描述规模，毛利率描述每元销售额留下多少毛利。

把品类放进“规模 × 效率”矩阵会更容易决定下一步：

规模	毛利率	解释重点	常见动作
高	高	核心贡献品类	保证供给，避免无效折扣
高	低	有销量但利润承压	检查成本、折扣和商品结构
低	高	有效率但规模不足	测试曝光、组合和适用人群
低	低	贡献与效率都弱	缩减资源，先验证保留理由

高、低的分界可以用中位数、预算目标或历史基线。本项目先不强行贴标签，避免用很小的数据集做过度决策；我们先掌握方法，再结合业务目标设阈值。

找出具体商品的贡献

sql

SELECT
    product_name,
    category,
    COUNT(DISTINCT order_id) AS orders,
    SUM(quantity) AS units,
    ROUND(SUM(revenue), 2) AS revenue,
    ROUND(SUM(gross_profit), 2) AS gross_profit
FROM v_sales_detail
GROUP BY product_id, product_name, category
ORDER BY revenue DESC;

把 product_id 放进分组，是为了避免未来出现同名商品时被合并。展示时可以不显示编号，但计算时要使用稳定键。

商品排名还要同时看订单数与销量。某商品销售额高，可能因为单价高而不是买家多；销量高也可能来自一笔批量订单。orders、units、revenue 和 gross_profit 放在同一行，正是为了把这些可能性分开。

把订单变成购物篮

购物篮分析只关心某件商品是否出现在订单中。同一订单里买两件相同商品，仍然只算一次出现，所以先去重：

sql

WITH baskets AS (
    SELECT DISTINCT
        order_id,
        product_id,
        product_name
    FROM v_sales_detail
)
SELECT *
FROM baskets
ORDER BY order_id, product_id
LIMIT 20;

然后让购物篮表和自己连接，找同一订单里的两件不同商品：

sql

WITH baskets AS (
    SELECT DISTINCT order_id, product_id, product_name
    FROM v_sales_detail
)
SELECT
    a.product_name AS product_a,
    b.product_name AS product_b,
    COUNT(*) AS pair_orders
FROM baskets AS a
JOIN baskets AS b
  ON a.order_id = b.order_id
 AND a.product_id < b.product_id
GROUP BY
    a.product_id,
    a.product_name,
    b.product_id,
    b.product_name
ORDER BY pair_orders DESC;

条件 a.product_id < b.product_id 同时避免“商品和自己配对”以及“A+B、B+A”重复计数。

为什么第一步必须 DISTINCT？假设订单 O001 中咖啡壶分两行记录、收纳盒分三行记录。若直接连接明细，两种商品会形成 2 × 3 = 6 行组合，COUNT(*) 会把一个购物篮膨胀成多次共现；去重后，每件商品在一个订单中只有一次存在记录，共现次数才表示“多少笔订单同时包含它们”。

计算支持度、置信度和提升度

支持度表示组合占全部订单的比例；置信度表示买了 A 的订单中有多少也买了 B；提升度把这条置信度与 B 的自然出现概率比较。

\text{支持度}(A,B)=\frac{\text{同时包含 A 与 B 的订单数}}{\text{总订单数}}

\text{置信度}(A\rightarrow B)=\frac{\text{同时包含 A 与 B 的订单数}}{\text{包含 A 的订单数}}

\text{提升度}(A\rightarrow B)=\frac{\text{置信度}(A\rightarrow B)}{\text{包含 B 的订单占比}}

sql

WITH baskets AS (
    SELECT DISTINCT order_id, product_id, product_name
    FROM v_sales_detail
), pairs AS (
    SELECT
        a.product_id AS product_a_id,
        a.product_name AS product_a,
        b.product_id AS product_b_id,
        b.product_name AS product_b,
        COUNT(*) AS pair_orders
    FROM baskets AS a
    JOIN baskets AS b
      ON a.order_id = b.order_id
     AND a.product_id < b.product_id
    GROUP BY
        a.product_id, a.product_name,
        b.product_id, b.product_name
    HAVING COUNT(*) >= 30
), product_orders AS (
    SELECT
        product_id,
        COUNT(DISTINCT order_id) AS orders_with_product
    FROM baskets
    GROUP BY product_id
), totals AS (
    SELECT COUNT(DISTINCT order_id) AS total_orders
    FROM baskets
)
SELECT
    p.product_a,
    p.product_b,
    p.pair_orders,
    ROUND(1.0 * p.pair_orders / t.total_orders, 4) AS support,
    ROUND(1.0 * p.pair_orders / pa.orders_with_product, 4) AS confidence,
    ROUND(
        (1.0 * p.pair_orders / pa.orders_with_product)
        / (1.0 * pb.orders_with_product / t.total_orders),
        4
    ) AS lift
FROM pairs AS p
JOIN product_orders AS pa ON pa.product_id = p.product_a_id
JOIN product_orders AS pb ON pb.product_id = p.product_b_id
CROSS JOIN totals AS t
WHERE
    (1.0 * p.pair_orders / pa.orders_with_product)
    / (1.0 * pb.orders_with_product / t.total_orders) > 1.20
ORDER BY lift DESC, pair_orders DESC;

例如“手冲咖啡壶 + 收纳盒”共同出现在 85 笔订单中，支持度约 0.167，提升度约 4.072。提升度大于 1 表示两件商品一起出现的程度高于随机预期，但这仍然是关联，不是因果。

用 100 笔订单做一次手算：40 笔含 A，20 笔含 B，12 笔同时含 A 和 B。

\text{支持度}=12/100=12\%

\text{置信度}(A\rightarrow B)=12/40=30\%

\text{提升度}(A\rightarrow B)=30\%/20\%=1.5

这表示购买 A 的订单出现 B 的概率，是 B 在全部订单中自然出现概率的 1.5 倍。置信度有方向：A → B 的分母是 40，而 B → A 的分母是 20，所以后者是 60%；提升度在二元共现中数值相同，但推荐入口和可触达人数不同。

把规则变成可执行条件

实际推荐不能只按提升度排序。支持度太低的组合可能只是偶然。上面的完整查询已经在 pairs 中用 HAVING COUNT(*) >= 30 限制共同订单数，并在最终结果中只保留提升度大于 1.20 的组合。

阈值应随订单规模调整。订单量扩大后，可以提高最低共同订单数；如果组合太少，则先检查商品类别与分析时间范围。

在把组合放进推荐位之前，再做三层检查：

规模检查：共同订单数是否过少，是否还需要更多样本和稳定性检验。本项目先用 30 笔作为演示门槛，它不能保证关联不是偶然。
稳定性检查：把数据分成前后两个时期，组合是否都存在，而不是只由一次活动造成。
经营检查：组合后的毛利是否为正、库存是否充足、两件商品是否真的适合一起使用。

可以用下面的查询查看组合在不同月份是否稳定：

sql

WITH baskets AS (
    SELECT DISTINCT order_id, order_month, product_id
    FROM v_sales_detail
), monthly_pairs AS (
    SELECT
        a.order_month,
        a.product_id AS product_a_id,
        b.product_id AS product_b_id,
        COUNT(*) AS pair_orders
    FROM baskets AS a
    JOIN baskets AS b
      ON a.order_id = b.order_id
     AND a.product_id < b.product_id
    GROUP BY a.order_month, a.product_id, b.product_id
)
SELECT *
FROM monthly_pairs
WHERE pair_orders >= 3
ORDER BY product_a_id, product_b_id, order_month;

这张月度表不直接证明推荐有效，只用于判断关联是否持续。真正上线组合推荐后，还要通过对照实验比较转化率、连带率和组合毛利。下一节转向客户，把购买日期、频次和金额整合成可维护的客户分群。

换一组数据自己核对

下面的练习使用小型变式。请先按正文的粒度和口径作答，再展开核对。

练习 1：区分件数与购物篮存在

订单 1 的 A 商品分两行、B 商品分三行记录；订单 2 只有 A。一笔订单里商品买了多件，与同一商品分多行有什么不同？去重前后 A+B 共现计数是多少？

练习 2：用同一交易集计算三种指标

80 笔订单，20 笔含 A、40 笔含 B、10 笔同时含 A 和 B。计算支持度、A→B 与 B→A 置信度和提升度。

练习 3：分清频率阈值、稳定性与因果

组合甲共现 5 笔、提升度 8；组合乙共现 35 笔、提升度 1.3。按本节共同订单至少 30、提升度大于 1.20 的门槛保留哪个？能据此断言推荐位会增加毛利吗？

练习 4：区分品类贡献、件数和单位效率

品类甲 2 笔订单卖出 10 件，销售额 200 元、毛利 40 元；品类乙 4 笔订单卖出 4 件，销售额 400 元、毛利 60 元。谁的销售额、件数和毛利率更高？同一订单买两个品类时，各品类订单数能直接加成全局订单数吗？