播面

面试真题：商品库存可售天数（DOI）及积压商品分析

题目背景：
在零售和电商供应链管理中，库存可售天数（Days of Inventory, 简称 DOI）是一个核心指标，用于衡量当前库存水平能支撑多久的销售。计算公式为：
$$\text{DOI} = \frac{\text{当前库存量}}{\text{过去 30 天的日均销量}}$$
现在，你需要帮助供应链团队识别积压商品（DOI > 180天），以便及时进行促销或清仓处理。

已知：

1. 评估基准日期为 2023-10-31。
2. 过去 30 天的范围定义为：[2023-10-02, 2023-10-31]（含起止日期）。
3. 如果某商品在过去 30 天内没有销售记录，其日均销量视为 0，可售天数视为无限大（在 SQL 中可用特定大数值如 9999 或 NULL 表示，并判定为积压商品）。

示例数据

1. 商品库存表 (inventory)

2. 销售明细表 (sales_history)

注：仅展示与计算相关的部分代表性销售数据

期望输出

解释：

• 商品 101：30天销量30，日均1，DOI=500/1=500 > 180，属于积压。
• 商品 102：30天销量300，日均10，DOI=1000/10=100 <= 180，不属于积压。
• 商品 103：30天销量0，日均0，DOI设为9999（无限大），属于积压。
• 商品 104：30天销量150，日均5，DOI=2000/5=400 > 180，属于积压。
• 商品 105：30天销量10（10-01那笔不计入），日均0.33，DOI=50 / 0.333 = 150 <= 180，不属于积压。

SQL 解决方案 (MySQL 8.0 / PostgreSQL 通用)

WITH sales_30d AS (
    -- 步骤 1: 筛选并计算过去 30 天每个商品的销售总量
    SELECT 
        product_id,
        SUM(quantity_sold) AS total_sales
    FROM 
        sales_history
    WHERE 
        sale_date BETWEEN '2023-10-02' AND '2023-10-31'
    GROUP BY 
        product_id
),
doi_calculation AS (
    -- 步骤 2: 关联库存表，计算日均销量和 DOI，处理零销售的边界情况
    SELECT 
        i.product_id,
        i.product_name,
        i.current_stock,
        COALESCE(s.total_sales, 0) AS total_sales_30d,
        ROUND(COALESCE(s.total_sales, 0) / 30.0, 2) AS avg_daily_sales,
        CASE 
            WHEN COALESCE(s.total_sales, 0) = 0 THEN 9999.00  -- 处理除以 0 的情况
            ELSE ROUND(i.current_stock / (COALESCE(s.total_sales, 0) / 30.0), 2)
        END AS doi
    FROM 
        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,
    avg_daily_sales,
    doi
FROM 
    doi_calculation
WHERE 
    doi > 180
ORDER BY 
    doi DESC;

核心考点与深度解析

在面试中，这道题看似简单，实则暗藏多个考察候选人业务思维和SQL工程健壮性的陷阱。

1. 边界条件处理（零除报错 - Zero Division Error）

• 痛点：这是实际开发中最容易导致 SQL 报错的场景。如果某商品在过去30天内没有任何销量，则 total_sales 为 NULL（或 0）。直接进行 current_stock / avg_daily_sales 会触发除零错误。
• 解答话术：“在计算分母可能为 0 的指标时，我习惯使用 CASE WHEN 或 NULLIF 来进行防御性编程。在本题中，当销售量为 0 时，我将其 DOI 赋予一个极大值 9999，以此来代表无限大，确保业务上能将其归为积压商品，同时保证了 SQL 运行的健壮性。”

2. 关联方式的选择（LEFT JOIN vs INNER JOIN）

• 痛点：若使用 INNER JOIN 关联库存表和销售表，那些在过去 30 天从未销售过的商品（如商品 103）就会在 sales_history 中无匹配记录而被直接过滤掉。但事实上，这部分商品才是最严重的积压库存！
• 解答话术：“我选择使用 LEFT JOIN 以 inventory 为主表进行关联。因为无销售记录的商品代表了流动性极差的死库存，必须保留在结果集中。通过 COALESCE(s.total_sales, 0) 将未关联到的商品的销量转化为 0，从而准确计算其 DOI。”

3. 业务时间窗口计算

• 痛点：如何精准圈定“过去30天”。
• 解答话术：“在实际生产环境中，我不会写死固定日期，而是会使用动态函数，例如在 MySQL 中使用 sale_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)。这里为了配合面试题给定的静态数据集，我使用了明确的日期区间过滤，以保证计算的可复现性。”

4. 浮点数精度（Type Casting / Rounding）

• 注意：在某些 SQL 引擎中（如 PostgreSQL），两个整数相除（如 total_sales / 30）会自动向下取整为整数（例如 10 / 30 = 0），导致日均销量失真。
• 技巧：在分母使用带有小数点的 30.0，强迫引擎进行浮点数运算，并用 ROUND(..., 2) 保证输出格式的美观性。