17.10 - 例: 移動平均に対するEXPAND ON - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ操作言語

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1146-171K-JPN
Language
日本語 (日本)

この例では、時系列データを平滑化する一般的な方法である移動平均を、price列のデータに対して作成する方法を示します。

まず、stkテーブルを作成します。

     CREATE SET TABLE stk (
       stock_id  INTEGER,
       price     FLOAT,
       validity  PERIOD(TIMESTAMP))
     PRIMARY INDEX (stock_id);

stkテーブルには、次の30行のセットが含まれています。

stock_id price validity
1000 10.00 2006-01-01 09:00:00 , 2006-01-01 12:00:00
1000 11.00 2006-01-01 12:00:00 , 2006-01-01 15:00:00
1000 9.00 2006-01-01 15:00:00 , 2006-01-01 18:00:00
1000 12.00 2006-01-02 09:00:00 , 2006-01-02 12:00:00
1000 13.00 2006-01-02 12:00:00 , 2006-01-02 15:00:00
1000 14.00 2006-01-02 15:00:00 , 2006-01-02 18:00:00
1000 8.00 2006-01-03 09:00:00 , 2006-01-03 12:00:00
1000 5.00 2006-01-03 12:00:00 , 2006-01-03 15:00:00
1000 15.00 2006-01-03 15:00:00 , 2006-01-03 18:00:00
1000 19.00 2006-01-04 09:00:00 , 2006-01-04 12:00:00
1000 16.00 2006-01-04 12:00:00 , 2006-01-04 15:00:00
1000 16.00 2006-01-04 15:00:00 , 2006-01-04 18:00:00
1000 16.00 2006-01-05 09:00:00 , 2006-01-05 12:00:00
1000 16.00 2006-01-05 12:00:00 , 2006-01-01 15:00:00
1000 16.00 2006-01-05 15:00:00 , 2006-01-05 18:00:00
1001 20.00 2006-01-01 09:00:00 , 2006-01-01 12:00:00
1001 21.00 2006-01-01 12:00:00 , 2006-01-01 15:00:00
1001 19.00 2006-01-01 15:00:00 , 2006-01-01 18:00:00
1001 22.00 2006-01-02 09:00:00 , 2006-01-02 12:00:00
1001 23.00 2006-01-02 12:00:00 , 2006-01-02 15:00:00
1001 24.00 2006-01-02 15:00:00 , 2006-01-02 18:00:00
1001 18.00 2006-01-03 09:00:00 , 2006-01-03 12:00:00
1001 15.00 2006-01-03 12:00:00 , 2006-01-03 15:00:00
1001 25.00 2006-01-03 15:00:00 , 2006-01-03 18:00:00
1001 29.00 2006-01-04 09:00:00 , 2006-01-04 12:00:00
1001 26.00 2006-01-04 12:00:00 , 2006-01-04 15:00:00
1001 26.00 2006-01-04 15:00:00 , 2006-01-04 18:00:00
1001 26.00 2006-01-05 09:00:00 , 2006-01-05 12:00:00
1001 24.00 2006-01-05 12:00:00 , 2006-01-01 15:00:00
1001 24.00 2006-01-05 15:00:00 , 2006-01-05 18:00:00

この例では、3日間の在庫の移動平均を返します。

     SELECT stock_id, CAST (p AS DATE), AVG(price)
            OVER (PARTITION BY stock_id
                  ORDER BY p ROWS
                  2 PRECEDING)
     FROM (SELECT stock_id, price, BEGIN(p)
           FROM stk
           EXPAND ON validity AS p
                     BY ANCHOR DAY AT TIME ‘17:59:59’
                     FOR PERIOD(TIMESTAMP '2006-01-01 17:59:59',
                                TIMESTAMP '2006-01-05 18:00:00')) AS dt;

この文は、priceに関する移動平均を持つ、以下の10行を返します。

stock_id CAST(p AS DATE) AVG(price)
1000 2006-01-01 9.00000000000000E 000
1000 2006-01-02 1.15000000000000E 001
1000 2006-01-03 1.26666666666667E 001
1000 2006-01-04 1.50000000000000E 001
1000 2006-01-05 1.56666666666667E 001
1001 2006-01-01 1.90000000000000E 001
1001 2006-01-02 2.15000000000000E 001
1001 2006-01-03 2.26666666666667E 001
1001 2006-01-04 2.50000000000000E 001
1001 2006-01-05 2.50000000000000E 001

以下のSELECT文のように、calendarテーブルと結合することにより、EXPAND ON句を使用せずに同じ結果を出すことができます。

     SELECT stock_id, CAST(CAST(p AS TIMESTAMP) AS DATE),
            AVG(price) OVER (PARTITION BY stock_id
                             ORDER BY p ROWS
                             2 PRECEDING)
     FROM stk, (SELECT (calendar_date (FORMAT 'yyyy-mm-dd')) || ' ' ||
                FROM sys_callendar.calendar
                WHERE calendar_date BETWEEN DATE '2006-01-01'
                                    AND     DATE '2006-01-06') AS dt(p)
     WHERE BEGIN(validity) <= p
     AND   p < END(validity)) AS expnd;