Example

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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