如何用SQL计算产品距最近促销开始的天数（含促销后持续计数）

可以通过标记促销起始日期并向前填充的方式实现需求，以下是兼容大多数SQL方言的实现方案：

WITH promo_starts AS (
    SELECT 
        product,
        date,
        in_promo,
        -- 标记每个促销周期的第一天：当前为促销且前一天非促销（或为产品首条促销记录）
        CASE 
            WHEN in_promo = true 
                 AND (LAG(in_promo) OVER (PARTITION BY product ORDER BY date) != true 
                      OR LAG(in_promo) OVER (PARTITION BY product ORDER BY date) IS NULL)
            THEN date
            ELSE NULL
        END AS promo_start_date
    FROM product_sales_data
),
filled_promo_starts AS (
    SELECT 
        product,
        date,
        in_promo,
        -- 向前填充最近的促销起始日期，覆盖后续所有行直到下一次促销开始
        LAST_VALUE(promo_start_date IGNORE NULLS) OVER (
            PARTITION BY product 
            ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS last_promo_start
    FROM promo_starts
)
SELECT 
    product,
    date,
    in_promo,
    -- 计算当前日期与最近促销起始日的天数差，无促销记录则返回null
    CASE 
        WHEN last_promo_start IS NULL THEN NULL
        ELSE DATEDIFF(date, last_promo_start)
    END AS days_since_last_promo
FROM filled_promo_starts
ORDER BY product, date;

逻辑说明 #

1. 标记促销起始日：在promo_startsCTE中，识别每个促销周期的第一天——只有当当前行是促销状态，且前一行不是促销（或为该产品的第一条记录）时，才将当前日期标记为促销起始日。
2. 填充促销起始日：在filled_promo_startsCTE中，使用LAST_VALUE结合IGNORE NULLS，将最近的促销起始日期填充到后续所有行，直到下一次促销开始出现新的起始日。
3. 计算天数差：最后通过DATEDIFF计算当前日期与填充后的促销起始日的差值，无促销记录的行返回null，完全符合预期需求。

执行结果 #

执行上述SQL后，将得到与预期完全一致的结果：

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