T-SQL 计算移动平均线
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26618353/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL calculate moving average
提问by RunW
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.
我正在使用 SQL Server 2008 R2，试图计算移动平均值。对于我视图中的每条记录，我想收集之前 250 条记录的值，然后计算此选择的平均值。
My view columns are as follows:
我的观点列如下：
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 |
TransactionIDis 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.
TransactionID是独特的。对于每个TransactionID，我想计算前 250 条记录的列值的平均值。因此，对于 TransactionID 300，收集前 250 行的所有值（视图按 TransactionID 降序排序），然后在列 MovAvg 中写入这些值的平均值的结果。我希望收集一系列记录中的数据。
回答by jpw
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:
与更高版本相比，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
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
回答by Adam Porad
Use a Common Table Expression (CTE)to include the rownum for each transaction, then join the CTE against itself on the row number so you can get the previous values to calculate the average with.
使用通用表表达式 (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
