我正在使用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列中写入这些值的平均值的结果.我正在寻找一系列记录中的数据.
解决方法
sql 2008中的窗口函数相对于以后的版本相当有限,如果我记得正确,则只能进行分区,并且不能使用任何行/范围框架限制,但我认为这可能是您想要的：
;WITH cte (rn,transactionid,value) AS (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY 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,-- 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
