SQL实现：计算自上次活动以来的天数（含当前周）

修正后的查询语句（通用SQL版本） #

SELECT 
    ID, 
    DATE, 
    CHANNEL, 
    VENDOR, 
    ENG,
    CASE 
        WHEN ENG > 0 THEN 0
        ELSE DATE - LAST_VALUE(CASE WHEN ENG > 0 THEN DATE END IGNORE NULLS) OVER (
            PARTITION BY ID, CHANNEL, VENDOR 
            ORDER BY DATE 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
    END AS n_days
FROM tab1
ORDER BY ID, DATE;

针对不支持IGNORE NULLS的SQL方言（如MySQL）的替代方案 #

SELECT 
    ID, 
    DATE, 
    CHANNEL, 
    VENDOR, 
    ENG,
    CASE 
        WHEN ENG > 0 THEN 0
        ELSE DATE - MAX(CASE WHEN ENG > 0 THEN DATE END) OVER (
            PARTITION BY ID, CHANNEL, VENDOR 
            ORDER BY DATE 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
    END AS n_days
FROM tab1
ORDER BY ID, DATE;

关键修改说明 #

1. 保留所有行：移除原查询中WHERE ENG>0的过滤条件，确保ENG=0的周度数据也能被纳入结果。
2. 跟踪最近活动日期：
   • 使用LAST_VALUE(CASE WHEN ENG > 0 THEN DATE END IGNORE NULLS)或MAX(CASE WHEN ENG > 0 THEN DATE END)窗口函数，在ID、CHANNEL、VENDOR分区内，按日期排序获取当前行之前最近的一次有活动（ENG>0）的日期。
   • IGNORE NULLS（部分SQL支持）会跳过ENG=0对应的空值，直接定位到最近的有效活动日期；MAX()函数则通过窗口范围自动保留最新的有效活动日期，适配不支持IGNORE NULLS的场景。
3. 计算间隔天数：通过CASE逻辑判断，若当前行有活动则n_days设为0，否则用当前日期减去最近活动日期得到间隔天数。

内容的提问来源于stack exchange，提问作者Krishnang K Dalal