在Redshift中实现忽略Null值的lag函数的变通方案

针对你需要在Redshift里实现忽略Null值的lag函数效果的需求，我这里有个非常实用的变通方案，刚好能适配你给出的示例数据表场景。核心思路是通过分组标记+窗口函数的组合，让每个Null值自动继承最近的非Null block_id 值。

完整实现SQL #

WITH grouped_data AS (
    SELECT 
        EmpID,
        Type,
        timestamp,
        block_id,
        -- 给每个非Null的block_id生成分组标识，遇到非Null值就递增分组号
        SUM(CASE WHEN block_id IS NOT NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY EmpID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM your_table_name
)
SELECT 
    EmpID,
    Type,
    timestamp,
    block_id,
    -- 在每个分组内取第一个非Null的block_id，即最近的非Null基准值
    FIRST_VALUE(block_id) OVER (PARTITION BY EmpID, group_id ORDER BY timestamp) AS last_non_null_block_id
FROM grouped_data
ORDER BY EmpID, timestamp;

方案分步解释 #

我们分两步实现“忽略Null的lag”效果：

1. 分组标记（grouped_data CTE）：

   • 用SUM() OVER()窗口函数，按EmpID分区、timestamp排序，每遇到一个非Null的block_id就给分组号加1。这样所有位于两个非Null block_id之间的Null行，都会被分到同一个组里，归属最近的那个非Null block_id。
   • 比如你示例里的前7行（从第一个'R'到第三个'D'）会被分到group_id=1，第二个'R'开始的行则会被分到group_id=2，以此类推。

2. 获取最近非Null值：

   • 用FIRST_VALUE()窗口函数，按EmpID和group_id分区，取每个分组里的第一个block_id（也就是那个非Null的基准值），这样组内所有的Null行都会自动继承这个值，相当于自动往前找最近的非Null值填充，完美替代忽略Null的lag函数效果。

预期结果示例 #

针对你给出的数据，运行后的关键列结果如下：

注意事项 #

• 如果某一行之前没有任何非Null的block_id（比如表的第一行就是Null），那么last_non_null_block_id会保持Null，这符合逻辑——因为没有可继承的前置非Null值。
• 这个方案依赖timestamp的排序准确性，要确保timestamp列能正确反映数据的先后顺序。

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