您需要一个窗口化的AVG，它带有一个OVER子句，并使用适当的行范围（ORDER BY [Date] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING）：
试验数据：

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

声明：

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

结果（未修约）：
| 日期|价值|比较|
| - ------|- ------|- ------|
| 小行星2022|一百|0.4285714285714美元|
| 小行星2022|七十五|0.1194029850746|
| 小行星2022|六十|-0.2000000000000|
| 小行星2022|七十五|* 无效 *|

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

类似于这样。不过要注意0值