16.20 - Using Ordered Analytical Functions - 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)

Example: Using RANK and AVG

Consider the result of the following SELECT statement using the following ordered analytical functions, RANK and AVG.

   SELECT item, smonth, sales, 
   RANK() OVER (PARTITION BY item ORDER BY sales DESC), 
   AVG(sales) OVER (PARTITION BY item 
                    ORDER BY smonth 
                    ROWS 3 PRECEDING)
   FROM sales_tbl
   ORDER BY item, smonth;

The results table might look like the following.

Item SMonth Sales Rank(Sales) Moving Avg(Sales)
A 1996-01 110 13 110
A 1996-02 130 10 120
A 1996-03 170 6 137
A 1996-04 210 3 155
A 1996-05 270 1 195
A 1996-06 250 2 225
A 1996-07 190 4 230
A 1996-08 180 5 222
A 1996-09 160 7 195
A 1996-10 140 9 168
A 1996-11 150 8 158
A 1996-12 120 11 142
A 1997-01 120 11 132
B 1996-02 30 5 30
... ... ... ... ...