Example: EXPAND ON For an Anchored Interval - 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-10-04
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantageā„¢

This example shows the use of an anchored interval for doing anchor period and anchor point expansions. For an anchor period expansion, the expanded period value must overlap the expanding period, while for an anchor point expansion, the begin value of the expanded period value must be contained in the expanding period, which is a more restrictive condition.

First create the sold_products table.

     CREATE SET TABLE sold_products, NO FALLBACK  (
       product_id       INTEGER,
       product_price    DECIMAL(10,2),
       product_duration PERIOD(DATE))
     PRIMARY INDEX (product_id);

The sold_products table contains the following rows.

product_id product_price product_duration
1000 100.00 2007-02-15, 2007-08-11
1001  99.99 2007-03-04, 2007-05-01

The following SELECT statement specifies an anchor period of MONTH_BEGIN. This is an anchor period expansion.

     SELECT product_id, product_price, product_duration, expd
     FROM sold_products
     EXPAND ON product_duration AS expd BY ANCHOR PERIOD MONTH_BEGIN;

The statement returns the following nine rows, with the original two rows highlighted in red:

product_id product_price product_duration expd
1000 100.00 2007-02-15,2007-08-11 2007-02-01,2007-03-01
1000 100.00 2007-02-15,2007-08-11 2007-03-01,2007-04-01
1000 100.00 2007-02-15,2007-08-11 2007-04-01,2007-05-01
1000 100.00 2007-02-15,2007-08-11 2007-05-01,2007-06-01
1000 100.00 2007-02-15,2007-08-11 2007-06-01,2007-07-01
1000 100.00 2007-02-15,2007-08-11 2007-07-01,2007-08-01
1000 100.00 2007-02-15,2007-08-11 2007-08-01,2007-09-01
1001  99.99 2007-03-04,2007-05-01 2007-03-01,2007-04-01
1001  99.99 2007-03-04,2007-05-01 2007-04-01,2007-05-01

For an anchor point expansion done on the same data, the shaded rows would not appear, as the following example shows.

The following table describes the difference between anchor point and anchor period expansions.

Expansion Type Description
Anchor period expansion Expanded period value must overlap the expanding period.
Anchor point expansion Begin value of the expanded period value must be contained within the expanding period.

Submit the following SELECT statement, which differs from the previous statement only in specifying the BEGIN bound function on product_duration instead of simply specifying the column name. This is an anchor point expansion done on the same data as the previous anchor period expansion.

     SELECT product_id, product_price, product_duration, BEGIN(expd)
     FROM sold_products
     EXPAND ON product_duration AS expd BY ANCHOR MONTH_BEGIN;

This statement returns seven rows, rather than nine, with the rows shaded in red from the previous example not appearing in the result set.

product_id product_price product_duration begin(expd)
1000 100.00 2007-02-15,2007-08-11 2007-03-01
1000 100.00 2007-02-15,2007-08-11 2007-04-01
1000 100.00 2007-02-15,2007-08-11 2007-05-01
1000 100.00 2007-02-15,2007-08-11 2007-06-01
1000 100.00 2007-02-15,2007-08-11 2007-07-01
1000 100.00 2007-02-15,2007-08-11 2007-08-01
1001  99.99 2007-03-04,2007-05-01 2007-04-01