面试真题：计算商品库存可售天数（DOI）并筛选积压商品

1. 题目背景

在电商和零售供应链管理中，DOI（Days of Inventory，库存可售天数） 是衡量库存健康度的核心指标。
计算公式为：$$\text{DOI} = \frac{\text{当前库存量}}{\text{过去 30 天的日均销量}}$$
如果某种商品的 DOI 超过 180 天，通常被定义为严重积压商品，需要警惕资金占用风险并及时促销清仓。

请根据给定的商品库存表和销售明细表，编写 SparkSQL 查询，找出所有 DOI > 180天 的积压商品，输出商品ID、商品名称、当前库存、过去30天总销量、日均销量及 DOI（保留1位小数）。

2. 基础数据

表 1：商品库存表 (product_inventory)

表 2：销售明细表 (sales_records)

注：假设当前计算基准日期为 2023-10-31，过去30天的区间为 2023-10-02 至 2023-10-31。

3. 期望输出结果

4. SparkSQL 解决方案

WITH sales_30d AS (
    -- 1. 聚合过去30天内每种商品的销量
    SELECT 
        product_id,
        COALESCE(SUM(quantity), 0) AS total_sales_30d
    FROM 
        sales_records
    WHERE 
        sale_date >= DATE_SUB(CAST('2023-10-31' AS DATE), 30)
        AND sale_date <= CAST('2023-10-31' AS DATE)
    GROUP BY 
        product_id
),

doi_calculation AS (
    -- 2. 关联库存表与销量表，计算日均销量和 DOI
    -- 使用 LEFT JOIN 防止过去30天销量为0的商品被过滤掉（这些更是潜在的积压商品）
    SELECT 
        i.product_id,
        i.product_name,
        i.current_stock,
        COALESCE(s.total_sales_30d, 0) AS total_sales_30d,
        ROUND(COALESCE(s.total_sales_30d, 0) / 30.0, 4) AS avg_daily_sales,
        CASE 
            -- 处理除数为0的极端边界情况：若30天销量为0，则DOI设为极大值（如9999）代表无限可售
            WHEN COALESCE(s.total_sales_30d, 0) = 0 THEN 9999.0
            ELSE ROUND(i.current_stock / (COALESCE(s.total_sales_30d, 0) / 30.0), 1)
        END AS doi
    FROM 
        product_inventory i
    LEFT JOIN 
        sales_30d s ON i.product_id = s.product_id
)

-- 3. 筛选出 DOI 大于 180 天的积压商品
SELECT 
    product_id,
    product_name,
    current_stock,
    total_sales_30d,
    ROUND(avg_daily_sales, 1) AS avg_daily_sales,
    doi
FROM 
    doi_calculation
WHERE 
    doi > 180
ORDER BY 
    doi DESC;

5. 核心考点与 SparkSQL 深度分析

在面试中，这道题看似简单，但其实隐藏了多个高级考点。面试官主要通过此题考察以下几点：

① 边界条件与“除零”异常处理（面试核心加分项）

• 痛点：如果某种商品在过去30天内完全没有销售记录（如 P003），直接进行除法计算 current_stock / (total_sales / 30) 会导致 NULL 或 Division by zero 报错。
• 解决办法：
  • 使用 LEFT JOIN 保留无销售记录的库存商品。
  • 配合 COALESCE(s.total_sales_30d, 0) 将 NULL 转为 0。
  • 使用 CASE WHEN 捕获销量为 0 的情况，将其 DOI 赋予一个极大值（例如 9999）或者特殊标识。这体现了生产级代码的健壮性。

② 日期函数的准确运用

• 过去 30 天的范围界定，标准写法是使用 DATE_SUB(current_date(), 30)。
• 在面试或测试中，通常会使用一个固定的基准日期（如 CAST('2023-10-31' AS DATE)）来保证结果幂等。

③ 性能优化：Broadcast Hash Join (广播连接)

• 场景分析：商品库存表 product_inventory 通常是维度表，数据量相对较小（万级或十万级）；而 sales_records 是流水事实表，数据量极大（千万级或亿级）。
• 优化手段：
  • 在 Spark 引擎中，为了避免大范围的 Shuffle，应该主动将小表（库存表）进行广播。
  • SQL 写法：可以使用 Hint 强制广播：
    sql

    SELECT /*+ BROADCAST(i) */ ... FROM product_inventory i LEFT JOIN sales_30d s ...

  • 这将原本默认的 SortMergeJoin 转换为 BroadcastHashJoin，执行效率能提升数倍。

④ 聚合与过滤的先后顺序

• 必须先过滤过去 30 天的销售明细，再进行 Group By 聚合。不能先全局聚合再去过滤日期，否则会导致全表扫描和无用的 Shuffle 计算。