我试图在SELECT语句中有一个基于同一SELECT语句中n个前一行的列的运行平均列.我需要的平均值是基于结果集中的前n行.
让我解释
Id Number Average 1 1 NULL 2 3 NULL 3 2 NULL 4 4 2 <----- Average of (1,3,2),Numbers from prevIoUs 3 rows 5 6 3 <----- Average of (3,2,4),Numbers from prevIoUs 3 rows . . . . . .
Average列的前3行为null,因为之前没有行. “平均值”列中的第4行显示前3行中“数字”列的平均值.
我需要一些帮助来尝试构建一个将执行此操作的sql Select语句.
解决方法
这应该这样做：
--Test Data
CREATE TABLE RowsToAverage
(
ID int NOT NULL,Number int NOT NULL
)
INSERT RowsToAverage(ID,Number)
SELECT 1,1
UNION ALL
SELECT 2,3
UNION ALL
SELECT 3,2
UNION ALL
SELECT 4,4
UNION ALL
SELECT 5,6
UNION ALL
SELECT 6,8
UNION ALL
SELECT 7,10
--The query
;WITH NumberedRows
AS
(
SELECT rta.*,row_number() OVER (ORDER BY rta.ID ASC) AS RowNumber
FROM RowsToAverage rta
)
SELECT nr.ID,nr.Number,CASE
WHEN nr.RowNumber <=3 THEN NULL
ELSE ( SELECT avg(Number)
FROM NumberedRows
WHERE RowNumber < nr.RowNumber
AND RowNumber >= nr.RowNumber - 3
)
END AS MovingAverage
FROM NumberedRows nr
