T-SQL 计算移动平均值

Question

我正在使用SQL Server 2008 R2，尝试计算移动平均值。对于我的视图中的每个记录，我想收集前250个记录的值，然后计算此选择的平均值。

我的视图列如下：

TransactionID | TimeStamp           | Value | MovAvg
----------------------------------------------------
            1 | 01.09.2014 10:00:12 |     5 |    
            2 | 01.09.2014 10:05:34 |     3 | 
...
          300 | 03.09.2014 09:00:23 |     4 | 

TransactionID是唯一的。对于每个TransactionID，我想计算前250条记录中列值的平均值。因此，对于TransactionID 300，从前250行（按TransactionID降序排序）收集所有值，然后在MovAvg列中写入这些值的平均值结果。我希望在记录范围内收集数据。

- RunW

---

请查阅 PARTITION BY 和 ROW_NUMBER。 - Mihai

谢谢。你有没有关于如何做到这一点的建议？ - RunW

@RunW是否存在相同的交易ID但不同的值，除了交易ID之外还有时间戳列或标识列吗？ - radar

@RunW，你能提供样本数据吗？如果交易ID是唯一的，那么如何有250个相同ID的先前记录呢？ - radar

如果您编辑问题并包含示例数据，则可以对其进行更多格式设置。此外，并非每个人都会阅读评论，因此在阅读问题并决定是否要回答时，这将有助于其他人。 - Adam Porad

Answer 1

与后续版本相比，SQL 2008 中的窗口函数非常有限，如果我没记错的话，您只能进行分区操作，无法使用任何行/范围框架限制，但我认为这可能是您想要的：

;WITH cte (rn, transactionid, value) AS (
    SELECT 
       rn = ROW_NUMBER() OVER (ORDER BY transactionid),
       transactionid,
       value
    FROM your_table
)

SELECT 
    transactionid, 
    value, 
    movagv = (
        SELECT AVG(value) 
        FROM cte AS inner_ref
        -- average is calculated for 250 previous to current row inclusive
        -- I might have set the limit one row to large, maybe it should be 249
        WHERE inner_ref.rn BETWEEN outer_ref.rn-250 AND outer_ref.rn
        ) 
FROM cte AS outer_ref

请注意，它对每一行应用相关子查询，性能可能不是很好。

在较新的版本中，您可以使用窗口帧函数并执行以下操作：

SELECT 
    transactionid, 
    value,
    -- avg over the 250 rows counting from the previous row
    AVG(value) OVER (ORDER BY transactionid  
                     ROWS BETWEEN 251 PRECEDING AND 1 PRECEDING),
    -- or 250 rows counting from current
    AVG(value) OVER (ORDER BY transactionid  
                     ROWS BETWEEN 250 PRECEDING AND CURRENT ROW)
FROM your_table

- jpw

---

非常感谢。 这确实是正确的方法，但正如您所说，性能非常差。 感谢您的帮助。 - RunW

在SQL 2008中，有没有更有效的方法来完成这个任务，而不是在每一行上运行相关子查询？我一直在努力想出一个解决方案，以降低我的执行时间，但一直没有成功。 - mitchimus

@mitchimus 可能有，但我不知道 - 我没有仔细考虑过，因为后来的服务器版本有更好的选择。 - jpw

Answer 2

使用通用表达式（CTE）来为每个交易包括rownum，然后在行号上对CTE进行自我连接，以便获取先前的值来计算平均值。

CREATE TABLE MyTable (TransactionId INT, Value INT)

;with Data as
(
  SELECT TransactionId, 
         Value, 
         ROW_NUMBER() OVER (ORDER BY TransactionId ASC) as rownum
  FROM MyTable
)
SELECT d.TransactionId , Avg(h.Value) as MovingAverage
FROM Data d
JOIN Data h on h.rownum between d.rownum-250 and d.rownum-1
GROUP BY d.TransactionId 

- Adam Porad