Snowflake中基于窗口函数优化带日期条件的事件差值计算

问题场景 #

需要计算满足条件X的事件与前一个符合条件事件的日期差值，核心规则：若两个事件发生在同一天，第二个事件需取前一天日期而非当天更早的时间点。原实现采用关联子查询，性能较差，子查询代码如下：

(
    SELECT MAX(ld2.date)
    FROM table ld2
    WHERE  ld2.condition = 'X'
    AND ld2.id = ld.id
    AND ld2.date < ld.date
) AS last_date,

尝试嵌套窗口函数时触发错误 may not be nested inside another window function，错误代码如下：

MAX(CASE WHEN condition = 'X' AND date < LEAD(date) OVER (PARTITION BY id ORDER BY date) THEN date END) OVER (PARTITION BY id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS last_date

可行实现方案 #

以下两种方案均基于Snowflake原生窗口函数实现，性能远优于关联子查询：

方案1：LAG函数结合条件处理

先标记符合条件的记录，再利用LAG()获取前序符合条件的日期，最后处理同一天的特殊规则：

WITH filtered_events AS (
    SELECT 
        id,
        date,
        condition,
        CASE WHEN condition = 'X' THEN date END AS valid_date
    FROM your_table
),
prev_valid_events AS (
    SELECT 
        id,
        date,
        condition,
        LAG(valid_date) OVER (PARTITION BY id ORDER BY date) AS raw_last_date
    FROM filtered_events
)
SELECT 
    id,
    date,
    condition,
    -- 处理同一天逻辑：若当前日期与前序日期相同，则取前一天
    CASE 
        WHEN raw_last_date = date THEN DATEADD(day, -1, raw_last_date)
        ELSE raw_last_date
    END AS last_date,
    -- 计算日期差值（按需保留）
    DATEDIFF(day, 
        CASE 
            WHEN raw_last_date = date THEN DATEADD(day, -1, raw_last_date)
            ELSE raw_last_date
        END, 
        date
    ) AS date_diff
FROM prev_valid_events;

方案2：LAST_VALUE函数动态获取最近前序日期

利用LAST_VALUE()结合IGNORE NULLS参数，直接在单查询中获取截止到当前行前一行的最近符合条件日期，再处理同一天规则：

SELECT 
    id,
    date,
    condition,
    -- 获取最近的前序符合条件X的日期
    LAST_VALUE(CASE WHEN condition = 'X' THEN date END IGNORE NULLS) 
        OVER (PARTITION BY id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS raw_last_date,
    -- 处理同一天的特殊规则
    CASE 
        WHEN raw_last_date = date THEN DATEADD(day, -1, raw_last_date)
        ELSE raw_last_date
    END AS last_date,
    -- 计算日期差值（按需保留）
    DATEDIFF(day,
        CASE 
            WHEN raw_last_date = date THEN DATEADD(day, -1, raw_last_date)
            ELSE raw_last_date
        END,
        date
    ) AS date_diff
FROM your_table;

性能说明 #

两种方案均采用Snowflake批量优化的窗口函数逻辑，避免了关联子查询带来的重复扫描和笛卡尔积风险，在大数据量场景下性能提升显著。

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