按TIMESTMP排序，将记录与前一条非空记录的MEASURE值求和

看起来你需要处理时间序列数据里的空值填充并计算求和，这在SQL场景里挺常见的，我来给你详细拆解解决方案：

先明确你的示例数据和需求 #

示例数据表

核心需求

按TIMESTMP排序后，每条记录的MEASURE要和**最近的前一条非空MEASURE**做求和运算；如果当前MEASURE是空值，你可以根据需求决定求和结果（比如返回前一条非空值，或者返回空）。

针对不同数据库的解决方案 #

我会分两种情况给你写代码，覆盖主流数据库：

1. 支持IGNORE NULLS的数据库（PostgreSQL、Oracle等）

这类数据库的窗口函数直接支持跳过空值，用LAST_VALUE就能轻松拿到最近的前一条非空值：

SELECT 
    C1, C2, C3, TIMESTMP, MEASURE,
    -- 拿到最近的前一条非空MEASURE
    LAST_VALUE(MEASURE) OVER (
        PARTITION BY C1, C2, C3  -- 如果需要按C1/C2/C3分组处理就保留，不需要就删掉
        ORDER BY TIMESTMP
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        IGNORE NULLS
    ) AS prev_non_null_measure,
    -- 计算求和结果：当前MEASURE非空就相加，否则取前一条非空值
    CASE 
        WHEN MEASURE IS NOT NULL THEN MEASURE + LAST_VALUE(MEASURE) OVER (
            PARTITION BY C1, C2, C3
            ORDER BY TIMESTMP
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            IGNORE NULLS
        )
        ELSE LAST_VALUE(MEASURE) OVER (
            PARTITION BY C1, C2, C3
            ORDER BY TIMESTMP
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            IGNORE NULLS
        )
    END AS sum_result
FROM your_table_name
ORDER BY TIMESTMP;

2. 不支持IGNORE NULLS的数据库（MySQL 8.0+）

MySQL 8.0+虽然有窗口函数，但不支持IGNORE NULLS，我们可以先给数据分组——每当遇到非空的MEASURE就新建一个组，再用MAX函数来模拟填充：

SELECT 
    C1, C2, C3, TIMESTMP, MEASURE,
    -- 模拟拿到最近的前一条非空MEASURE
    MAX(MEASURE) OVER (
        PARTITION BY C1, C2, C3, grp
        ORDER BY TIMESTMP
    ) AS prev_non_null_measure,
    -- 计算求和结果
    CASE 
        WHEN MEASURE IS NOT NULL THEN MEASURE + MAX(MEASURE) OVER (
            PARTITION BY C1, C2, C3, grp
            ORDER BY TIMESTMP
        )
        ELSE MAX(MEASURE) OVER (
            PARTITION BY C1, C2, C3, grp
            ORDER BY TIMESTMP
        )
    END AS sum_result
FROM (
    -- 先给数据分组：每遇到非空MEASURE就加1，形成连续的组
    SELECT 
        *,
        SUM(CASE WHEN MEASURE IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY C1, C2, C3
            ORDER BY TIMESTMP
        ) AS grp
    FROM your_table_name
) t
ORDER BY TIMESTMP;

额外说明 #

• 如果不需要按C1/C2/C3分组处理（比如全局所有数据按时间戳排序），直接删掉所有PARTITION BY C1, C2, C3的部分就行
• 要是你希望当前MEASURE为空时求和结果返回NULL，只需要把CASE语句里的ELSE部分改成NULL就可以了

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