17.05 - Example: EXPAND ON and Span Grouping - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
January 2021
English (United States)
Last Update

This example shows the use of the EXPAND ON clause with grouping on a span of entries from the select list.

First create the stock table.

     CREATE SET TABLE stock (
       stock_id        INTEGER,
       stock_quantity  INTEGER,
       begin_end_date  PERIOD(DATE))
     PRIMARY INDEX (stockid);

The stock table contains the following rows.

stock_id stock_quantity begin_end_date
100 200 2005-10-10,2005-11-15
101  20 2005-06-01,2005-08-31

This example shows how you can compute a weighted average for the stock_quantity column on a monthly basis.

Assume that udf_agspan is an aggregate UDF that adds the stock quantity for given month of a year and then divides the sum by the number of days in that month. This provides a different result when compared to the AVG function when the row is not spanning the whole month.

     SELECT udf_agspan(stock_quantity,
            EXTRACT(YEAR FROM BEGIN(expdcol)),
            EXTRACT(MONTH FROM BEGIN(expdcol)))
            (FORMAT ‘-----9.999’) AS wavg,
            EXTRACT(YEAR FROM BEGIN(expdcol) AS yr,
            EXTRACT(MONTH FROM BEGIN(expdcol) AS mn,
     FROM (SELECT stock.*, expdcol
           FROM stock
           EXPAND ON begin_end_date AS expdcol BY INTERVAL '1'DAY) AS dt
     GROUP BY 2,3,4;

This statement returns the following rows.

wavg yr mn stock_id
141.935 2005 10 100
 93.333 2005 11 100
 20.000 2005 06 101
 20.000 2005 07 101
 19.355 2005 08 101