T-SQL计算移动平均线 [英] T-SQL calculate moving average

问题描述

我正在使用 SQL Server 2008 R2，试图计算移动平均线.对于我认为的每条记录，我想收集之前 250 条记录的值，然后计算此选择的平均值.

I am working with SQL Server 2008 R2, trying to calculate a moving average. For each record in my view, I would like to collect the values of the 250 previous records, and then calculate the average for this selection.

我的视图栏如下:

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 中写入这些值的平均值.我希望收集一系列记录中的数据.

TransactionID is unique. For each TransactionID, I would like to calculate the average for column value, over previous 250 records. So for TransactionID 300, collect all values from previous 250 rows (view is sorted descending by TransactionID) and then in column MovAvg write the result of the average of these values. I am looking to collect data within a range of records.

推荐答案

SQL 2008 中的窗口函数与以后的版本相比相当有限，如果我没记错的话你只能分区，你不能使用任何行/范围框架限制，但我认为这可能是你想要的:

The window functions in SQL 2008 are rather limited compared to later versions and if I remember correct you can only partition and you can't use any rows/range frame limit but I think this might be what you want:

;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

请注意，它将关联的子查询应用于每一行，性能可能不会很好.

Note that it applies a correlated sub-query to every row and performance might not be great.

在更高版本中，您可以使用窗口框架函数并执行以下操作:

With the later versions you could have used window frame functions and done something like this:

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

这篇关于T-SQL计算移动平均线的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！