Databricks中基于连续28天库存数据的超库存标识实现

场景与需求 #

现有数据集包含数千种物料、数百万行记录，日期列存在跳变和重复情况。需要实现：针对每个物料，检查当前行及之后连续28天内，每日的Projected Stock是否均达到Target Stock的200%；若存在任意一行满足该条件，则给该物料的所有行添加Overstock标识（值为"Yes"）。

现有基础代码（已修正） #

你提供的CTE可优化，去掉无用的group by all和CTE内的order by（CTE内排序不生效），修正后如下：

Projected_stock as (
    select 
      Material,
      Date,
      Stock,
      Target_Stock,
      Demand,
      Supply,
      sum(Stock - Demand + Supply) over (partition by material order by date rows between unbounded preceding and current row) as Projected_Stock
    from datasource
)

完整解决方案代码 #

结合日期去重、窗口范围检查、全物料标识关联，完整SQL代码如下：

WITH cleaned_data AS (
    -- 去重每个物料的重复日期记录，避免干扰后续计算
    SELECT DISTINCT
        Material,
        Date,
        Stock,
        Target_Stock,
        Demand,
        Supply
    FROM datasource
    -- 若日期是字符串类型，需先转换为日期格式，比如：
    -- WHERE TO_DATE(Date, 'yyyy-MM-dd') IS NOT NULL
),
projected_stock AS (
    SELECT
        Material,
        Date,
        Stock,
        Target_Stock,
        Demand,
        Supply,
        -- 计算累计预计库存
        SUM(Stock - Demand + Supply) OVER (
            PARTITION BY Material 
            ORDER BY Date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Projected_Stock
    FROM cleaned_data
),
daily_overstock_check AS (
    SELECT
        ps.*,
        -- 检查当前日期及之后28天内的所有记录是否均达标
        CASE
            WHEN EVERY(Projected_Stock >= Target_Stock * 2) OVER (
                PARTITION BY Material 
                ORDER BY Date 
                RANGE BETWEEN CURRENT ROW AND INTERVAL 28 DAY FOLLOWING
            ) THEN 'Yes'
            ELSE 'No'
        END AS Daily_Overstock
    FROM projected_stock
),
-- 标记整个物料是否满足超库存条件（只要有一行达标，所有行都标记Yes）
material_overstock_flag AS (
    SELECT
        Material,
        MAX(Daily_Overstock) AS Overstock
    FROM daily_overstock_check
    GROUP BY Material
)
-- 关联回原始数据集，给所有行添加超库存标识
SELECT
    d.*,
    mof.Overstock
FROM datasource d
JOIN material_overstock_flag mof
    ON d.Material = mof.Material
ORDER BY d.Material, d.Date;

关键逻辑说明 #

1. 数据清洗：通过DISTINCT去重同一物料的重复日期记录，避免窗口计算出错。
2. 28天范围检查：使用RANGE BETWEEN CURRENT ROW AND INTERVAL 28 DAY FOLLOWING窗口，自动适配日期跳变的情况，无需补全缺失日期，只要在当前日期后28天内的记录都会被纳入检查。
3. 全物料标识：通过MAX(Daily_Overstock)聚合，只要该物料存在任意一行满足条件，所有行都会被标记为"Yes"（因为"Yes"的排序优先级高于"No"）。
4. 性能优化：确保Material字段有分区或统计信息，Databricks会自动优化窗口函数的执行效率，适配数百万行的数据集。

内容的提问来源于stack exchange，提问作者Kristian Tonev