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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
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.

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