Example: EXPAND ON and Span Grouping - Analytics Database - Teradata Vantage

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.

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.