面试真题

题目：计算销售额贡献了平台前 80% 的热销商品数量，占平台在售商品总数量的比例。

数据准备

1. 商品信息表 (product_info)

记录平台所有商品的基本信息及在售状态。

2. 订单详情表 (order_detail)

记录商品的销售流水数据。

解题思路

这是一道典型的二八定律（帕累托法则）分析题，在电商数据分析面试中非常高频。我们需要拆解为以下几个步骤：

1. 过滤在售商品并计算总销售额：

   • 目标人群是在售商品（is_on_sale = 1）。
   • 需要计算每个在售商品的累计销售额。对于没有销售额的在售商品，使用 COALESCE(sales, 0) 填充。

2. 计算累计销售额占比（窗口函数）：

   • 将在售商品按销售额从大到小排列。
   • 使用窗口函数 SUM() OVER(...) 计算累计销售额。
   • 使用窗口函数 SUM() OVER() 获取在售商品的总销售额。

3. 确定“贡献前 80% 销售额”的商品边界：

   • 判定条件：排除当前商品后的前序累计销售额未达到 80%。
   • 公式：(当前累计销售额 - 当前商品销售额) < 总销售额 * 0.8。这能确保刚好把使累计销售额跨越 80% 边界的那个商品也算入“热销商品”。

4. 计算比例：

   • 统计符合条件的商品数（分子）。
   • 统计所有在售商品总数（分母）。
   • 相除得到最终比例。

SparkSQL 解决方案

WITH tmp_product_sales AS (
    -- Step 1: 筛选在售商品，并关联订单表计算每个商品的销售总额
    SELECT 
        p.product_id,
        COALESCE(SUM(o.sales_amount), 0) AS product_sales
    FROM product_info p
    LEFT JOIN order_detail o ON p.product_id = o.product_id
    WHERE p.is_on_sale = 1
    GROUP BY p.product_id
),
tmp_cum_sales AS (
    -- Step 2: 使用窗口函数计算累计销售额、总销售额以及在售商品总数
    SELECT 
        product_id,
        product_sales,
        -- 按销售额降序，计算累计销售额
        SUM(product_sales) OVER (ORDER BY product_sales DESC, product_id ASC) AS cum_sales,
        -- 计算所有在售商品的总销售额
        SUM(product_sales) OVER () AS total_sales,
        -- 计算在售商品的总个数
        COUNT(1) OVER () AS total_product_count
    FROM tmp_product_sales
),
tmp_hot_products AS (
    -- Step 3: 筛选出贡献前 80% 销售额的商品
    -- 逻辑：剔除当前商品销售额后，前面的累加额还不足 80% 的商品，都算作贡献了前 80% 的商品
    SELECT 
        product_id,
        total_product_count
    FROM tmp_cum_sales
    WHERE (cum_sales - product_sales) < total_sales * 0.8
)
-- Step 4: 计算热销商品占在售商品的比例
SELECT 
    COUNT(1) AS hot_product_count,
    MAX(total_product_count) AS total_on_sale_count,
    ROUND(COUNT(1) / MAX(total_product_count), 4) AS hot_ratio
FROM tmp_hot_products;

输出结果：

(解释：在售商品共 8 个，总销售额 100,000。P01(50k) + P02(30k) = 80,000，正好占 80%。所以热销商品有 2 个，占比 2/8 = 25%)

核心考点解析（面试加分项）

在面试中，仅仅写出 SQL 是不够的。面试官往往会针对你的代码进行追问，以下是针对本题的深度分析和答题话术：

1. 为什么不用 cum_sales <= total_sales * 0.8 作为筛选条件？

• 边界溢出问题：如果第一名商品的销售额就占了 81%，用 <= 会导致筛选出的热销商品数为 0，这显然不符合业务逻辑。
• 标准解法：使用 (cum_sales - product_sales) < total_sales * 0.8（即：历史累加不含自己 < 80%）。这样能保证至少有一个商品被拉进来，且能精准定位到让累计销售额刚好达到或突破 80% 阈值的那个临界商品。

2. 窗口函数中的 ORDER BY 为什么要加 product_id 区分？

• 在 Spark 中，如果 ORDER BY 字段的值相同（比如有两个商品销售额都是 0），不加唯一键（如 product_id）会导致窗口函数输出不确定的累加结果（Non-deterministic）。
• 加上 product_id 可以强制实现全局唯一排序，保证 SQL 多次运行结果的一致性。

3. 性能优化：如何避免窗口函数引发的全局单点瓶颈（Data Skew）？

• 痛点：SUM(product_sales) OVER ()（没有 PARTITION BY）在 Spark 中会把所有数据分发到单个 Executor 上进行计算，如果商品量级达到千万级，会导致严重的 OOM（内存溢出）。
• 面试官追问优化方案：
  在实际生产中，我们可以通过两阶段聚合或广播变量来优化：
  sql

  -- 优化方案：先通过聚合计算求出总销售额和总数，再与大表进行 MapJoin (Broadcast Join)
  WITH tmp_product_sales AS (
      SELECT p.product_id, COALESCE(SUM(o.sales_amount), 0) AS product_sales
      FROM product_info p LEFT JOIN order_detail o ON p.product_id = o.product_id
      WHERE p.is_on_sale = 1 GROUP BY p.product_id
  ),
  tmp_stats AS (
      -- 这一步只会产出一行数据，非常适合 Broadcast
      SELECT SUM(product_sales) AS total_sales, COUNT(1) AS total_product_count 
      FROM tmp_product_sales
  )
  SELECT ...
  FROM tmp_product_sales p
  CROSS JOIN tmp_stats s -- Spark 会自动将其转化为 Broadcast Nested Loop Join，避免了全局单点 Shuffle

4. 业务边界考虑：

• 必须要用 LEFT JOIN 保留在售但从未售出（销售额为 NULL）的商品，并用 COALESCE(..., 0) 填充。如果直接用 INNER JOIN，会导致分母（在售商品总数）偏小，从而高估热销商品的比例。