SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (value INT);
INSERT INTO test VALUES (1), (2), (3), (4), (5), (6);
6 rows affected

SELECT value,
       AVG(value) OVER (ORDER BY value ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) avg2
FROM test;


value
avg2


1
2

2
2

3
3

4
4

5
4

6
5


SELECT value,
       AVG(value) OVER (ORDER BY value ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) avg2
FROM test;


value
avg2


1
null

2
1

3
1

4
2

5
3

6
4