SQL使用LAG窗口函数时如何跳过Status为6的特定行取值

核心逻辑 #

基础LAG()函数只能按固定行偏移取值，无法自动跳过Status=6的非活跃记录，因此无法直接满足需求。要实现的规则是：所有行（包括Status=6的行）的PrevED值，都取排序规则下、当前行之前最近一条Status≠6的记录的EndDate，不存在上一条有效记录时返回默认值01-Jan-1900。

最优实现方案（支持IGNORE NULLS的数据库：Oracle、PostgreSQL、BigQuery、SQL Server 2022+等） #

用带IGNORE NULLS的LAST_VALUE()窗口函数即可实现，仅需一次表扫描，性能最优，写法最简洁：

SELECT
    SPID,
    ID,
    Status,
    StartDate,
    EndDate,
    COALESCE(
        LAST_VALUE(CASE WHEN Status <> 6 THEN EndDate END) IGNORE NULLS OVER (
            PARTITION BY SPID -- 按SPID分组计算，若全局排序不需要分组可删除该行
            ORDER BY StartDate, ID -- 排序规则和原有业务逻辑保持一致即可
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING -- 窗口范围限定为当前行之前的所有数据，不取当前行自身
        ),
        '1900-01-01' -- 无匹配上一条有效记录时的默认值
    ) AS PrevED
FROM 你的源表名

关键说明 #

• 通过CASE语句把Status=6的行的待取值置为NULL，这类行不会作为有效EndDate被返回
• IGNORE NULLS参数让窗口函数自动跳过NULL值，直接取窗口内最近的非NULL值，自动实现跳过非活跃行的效果
• 窗口范围子句严格限定只取当前行之前的数据，和原生LAG()的取值范围完全一致
• 用COALESCE做兜底，第一条有效记录之前没有数据时返回指定默认日期

兼容低版本数据库的通用写法（不支持IGNORE NULLS场景，如SQL Server 2019及更早版本） #

通过给有效记录打分组标记后关联取值，兼容所有支持窗口函数的数据库：

WITH ValidGroupMark AS (
    SELECT
        *,
        COUNT(CASE WHEN Status <> 6 THEN 1 END) OVER (
            PARTITION BY SPID
            ORDER BY StartDate, ID
            ROWS UNBOUNDED PRECEDING
        ) AS GroupID
    FROM 你的源表名
)
SELECT
    t1.SPID,
    t1.ID,
    t1.Status,
    t1.StartDate,
    t1.EndDate,
    COALESCE(t2.EndDate, '1900-01-01') AS PrevED
FROM ValidGroupMark t1
LEFT JOIN (
    SELECT SPID, GroupID, EndDate
    FROM ValidGroupMark
    WHERE Status <> 6
) t2
ON t1.SPID = t2.SPID AND t1.GroupID - 1 = t2.GroupID

两种写法代入提供的样例数据，输出结果和预期完全匹配。

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