17.05 - Example: EXPAND ON for a Moving Average - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

This example shows how to create a moving average, which is a common method of smoothing time series data, on the data in the price column.

First create the stk table.

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

The stk table contains the following set of 30 rows.

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

This example returns a moving average of stock over a three day period.

     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;

This statement returns the following 10 rows with a moving average over price.

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

You can produce the same result without using an EXPAND ON clause by joining with a calendar table, as in the following SELECT statement.

     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;