Window Aggregate Functions - Teradata Vantage

An aggregate function on which a window specification is applied is called a window aggregate function. Without a window specification, aggregate functions return one value for all qualified rows examined. Window aggregate functions return a new value for each of the qualifying rows participating in the query.

Thus, the following SELECT statement, which includes the aggregate AVG, returns one value only: the average of sales.

   SELECT AVG(sale) 
   FROM monthly_sales;

   Average(sale)
   -------------
            1368

The AVG window function retains each qualifying row.

The following SELECT statement may return the following results:

   SELECT territory, smonth, sales,
   AVG(sales) OVER (PARTITION BY territory 
                    ORDER BY smonth ROWS 2 PRECEDING)
   FROM sales_history;

   territory  smonth   sales  Moving Avg(sales)
   ---------  -------  -----  -----------------
   East        199810     10                 10
   East        199811      4                  7
   East        199812     10                  8
   East        199901      7                  7
   East        199902     10                  9
   West        199810      8                  8
   West        199811     12                 10
   West        199812      7                  9
   West        199901     11                 10
   West        199902      6                  8