Compare value of current row to average of all previous rows-CodePudding

I'm looking to compare the value of each date to the average value of all previous dates and calculate the percent change. For example, in the source table below, I would want to compare the value of 100 from December 2022 to the average of November, October, and September ((75 60 75)/3) to bring back the 0.43 change.

Source Table

Date	Value
December 2022	100
November 2022	75
October 2022	60
September 2022	75

Desired Output

Date	Value	Comparison
December 2022	100	0.43
November 2022	75	0.11
October 2022	60	-0.20
September 2022	75	-

CodePudding user response：

You need a windowed AVG with an OVER clause using the appropriate range of rows (ORDER BY [Date] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING):

Test data:

SELECT *
INTO Data
FROM (VALUES
   (20221201, 100),
   (20221101, 75),
   (20221001, 60),
   (20220901, 75)
) v ([Date], [Value])

Statement:

SELECT [Date], [Value], ([Value] - [Average]) * 1.00 / [Average] AS [Comparison]
FROM (  
   SELECT
      *, 
      [Average] = AVG([Value]) OVER (ORDER BY [Date] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
   FROM Data
) t  
ORDER BY [Date] DESC

Result (without rounding):

Date	Value	Comparison
20221201	100	0.4285714285714
20221101	75	0.1194029850746
20221001	60	-0.2000000000000
20220901	75	null

CodePudding user response：

drop table #t
select *
into #t
from 
(
    VALUES  (1,N'December 2022', 100.0)
    ,   (2,N'November 2022', 75.0)
    ,   (3,N'October 2022', 60.0)
    ,   (4,N'September 2022', 75.0)
) t (sort, col1,col2)


select col2, (col2 - AVG(col2) OVER(ORDER BY sort DESC ROWS between UNBOUNDED PRECEDING and 1 PRECEDING)) / AVG(col2) OVER(ORDER BY sort DESC ROWS between UNBOUNDED PRECEDING and 1 PRECEDING)
,   AVG(col2) OVER(ORDER BY sort DESC)
from #t
order by sort

Something like this. watch out for 0 values though