如何用SQL按分组与时间获取type为'X'的最后一个key2生成last_k2X列

需要从给定数据表中创建一列last_k2X，规则如下：

• 按时间ts排序后，展示type字段值为'X'的最后一个key2值
• 同一key1分区内，若某ts时间点存在多个type='X'的key2，则该时间点的所有行last_k2X均填充该key2值

输入数据表：

尝试过FIRST_VALUE()、LAG()等窗口函数但未得到正确结果，期望输出如下：

期望输出数据表：

通过两步窗口函数组合实现需求，先提取各时间点的X类型key2，再向前填充最近有效值。

SQL代码（兼容多数SQL方言，如Spark SQL、BigQuery等） #

WITH temp_data AS (
    SELECT 
        key1,
        key2,
        ts,
        type,
        -- 同一key1+ts分组内，提取type='X'的key2；自身是X则直接取
        CASE 
            WHEN type = 'X' THEN key2 
            ELSE MAX(CASE WHEN type = 'X' THEN key2 END) OVER (PARTITION BY key1, ts)
        END AS current_k2X
    FROM input_table
),
filled_data AS (
    SELECT 
        *,
        -- 按key1分区、ts排序，向前填充最近的非NULL current_k2X
        LAST_VALUE(current_k2X, IGNORE NULLS) OVER (
            PARTITION BY key1 
            ORDER BY ts 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS last_k2X
    FROM temp_data
)
SELECT key1, key2, ts, type, last_k2X
FROM filled_data
ORDER BY ts, key2;

逻辑说明 #

1. 临时表temp_data：

   • 对每个key1+ts的分组，用聚合窗口函数MAX()提取该时间点所有type='X'的key2值（若没有则为NULL）
   • 自身是X类型的行直接赋值key2，确保同时间点的行都能拿到正确的X对应值

2. 临时表filled_data：

   • 按key1分区、ts升序排列，使用LAST_VALUE(..., IGNORE NULLS)将最近的非NULLcurrent_k2X填充到当前及后续行
   • 窗口范围ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW保证只引用当前行之前的历史有效值

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