基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

统计同一笔购物车合并付款的交易中，包含 3 个及以上不同店铺商品的订单占总交易笔数的比例

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

1. 题目背景

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

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

2. 示例数据表

交易明细表：shoppping_cart_transactions

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

交易ID (transaction_id) 子订单ID (order_id) 商品ID (product_id) 店铺ID (shop_id) 支付金额 (pay_amount)
T1001 O2023100101 P001 S01 120.00
T1001 O2023100102 P002 S01 80.00
T1001 O2023100103 P003 S02 50.00
T1002 O2023100104 P004 S01 200.00
T1002 O2023100105 P005 S02 150.00
T1002 O2023100106 P006 S03 100.00
T1003 O2023100107 P007 S02 30.00
T1003 O2023100108 P008 S03 45.00
T1003 O2023100109 P009 S04 60.00
T1003 O2023100110 P010 S01 120.00
T1004 O2023100111 P011 S05 99.00
T1005 O2023100112 P012 S01 15.00
T1005 O2023100113 P013 S01 35.00

数据逻辑分析说明：

  • 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. 期望输出结果

multi_shop_transaction_ratio
0.4000

4. SQL 解答方案

方案一：使用 CTE (公用表表达式) 与 AVG 函数（推荐，优雅高效）
sql 
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 计数（直观易懂）
sql 
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) 是必不可少的临门一脚，体现了候选人对需求细节的把控。
00:00
00:00