SQL实现滑动14天平均值统计的最优方案咨询

不推荐使用循环实现，SQL是面向集合的查询语言，循环逐行操作的性能远低于集合运算，而且容易出现边界判断错误。以下是适配你需求的两种实现方案，你使用的DATEADD、GETDATE属于SQL Server语法，方案默认适配SQL Server环境：

方案1：CTE递归生成窗口+批量计算（推荐） #

这个方案的逻辑和你熟悉的Python列表生成思路类似：先批量生成所有合法的14天不重叠窗口，再一次性关联原表计算所有窗口的平均值，直接写入新表，不需要手动逐次追加数据。

WITH DateBounds AS (
    -- 先获取表中数据的时间边界，避免用GETDATE()生成超出数据范围的空窗口
    SELECT MIN(RecordDate) AS min_date, MAX(RecordDate) AS max_date
    FROM ResultsData
),
WindowSeries AS (
    -- 递归生成所有不重叠的14天窗口，从最新数据日期往前倒推
    SELECT 
        DATEADD(DAY, -14, max_date) AS start_date,
        max_date AS end_date
    FROM DateBounds
    UNION ALL
    SELECT 
        DATEADD(DAY, -14, start_date) AS start_date,
        DATEADD(DAY, -14, end_date) AS end_date
    FROM WindowSeries
    CROSS JOIN DateBounds
    -- 终止条件：窗口起始日期不早于表中最早的数据日期
    WHERE DATEADD(DAY, -14, start_date) >= min_date
)
-- 计算每个窗口的平均值并写入新表NewAvgResult
SELECT 
    AVG(rd.NumberValue) AS average,
    ws.start_date,
    ws.end_date
INTO NewAvgResult
FROM WindowSeries ws
LEFT JOIN ResultsData rd
    ON rd.RecordDate BETWEEN ws.start_date AND ws.end_date
GROUP BY ws.start_date, ws.end_date
ORDER BY ws.end_date DESC

方案优势 #

• 性能远高于循环实现，数据量越大优势越明显
• 自动处理边界，不会出现窗口超出数据范围的问题
• 一次查询完成所有计算和新表创建，不需要额外的插入操作

方案2：循环实现（仅小数据量场景使用） #

如果你的数据库版本不支持CTE递归，可以用循环实现，注意不要用GETDATE()作为窗口基准，用表中实际的最大数据日期避免生成空窗口：

-- 先创建结果存储表
CREATE TABLE NewAvgResult (
    average FLOAT,
    start_date DATE,
    end_date DATE
)
DECLARE @min_date DATE, @current_start DATE, @current_end DATE
-- 初始化时间边界
SELECT 
    @min_date = MIN(RecordDate),
    @current_end = MAX(RecordDate)
FROM ResultsData
SET @current_start = DATEADD(DAY, -14, @current_end)

WHILE @current_start >= @min_date
BEGIN
    -- 插入当前窗口的计算结果
    INSERT INTO NewAvgResult(average, start_date, end_date)
    SELECT 
        AVG(NumberValue) AS average,
        @current_start AS start_date,
        @current_end AS end_date
    FROM ResultsData
    WHERE RecordDate BETWEEN @current_start AND @current_end
    -- 窗口往前移动14天
    SET @current_end = @current_start
    SET @current_start = DATEADD(DAY, -14, @current_end)
END

补充：如果需要逐行滑动的14天平均值 #

如果你的需求不是14天不重叠窗口，而是每天都生成往前14天的滑动平均值，可以直接用窗口函数实现，逻辑更简单：

SELECT 
    AVG(NumberValue) OVER (ORDER BY RecordDate ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS average,
    DATEADD(DAY, -13, RecordDate) AS start_date,
    RecordDate AS end_date
INTO NewSlidingAvgResult
FROM ResultsData
ORDER BY RecordDate DESC

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