Example: EXPAND ON and DISTINCT - 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 how Vantage performs the DISTINCT operation after expansion occurs.

     CREATE SET TABLE products (
       product_id       INTEGER,
       product_price    DECIMAL(5,2),
       product_duration PERIOD(DATE))
     PRIMARY INDEX (product_id);

Assume that you have the following rows in products.

product_id

----------

product_price

-------------

product_duration

----------------

1000 100.00 2007-02-15, 2007-08-11
1001 99.99 2007-03-04, 2007-05-01
1001 101.10 2008-05-10, 2009-05-10
1001 1-4.10 2007-07-16, 2008-10-09

When you specify a DISTINCT operator in the query expression and the expanded column in the select list of your query, the database performs the DISTINCT operation after expansion and removes the duplicate rows from the expanded result (see the following example).

     SELECT DISTINCT product_id, pd
     FROM products
     EXPAND ON product_duration AS pd BY ANCHOR PERIOD MONTH_BEGIN;

This SELECT statement returns the following response set.

product_id pd
1000 2007-02-01, 2007-03-01
1000 2007-03-01, 2007-04-01
1000 2007-04-01, 2007-05-01
1000 2007-05-01, 2007-06-01
1000 2007-06-01, 2007-07-01
1000 2007-07-01, 2007-08-01
1000 2007-08-01, 2007-09-01
1001 2007-03-01, 2007-04-01
1001 2007-04-01, 2007-05-01
1001 2008-05-01, 2008-06-01
1001 2008-06-01, 2008-07-01
1001 2008-07-01, 2008-08-01
1001 2008-08-01, 2008-09-01
1001 2008-09-01, 2008-10-01
1001 2008-10-01, 2008-11-01
1001 2008-11-01, 2008-12-01
1001 2008-12-01, 2009-01-01
1001 2009-01-01, 2008-02-01
1001 2009-02-01, 2009-03-01
1001 2009-03-01, 2009-04-01
1001 2009-04-01, 2009-05-01
1001 2009-05-01, 2009-06-01
1001 2007-07-01, 2007-08-01
1001 2007-08-01, 2007-09-01
1001 2007-09-01, 2007-10-01
1001 2007-10-01, 2007-11-01
1001 2007-11-01, 2007-12-01
1001 2007-12-01, 2008-01-01
1001 2008-01-01, 2008-02-01
1001 2008-02-01, 2008-03-01
1001 2008-03-01, 2008-04-01
1001 2008-04-01, 2008-05-01