如何在SQL中实现首尾循环引用的循环滚动平均平滑函数？

我清楚你想要的是循环式的滚动平均——每个分区的首行会引用该分区的末行数据，末行也会引用首行数据，相当于把每个分区的数据集首尾衔接成环形来计算平滑值。你原来用窗口函数实现的普通滚动平均没法直接处理这种环形循环场景，咱们换个思路来实现：

完整实现代码 #

CREATE TABLE #Input ( PartitionID int , TimeID int , Quantity float ); 
INSERT INTO #Input VALUES 
    ( 1, 1, 2 ), ( 1, 2, 4 ), ( 1, 3, 6 ), ( 1, 4, 16 ), 
    ( 2, 4, 6 ), ( 2, 5, 1 ), ( 2, 6, 9 ), ( 2, 7, 2 ); 

-- 查看原始数据
SELECT * FROM #Input;

-- 循环滚动平均的核心逻辑
WITH PartitionedData AS (
    -- 给每个分区的行按TimeID排序编号，同时统计分区总行数
    SELECT 
        PartitionID,
        TimeID,
        Quantity,
        ROW_NUMBER() OVER(PARTITION BY PartitionID ORDER BY TimeID) AS RowNum,
        COUNT(*) OVER(PARTITION BY PartitionID) AS TotalRows
    FROM #Input
),
ExtendedData AS (
    -- 手动匹配每行的前后行（处理首尾循环）
    SELECT 
        p.PartitionID,
        p.TimeID,
        p.Quantity,
        -- 首行的前一行是分区末行，其他行取上一行
        (SELECT Quantity FROM PartitionedData WHERE PartitionID = p.PartitionID AND RowNum = CASE WHEN p.RowNum = 1 THEN p.TotalRows ELSE p.RowNum - 1 END) AS PrevQty,
        -- 末行的后一行是分区首行，其他行取下一行
        (SELECT Quantity FROM PartitionedData WHERE PartitionID = p.PartitionID AND RowNum = CASE WHEN p.RowNum = p.TotalRows THEN 1 ELSE p.RowNum + 1 END) AS NextQty
    FROM PartitionedData p
)
-- 更新原始表的Quantity为循环滚动平均值
UPDATE i
SET Quantity = ROUND((ed.Quantity + ed.PrevQty + ed.NextQty) / 3.0, 2)
FROM #Input i
JOIN ExtendedData ed ON i.PartitionID = ed.PartitionID AND i.TimeID = ed.TimeID;

-- 查看处理后的结果
SELECT * FROM #Input;

代码逻辑说明 #

1. PartitionedData CTE：先给每个分区内的行按TimeID排序编号，同时统计每个分区的总行数——这一步是为了快速判断哪一行是分区的首行/末行。
2. ExtendedData CTE：通过子查询精准匹配每行的"前一行"和"后一行"：
   • 如果当前行是分区第一行，前一行直接取分区的最后一行；
   • 如果当前行是分区最后一行，后一行直接取分区的第一行；
   • 中间行则正常取相邻的上一行和下一行。
3. 更新数据：计算当前值、前一行值、后一行值的平均值，用ROUND保留两位小数，和你期望的输出格式完全对齐。

最终结果验证 #

运行代码后，你会得到和预期一致的输出：

PartitionID | TimeID | Quantity
------------|--------|---------
1           | 1      | 7.33
1           | 2      | 4.00
1           | 3      | 8.67
1           | 4      | 8.00
2           | 4      | 3.00
2           | 5      | 5.33
2           | 6      | 4.00
2           | 7      | 5.67

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