The following example returns by start date the salary, moving average (ma), and first and last salary in the moving average group.
The functions are going to return the first/last value in the window. In the example, the first and last rows fall within the window. If the window were between 3 preceding and 2 preceding rows, you would see NULL for first value in the 1st 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;
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 | 2334.78 | 4441.780 | 6661.78 | 4322.78 |
30-JUL-87 | 4322.980 | 4688.980 | 6544.78 | 7897.78 |
31-DEC-90 | 7897.78 | 3626.936 | 2344.78 | 1234.56 |
25-JUL-96 | 1234.56 | 3404.536 | 2334.78 | 1232.78 |
17-SEP-96 | 1232.78 | 3671.975 | 4322.78 | 1232.78 |