如何用TSQL按指定秒数(@NUM)分组计算数据平均值？

原始需求 #

现有如下SQL查询语句，用于计算指定时间范围内的平均值：

DECLARE @NUM AS INT   -- 表示分组的秒数
DECLARE @DATA_START AS DATETIME = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
DECLARE @DATA_END AS DATETIME = GETDATE()

SELECT AVG(D.VALORE)
FROM AA_V_DATI_MISURAZIONE D
WHERE D.STARTDATE >= @DATA_START AND D.ENDDATE <= @DATA_END

需要修改查询，实现按每@NUM秒为一组，计算D.Valore的平均值。

用户自行编写的查询语句 #

SELECT 
    DATEADD(day, DATEDIFF(SECOND, @DATA_START, D.DataOra) / @NUM, @DATA_START) AS DATAORA,
    AVG(D.Valore) AS MEDIAVALORE
FROM 
    AA_V_DatiMisurazione D 
WHERE 
    D.STARTDATE >= @DATA_START 
    AND D.STARTDATE <= @DATA_END 
GROUP BY 
    DATEDIFF(SECOND, @DATA_START, D.DataOra) / @NUM
ORDER BY 
    DATAORA

问题修正与优化 #

用户编写的语句存在关键错误：DATEADD的第一个参数误用了day，按秒数分组应该用second，否则分组后的时间计算会完全偏离预期。

修正后的正确语句：

DECLARE @NUM AS INT   -- 分组的秒数
DECLARE @DATA_START AS DATETIME = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
DECLARE @DATA_END AS DATETIME = GETDATE()

SELECT 
    -- 按@NUM秒间隔计算分组起始时间
    DATEADD(SECOND, (DATEDIFF(SECOND, @DATA_START, D.DataOra) / @NUM) * @NUM, @DATA_START) AS DATAORA,
    AVG(D.Valore) AS MEDIAVALORE
FROM 
    AA_V_DatiMisurazione D 
WHERE 
    D.STARTDATE >= @DATA_START 
    AND D.STARTDATE <= @DATA_END 
GROUP BY 
    (DATEDIFF(SECOND, @DATA_START, D.DataOra) / @NUM)
ORDER BY 
    DATAORA

核心逻辑说明 #

1. 分组编号计算：通过DATEDIFF(SECOND, @DATA_START, D.DataOra) / @NUM的整数除法，将同一@NUM秒区间内的数据归为同一个分组编号。
2. 分组时间还原：用DATEADD(SECOND, 分组编号 * @NUM, @DATA_START)还原每组的起始时间，确保时间戳严格按@NUM秒的间隔对齐。
3. 范围过滤：保留原有的时间范围条件，确保仅处理指定区间内的数据。

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