SQL Server 2012中的递归衰减平均值 [英] Recursive Decaying Average in Sql Server 2012

问题描述

我需要计算衰减平均值(累积移动？)一组值的集合。序列中的最后一个值为50%权重，将所有先前序列的衰减平均值递归为另一个50%权重。

我提出了一个CTE查询，它可以产生正确的结果，但它依赖于一个连续的行号。我想知道在SQL 2012中有没有更好的方法来做到这一点，也许是使用over()的新窗口函数，或者类似的东西？

在实时数据中，行按时间排序。我可以使用一个SQL视图和row_number()来为我的CTE方法生成必要的行字段，但是如果有更有效的方法来实现这一点，我希望尽可能地保持高效。

我有一个包含两列的示例表：行int和值Float。我有6个样例数据值1、2、3、4、4。正确的结果应该是3.78125。

我的解决方案是：

;WITH items AS (
  SELECT TOP 1 
    Row, Value, Value AS Decayed
    FROM Sample Order By Row
  UNION ALL
  SELECT v.Row, v.Value, Decayed * .5 + v.Value *.5 AS Decayed
  FROM Sample v
  INNER JOIN items itms ON itms.Row = v.Row-1
  )
SELECT top 1 Decayed FROM items order by Row desc

这将正确地生成测试数据的3.78125。我的问题是：在SQL 2012中有没有更高效和/或更简单的方法来实现这一点，或者这是唯一的方法？谢谢。

推荐答案

一种可能的替代方案是

WITH T AS
(
SELECT      
 Value * POWER(5E-1, ROW_NUMBER() 
                     OVER (ORDER BY Row DESC)
               /* first row decays less so special cased */
              -IIF(LEAD(Value) OVER (ORDER BY Row DESC) IS NULL,1,0))
       as x
FROM Sample
)
SELECT SUM(x)
FROM T

SQL Fiddle

或使用60%/40%

WITH T AS
(
SELECT   IIF(LEAD(Value) OVER (ORDER BY Row DESC) IS NULL,  1,0.6)
         * Value 
         * POWER(4E-1, ROW_NUMBER() OVER (ORDER BY Row DESC) -1)
       as x
FROM Sample
)
SELECT SUM(x)
FROM T

SQL Fiddle

上述两种方法都对数据执行一次遍历，并且可能使用Row INCLUDE(Value)上的索引来避免排序。

这篇关于SQL Server 2012中的递归衰减平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！