如何用Windowing Function追踪最近出现的Special行

问题背景 #

现有测试数据：

需要生成结果，新增LastSeenSpecial列，记录当前行及之前最近的Special=1的Id；若之前没有Special=1的行，则返回NULL：

之前尝试MIN(Id) OVER (PARTITION BY Special ORDER BY Id)未得到预期结果，因为按Special分区会将0和1的行分开，无法追踪跨分区的最近Id，且前两行错误返回1而非NULL。

解决方案 #

可以使用带条件的窗口聚合函数，结合ORDER BY和范围窗口来实现：

SELECT 
    Id,
    Special,
    MAX(CASE WHEN Special = 1 THEN Id END) OVER (
        ORDER BY Id 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS LastSeenSpecial
FROM your_table_name;

原理说明 #

• CASE WHEN Special = 1 THEN Id END：仅在Special=1的行保留对应Id，其他行返回NULL
• MAX(...) OVER (ORDER BY Id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)：按Id顺序，对当前行及所有之前的行取最大值。由于只有Special=1的行有非NULL值，MAX会自动保留最近的（最大的）那个Id；如果之前没有Special=1的行，MAX结果就是NULL，完全符合需求。

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