如何高效获取时间序列数据的最新变更？求替代游标方案

现有时间序列数据表结构及数据如下：

Id       Year       Month         F1         F2         F3         F4
----------------------------------------------------------------------
1        2020         1           A          B          1          2
1        2020         2           AA      
1        2020         3                      BB         11
2        2020         1                                 3          4
2        2020         2           F          G 

期望得到各ID对应字段的最新变更结果：

Id       F1         F2         F3         F4
-----------------------------------------------
1        AA         BB         11         2
2        F          G          3          4

当前通过游标及4个变量实现该需求，请问是否存在更优的实现方式？

完全可以替代游标方案，用SQL的窗口函数+聚合逻辑实现，效率远高于逐行处理的游标，以下是两种实用思路：

思路1：按字段单独提取最新非空值 #

针对每个字段，先筛选出非空记录，按Id分组后以Year*12+Month作为时间排序依据，取每个Id下该字段的最新值，最后关联所有字段的结果。

以MySQL为例，SQL语句如下：

SELECT
    t.Id,
    MAX(CASE WHEN rn1 = 1 THEN F1 END) AS F1,
    MAX(CASE WHEN rn2 = 1 THEN F2 END) AS F2,
    MAX(CASE WHEN rn3 = 1 THEN F3 END) AS F3,
    MAX(CASE WHEN rn4 = 1 THEN F4 END) AS F4
FROM
    (SELECT DISTINCT Id FROM your_table) t
LEFT JOIN
    (SELECT Id, F1, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Year*12+Month DESC) rn1 FROM your_table WHERE F1 IS NOT NULL AND F1 != '') f1 ON t.Id = f1.Id
LEFT JOIN
    (SELECT Id, F2, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Year*12+Month DESC) rn2 FROM your_table WHERE F2 IS NOT NULL AND F2 != '') f2 ON t.Id = f2.Id
LEFT JOIN
    (SELECT Id, F3, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Year*12+Month DESC) rn3 FROM your_table WHERE F3 IS NOT NULL AND F3 != '') f3 ON t.Id = f3.Id
LEFT JOIN
    (SELECT Id, F4, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Year*12+Month DESC) rn4 FROM your_table WHERE F4 IS NOT NULL AND F4 != '') f4 ON t.Id = f4.Id
GROUP BY t.Id;

思路2：用LAST_VALUE窗口函数（适配支持的数据库） #

如果使用PostgreSQL、SQL Server这类支持LAST_VALUE()的数据库，可直接按Id分组，按时间排序后取每个字段的最后非空值，逻辑更简洁：

以SQL Server为例，SQL语句如下：

WITH ranked_data AS (
    SELECT
        Id,
        Year*12+Month AS time_order,
        LAST_VALUE(F1) OVER (PARTITION BY Id ORDER BY time_order ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_F1,
        LAST_VALUE(F2) OVER (PARTITION BY Id ORDER BY time_order ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_F2,
        LAST_VALUE(F3) OVER (PARTITION BY Id ORDER BY time_order ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_F3,
        LAST_VALUE(F4) OVER (PARTITION BY Id ORDER BY time_order ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_F4
    FROM your_table
)
SELECT DISTINCT
    Id,
    latest_F1 AS F1,
    latest_F2 AS F2,
    latest_F3 AS F3,
    latest_F4 AS F4
FROM ranked_data;

注：PostgreSQL支持IGNORE NULLS参数，可直接写成LAST_VALUE(F1) IGNORE NULLS OVER (...)，自动跳过空值取最新有效记录。

方案优势 #

• 性能更优：集合操作是数据库优化后的批量处理，远快于游标逐行遍历的方式；
• 代码易维护：无需复杂的游标循环逻辑，新增字段只需扩展对应处理逻辑；
• 稳定性强：减少游标带来的锁表、内存占用等潜在问题。

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