播面

面试题：购物车合并付款多店铺交易占比统计

1. 题目背景

在电商场景中，用户经常会把多个店铺的商品加入购物车，然后一次性合并下单付款。这种行为被称为“合并付款”。为了评估跨店铺营销活动的效果以及用户的拼单行为特征，产品运营部门需要监控“同一笔合并付款交易中，包含 3 个及以上不同店铺商品的交易笔数占总交易笔数的比例”。

请根据给定的交易明细表，编写一段 SQL 统计该比例（结果保留 4 位小数）。

2. 示例数据表

交易明细表：shoppping_cart_transactions

该表记录了每一笔合并支付订单中的具体商品明细。一条记录代表合并支付中的一个商品。

数据逻辑分析说明：

• T1001：包含 2 个不同店铺（S01, S02）
• T1002：包含 3 个不同店铺（S01, S02, S03） -> 满足条件
• T1003：包含 4 个不同店铺（S02, S03, S04, S01） -> 满足条件
• T1004：包含 1 个不同店铺（S05）
• T1005：包含 1 个不同店铺（S01，虽有两件商品但属于同店）
• 总交易笔数：5 笔（T1001 ~ T1005）
• 满足条件的交易笔数：2 笔（T1002, T1003）
• 期望占比：2 / 5 = 0.4000 (40.00%)

3. 期望输出结果

4. SQL 解答方案

方案一：使用 CTE (公用表表达式) 与 AVG 函数（推荐，优雅高效）

WITH transaction_shop_count AS (
    SELECT 
        transaction_id,
        COUNT(DISTINCT shop_id) AS distinct_shop_cnt
    FROM 
        shoppping_cart_transactions
    GROUP BY 
        transaction_id
)
SELECT 
    ROUND(
        AVG(CASE WHEN distinct_shop_cnt >= 3 THEN 1.0 ELSE 0.0 END), 
        4
    ) AS multi_shop_transaction_ratio
FROM 
    transaction_shop_count;

方案二：使用子查询与 SUM 计数（直观易懂）

SELECT 
    ROUND(
        SUM(CASE WHEN distinct_shop_cnt >= 3 THEN 1 ELSE 0 END) * 1.0 
        / COUNT(transaction_id), 
        4
    ) AS multi_shop_transaction_ratio
FROM (
    SELECT 
        transaction_id,
        COUNT(DISTINCT shop_id) AS distinct_shop_cnt
    FROM 
        shoppping_cart_transactions
    GROUP BY 
        transaction_id
) t;

5. 核心考点与深度解析

在面试中，这道题看似简单，但能非常好地考察候选人的 SQL 基础功底、细节处理能力以及业务理解深度。以下是该题的核心考点及面试官关注的加分项：

1. 颗粒度控制（Grouping）与去重计数（Distinct Count）

• 痛点：一笔合并交易（transaction_id）下会有多个商品，甚至同一个店铺的多个商品（如 T1005 买了 2 件 S01 的商品）。
• 考点：必须先使用 GROUP BY transaction_id 将粒度收拢到“交易级”，然后使用 COUNT(DISTINCT shop_id) 算出每笔交易涉及的去重店铺数。如果直接 COUNT(shop_id)，会导致同一店铺重复计算，从而产生错误数据。

2. 计算比例的巧妙写法（AVG 技巧）

• 在方案一中，使用了 AVG(CASE WHEN distinct_shop_cnt >= 3 THEN 1.0 ELSE 0.0 END)。
• 面试官加分点：这是计算“占比/转化率”非常高级且常用的技巧。
  • 如果满足条件，赋予 1.0，否则赋予 0.0。
  • 对其求平均值（AVG），在数学上等同于 满足条件的个数 / 总个数。
  • 这种写法不仅代码精简，而且省去了再次写 SUM(...) / COUNT(...) 的繁琐，可读性极佳。

3. 防止整数除法截断（Integer Division）

• 痛点：在很多 SQL 引擎中（如 PostgreSQL、SQL Server），两个整数相除（如 2 / 5）会默认执行整除，结果为 0。
• 考点：必须将分子或分母乘以 1.0 转换为浮点数，或者显式使用 CAST。
  • 方案二中通过 SUM(...) * 1.0 / COUNT(...) 巧妙地解决了这个问题。

4. 结果美化（ROUND）

• 题目要求“结果保留 4 位小数”，因此在最外层包裹 ROUND(..., 4) 是必不可少的临门一脚，体现了候选人对需求细节的把控。