Example: EXPAND ON for a Moving Average - 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 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;