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

题目描述：
在电商场景中，用户可以将多个店铺的商品加入购物车并合并为一笔交易（同一交易编号 trade_id）进行付款。请编写 SparkSQL 统计：包含 3 个及以上不同店铺商品的交易笔数占总交易笔数的比例（结果保留4位小数）。

1. 基础数据准备

订单明细表（user_order_detail）

数据解析说明：

• 交易 T101：包含店铺 S1, S2, S3（共3个不同店铺），满足条件。
• 交易 T102：包含店铺 S1, S2（共2个不同店铺），不满足条件。
• 交易 T103：包含店铺 S1, S2, S3, S4（共4个不同店铺），满足条件。
• 交易 T104：包含店铺 S1（共1个店铺），不满足条件。
• 总交易笔数：4笔 (T101, T102, T103, T104)。
• 满足条件的交易笔数：2笔 (T101, T103)。
• 目标比例：2 / 4 = 0.5000 (50.00%)。

2. SparkSQL 核心解法

WITH trade_shop_summary AS (
    -- Step 1: 统计每笔交易对应的独立店铺数量
    SELECT 
        trade_id, 
        COUNT(DISTINCT shop_id) AS shop_cnt
    FROM 
        user_order_detail
    GROUP BY 
        trade_id
)
-- Step 2: 计算符合条件的交易占比
SELECT 
    COUNT(CASE WHEN shop_cnt >= 3 THEN 1 END) AS multi_shop_trade_cnt,
    COUNT(1) AS total_trade_cnt,
    ROUND(
        COUNT(CASE WHEN shop_cnt >= 3 THEN 1 END) / COUNT(1), 
        4
    ) AS ratio
FROM 
    trade_shop_summary;

输出结果：

3. 面试官视角：SparkSQL 深度分析与优化

在实际面试中，写出上述 SQL 只能拿到基础分。面试官通常会针对 Spark 的底层执行机制和高并发大数据量场景进行追问。以下是应对追问的黄金解析：

核心考点一：COUNT(DISTINCT) 的数据倾斜问题与优化

• 问题痛点：
  在 trade_shop_summary 子查询中，我们使用了 COUNT(DISTINCT shop_id)。在 Spark 中，这会触发 Shuffle。如果某笔交易（例如大促期间的某些企业大单、代购订单）包含了极高数量的商品，会导致该 trade_id 被分发到单个 Reducer 上进行去重，从而引发数据倾斜（Data Skew）。

• 解决方案（两阶段聚合）：
  可以通过“先分组去重，再计数”的方式打破瓶颈，避免直接使用 COUNT(DISTINCT)：

  sql

  WITH temp_distinct AS (
      -- 先按 trade_id 和 shop_id 进行去重分组，分散倾斜压力
      SELECT trade_id, shop_id
      FROM user_order_detail
      GROUP BY trade_id, shop_id
  ),
  trade_shop_summary AS (
      -- 此时只需 COUNT(1) 即可，无去重压力
      SELECT trade_id, COUNT(1) AS shop_cnt
      FROM temp_distinct
      GROUP BY trade_id
  )
  SELECT 
      ROUND(SUM(CASE WHEN shop_cnt >= 3 THEN 1 ELSE 0 END) / COUNT(1), 4) AS ratio
  FROM trade_shop_summary;

核心考点二：Spark 执行计划与 Shuffle 减负

1. Map 阶段本地聚合（Map-side Aggregation）：
   在执行第一步 GROUP BY trade_id 时，Spark 会默认开启 spark.sql.mapOutputs.minSize 等相关优化。对于普通的 COUNT 聚合，Spark 会在 Map 端先进行 Aggregate，减少传输到 Executor 的数据量。
2. 避免笛卡尔积：
   该题切忌使用 JOIN 自身来判断多店铺。有些候选人会尝试用 a.shop_id <> b.shop_id AND b.shop_id <> c.shop_id 的自关联方式来凑 3 个店铺，这在海量数据下会产生严重的 Shuffle 甚至内存溢出（OOM）。一律优先使用 GROUP BY + CASE WHEN 解决分类计数问题。

核心考点三：精度丢失与边界处理

• 除数非空/非零处理：在工业界代码中，必须防范分母为 0 的情况。可以使用 NULLIF(COUNT(1), 0) 来规避 Division by zero 报错。
• 类型转换：Spark 中两个整型相除可能会丢失精度，但 SparkSQL 在进行 / 除法运算时会自动隐式转换为 Double。为了代码的安全性和可读性，显式使用 CAST(xxx AS DOUBLE) 也是加分项。