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

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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,
            stock_id
     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