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

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.

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.

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;