The following example returns by start date the salary, moving average (ma), and first and last salary in the moving average group.
The functions return the first and last values in the window. In the example, the first and last rows fall within the window. If the window is between 3 preceding and 2 preceding rows, you see NULL for first value in the first two rows.
SELECT start_date, salary,
AVG(salary) OVER(ORDER BY start_date
ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) ma,
FIRST_VALUE(salary) OVER(ORDER BY start_date
ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) first,
LAST_VALUE(salary) OVER(ORDER BY start_date
ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) last
FROM employee
ORDER BY start_date;
Result:
start_date salary ma first last ---------- -------- -------- ------- -------- 21-MAR-76 6661.78 6603.280 6661.78 6544.78 12-DEC-78 6544.78 5183.780 6661.78 2344.78 24-OCT-82 2344.78 4471.530 6661.78 2344.78 15-JAN-84 2344.78 4441.780 6661.78 4322.78 30-JUL-87 4322.98 4688.980 6544.78 7897.78 31-DEC-90 7897.78 3626.936 2344.78 1234.56 25-JUL-96 1234.56 3404.536 2344.78 1232.78 17-SEP-96 1232.78 3671.975 4322.78 1232.78