例: EXPAND ONおよびDISTINCT - Teradata Database - Teradata Vantage NewSQL Engine - 例: EXPAND ONおよびDISTINCT

Teradata Vantage™ SQLデータ操作言語

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
2019年3月
Language
日本語
Last Update
2019-10-29
dita:mapPath
ja-JP/fbo1512081269404.ditamap
dita:ditavalPath
ja-JP/fbo1512081269404.ditaval
dita:id
B035-1146
Product Category
Software
Teradata Vantage

この例では、拡張が発生した後、Teradata DatabaseがDISTINCT操作を行なう方法を示します。

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

製品に次の行があると仮定します。

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

クエリー式内にDISTINCT演算子を指定し、クエリーの選択リスト内の拡張された列を指定すると、Teradata Databaseは、この例で示すように、拡張後にそのDISTINCT操作を実行し、拡張された結果から重複する行を削除します。

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

SELECT文は、次の応答セットを返します。

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