在SSMS中按jobId计算非连续时间下的60秒温度移动平均

针对非连续时间序列按jobId计算最近60秒移动平均的需求，核心是基于时间区间而非行数定义计算窗口，以下是几种适配不同数据库的实现方案：

方案1：利用支持数值RANGE的窗口函数（如SQL Server） #

如果表中已有timeOffsetSeconds（每个jobId的秒级偏移量），可直接基于该字段的数值范围定义窗口：

SELECT 
    [dataId],
    [jobId],
    [plantProductCodeId],
    [timeStamp],
    [Initial Timestamp],
    [timeOffsetSeconds],
    [tempData],
    CASE 
        WHEN [timeOffsetSeconds] > 60
        THEN AVG([tempData]) OVER (
            PARTITION BY [jobId] 
            ORDER BY [timeOffsetSeconds]
            RANGE BETWEEN 60 PRECEDING AND CURRENT ROW
        )
        ELSE NULL
    END AS moving_average
FROM your_table_name

说明：RANGE BETWEEN 60 PRECEDING AND CURRENT ROW会筛选同一jobId下，timeOffsetSeconds在当前值-60到当前值之间的所有行，自动忽略时间缺失的行，精准覆盖最近60秒的有效数据。

方案2：利用支持时间INTERVAL的窗口函数（如PostgreSQL、BigQuery） #

如果数据库支持直接基于时间戳的区间计算，可使用INTERVAL定义窗口：

SELECT 
    dataId,
    jobId,
    plantProductCodeId,
    timeStamp,
    "Initial Timestamp",
    timeOffsetSeconds,
    tempData,
    CASE 
        WHEN timeOffsetSeconds > 60
        THEN AVG(tempData) OVER (
            PARTITION BY jobId 
            ORDER BY timeStamp
            RANGE BETWEEN INTERVAL '60 seconds' PRECEDING AND CURRENT ROW
        )
        ELSE NULL
    END AS moving_average
FROM your_table_name

说明：此方案直接基于原始timeStamp计算，无需依赖timeOffsetSeconds，自动匹配当前时间戳往前60秒内的所有数据，适配非连续时间场景更直观。

方案3：兼容老版本数据库的关联子查询 #

如果数据库不支持RANGE类型的窗口函数，可通过关联子查询实现：

SELECT 
    t1.dataId,
    t1.jobId,
    t1.plantProductCodeId,
    t1.timeStamp,
    t1."Initial Timestamp",
    t1.timeOffsetSeconds,
    t1.tempData,
    CASE 
        WHEN t1.timeOffsetSeconds > 60
        THEN (SELECT AVG(t2.tempData) 
              FROM your_table_name t2
              WHERE t2.jobId = t1.jobId
                AND t2.timeOffsetSeconds > t1.timeOffsetSeconds - 60
                AND t2.timeOffsetSeconds <= t1.timeOffsetSeconds)
        ELSE NULL
    END AS moving_average
FROM your_table_name t1
ORDER BY t1.jobId, t1.timeOffsetSeconds

说明：子查询会针对每一行，筛选同一jobId下符合最近60秒时间范围的数据并计算平均值。建议给jobId和timeOffsetSeconds建立复合索引，提升查询性能。

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