The average value of recursive decay on the Sql 2012 server

Question

I need to calculate the decomposing average (cumulative displacement?) Of a set of values. The last value in the series is 50% of the weight, with the decomposed average of all previous series, like the other weight of 50%, recursively.

I came up with a CTE query that gives the correct results, but depends on the serial line number. I am wondering if there is a better way to do this in SQL 2012, perhaps with new window functions for Over () or something like that?

In live data, rows are ordered by time. I can use the SQL view and ROW_NUMBER () to generate the required Row field for my CTE approach, but if there is a more efficient way to do this, I would like to keep it as efficient as possible.

I have an example table with two columns: Row int and Value Float. I have 6 sample data values ​​1,2,3,4,4,4. The correct result should be 3.78125.

My decision:

;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

This correctly produces 3.78125 with the test data. My question is: is there a more efficient and / or easier way to do this in SQL 2012, or is this the only way to do this? Thank.

Answer 1

A possible alternative could be

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

Or for an updated question using 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

both of the above perform a single pass through the data and can potentially use an index on Row INCLUDE(Value)to avoid sorting.