16.20 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1145-162K
Language
English (United States)

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