EXPAND ON Clause - Teradata Database

EXPAND ON Clause

Purpose  

Expands a column having a PERIOD data type, creating a regular time series of rows based on the period value in the input row.

The expansion of a PERIOD column produces one value‑equivalent row for each of the time granules in the epoch or timestamp representation of the specified time granule.

You can perform time series expansions only on PERIOD expressions.

Syntax

where:

ANSI Compliance

EXPAND ON is a Teradata extension to the ANSI SQL:2011 standard.

Because of this, Teradata Database reports a warning message if you use an EXPAND ON clause in a session in which the SQL Flagger is set. For information about the SQL Flagger, see SQL Fundamentals.

Note that the Teradata SQL EXPAND ON clause does not share any functionality with the EXPANDING clause of the ANSI SQL:2011 standard.

Time Series, Dense Representations, and Sparse Representations of Temporal Data

A time series is an ordered sequence of measurements of a variable that are arranged according to the time of their occurrence. Time series are typically measured at some constant frequency and their data points are generally, but not necessarily, spaced at uniform time intervals.

The characteristic properties of a time series include the following.

Typical business applications for time series analysis include the following.

The EXPAND ON clause enables several different forms of time series expansion on a PERIOD column value of an input row by producing a set of value‑equivalent rows, one for each granule in the specified time period. The number of granules is defined by the anchor name you specify for the clause.

You can expand sparse PERIOD representations of relational data into a dense representation of the same data. Data converted to a dense form can be more easily manipulated by complex analyses such as moving average calculations without having to write complex SQL requests to respond to business questions made against sparse relational data.

The available forms of time series expansion for the EXPAND ON clause are the following.

Rules and Restrictions for the EXPAND ON Clause

The rules and restrictions are:

You must specify an interval literal in the EXPAND ON clause to execute this form of expansion. This type of expansion is called an interval expansion.

An interval expansion might be useful for answering queries such as “compute the moving window average of inventory cost by week during the year 2010."

You must specify an anchored interval without specifying a PERIOD keyword to execute this form of expansion. Such expansion is called an anchor point expansion.

An anchor point expansion produces a specific date or time point in the expanded rows, where the date or time points are the anchor points present in the input period being expanded.

For example, BY ANCHOR MONTH_BEGIN returns multiple expanded rows, one for each beginning value of a month present in the input period. This form of expansion might be useful for answering queries such as “get the month end average inventory cost during the last quarter of the year 2010.”

You must specify an anchored interval with the PERIOD keyword to execute this form of expansion.

Such an expansion is called as anchor period expansion.

An anchor PERIOD expansion produces rows whose period beginning bounds are always aligned to specific DateTime values derived from the anchor names.

For example, BY ANCHOR PERIOD MONTH_BEGIN, BY ANCHOR PERIOD MONDAY, and so on.

The PERIOD value of the row along with fixed durations enables weighted computations such as “compute the weekly weighted average of inventory cost.”

For example, the EXPAND ON clause in the following statement (which assumes the existence of a column named pd1 in employee) is not valid:

   SELECT *

   FROM employee

   WHERE salary IN (SELECT salary

                    FROM salary_table

                    WHERE salary > 10000

                    EXPAND ON pd1);

If the specified column sequential position is not valid, for example, if it is greater than the projected number of columns, Teradata Database returns an error.

The specified column must have a data type of PERIOD.

You must specify a table reference in a SELECT statement with an EXPAND ON clause.

This includes cases where the derived table is contained within a subquery.

See “Example 3: Expansion Over a UNION Operator” on page 202.

See “Example 3: Expansion Over a UNION Operator” on page 202.

Note that a view that contains an EXPAND ON clause in its definition is not updatable.

You can work around this restriction by specifying the TOP n operator within a derived table and then specifying the EXPAND ON clause in the outer query.

The ORDER BY operation is then performed on the expanded rows if you specify an expanded column in the ORDER BY clause.

The expansion defaults to the rules for interval literals described in “Rules and Restrictions for Interval Expansion” on page 194.

The data type of the PERIOD expression must be compatible with the PERIOD data type of expanded_column_name.

If the specified period is not null and does not overlap with the row, then the row is not eligible for expansion.

The interval can either be an interval constant, or an anchored interval derived from the anchor name such as any day of a week, the MONTH_BEGIN keyword, or the MONTH_END keyword.

You must ensure that the appropriate join condition is specified in such cases. If no other join conditions are specified, then the system performs a Product Join on the referenced tables.

See “Example 15: Join Before Expansion” on page 217.

See “Example 16: Nullified EXPAND Operation” on page 218.

See “Example 17: Null Expansion Period Producing a Null Expanded Value” on page 220.

Rules and Restrictions for Interval Expansion

The rules and restrictions for the use of Interval Expansions are.

In this process, the last expanded row might not always cover the expansion interval duration. If this occurs, the result is a partial period, which is a period having a duration that is less than the expansion interval.

Anchor Period and Anchor Point Expansion

The rules and restriction for the use of Anchor PERIOD and Anchor Point Expansions are:

If you do not specify a time literal, the time literal value defaults to '00:00:00.000000+00:00' for an anchor name of ANCHOR_MILLISECOND, ANCHOR_SECOND, ANCHOR_MINUTE, ANCHOR_HOUR, WEEK_BEGIN, MONTH_BEGIN, QUARTER_BEGIN, or YEAR_BEGIN and to '23:59:59.999999+00:00' for any other anchor name.

The precision of the default value is set to the precision of the expansion expression. The default value includes the time zone value of +00:00 if the expansion expression specifies a time zone; otherwise, the value is the session time zone and does not specify a time zone.

The anchor, for example MONTH_BEGIN, is computed based on the session time zone. Thus, for two sessions that are at different time zones, the output will probably be different. For an example of this, see “Example 7: Same Expansion in Two Different Sessions in Different Time Zones” on page 208.

Each expanded value for the row in the result has the first day of the corresponding month as the BEGIN bound and the expansion interval defaults to INTERVAL '1' MONTH. The BEGIN bound of the expanded value for each result row is 'YYYY-MM-01'.

See the first SELECT request in “Example 4: EXPAND ON MONTH_BEGIN and MONTH_END” on page 204.

Each expanded value for the result row has the last day of the corresponding month as its BEGIN bound, and the expansion interval defaults to INTERVAL '1' MONTH.

The BEGIN bound of the expanded value for each result row is 'YYYY-MM-DD' where DD is one of 28, 29, 30, or 31, depending on the month and the year.

See the second SELECT request in “Example 4: EXPAND ON MONTH_BEGIN and MONTH_END” on page 204.

See “Example 1: Expansion on an Anchor Point Using WEEK_DAY” on page 200.

Rules and Restrictions for Anchor Point Expansion of the EXPAND ON Clause

The rules and restrictions for the use of Anchor Point Expansions of EXPAND ON are:

For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2007-08-14', DATE '2007-08-17'), then the expansion period starts on Tuesday and ends on Friday in the same week.

In this case, the input row does not produce any expanded rows in the expanded result.

In this case, the expanded row set does not have a row corresponding to the beginning bound of the expansion period.

For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2007-08-15', DATE '2007-08-25'), the expanded result contains only one point, which is DATE '2007-08-20'.

In such a case, the expanded rows contain a row with the beginning bound of the expansion period.

For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2007-08-20', DATE '2007-08-25'), then the last expanded row contains DATE '2007-08-20'.

This also means that each row expands to as many anchor points that exist in the expansion period.

Anchor Period Expansion of the EXPAND ON Clause

The rules and restrictions for the use of Anchor Period Expansion of EXPAND ON are:

For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2011-08-14', DATE '2011-08-17'), then the expansion period starts on Tuesday, ends on Friday of the same week, and the expanded row is a singleton with a resulting anchor period of PERIOD(DATE '2011-08-13', DATE'2011-08-20').

In this case, there is only one expanded row for the input row.

Such a row produces more than one expanded row in its result.

For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2011-08-15', DATE '2011-08-25').

In this case, the expanded result has two anchor periods, PERIOD(DATE '2011‑08-13', DATE '2011-08-20') and PERIOD(DATE '2011-08-20', DATE '2011‑08‑27').

For example, if the expansion is by MONDAY and the expansion period is PERIOD(DATE '2011-08-20', DATE '2011-08-27'), then the expanded row contains PERIOD(DATE '2011-08-20', DATE '2011-08-27').

In an anchor expansion, Teradata Database derives the BEGIN bound of the first expanded row from the business calendar set in the current session. The BEGIN bound of the first expanded row is derived from the YearBeginDate, WeekStart, and CalendarPeriod columns from the BusinessCalendarPattern table.

For example, a row has period from 1-Jan-2008 to 31-May-2008, YearBeginDate is 15-Mar-2008, and CalendarPeriod is from 1-Jan-08 to 30-Oct-08. Expanding by anchor point MONTH_BEGIN results in 4 rows, where the first row is from 15-Jan-08 to 15‑Feb-08 and second row is from 15-Feb-08 to 15-Mar-08 and 3rd row is from 15‑Mar-08 to 15-Apr08 and 4th rows is from 15-Apr-08 to 15-May-08. The MONTH_BEGIN value is derived from the YearBeginDate value, which is January 15 and, because the Calendar begins in January, the first month starts on January 15.

For example, if the business calendar for the session is ISO and you specify expansion by WEEK_BEGIN, Teradata Database translates the WEEK_BEGIN expansion to EXPAND ON ….BY ANCHOR MONDAY because the week begins on Monday.

For information on how the ISO calendar computes results, see the section named “About ISO Computation” in SQL Functions, Operators, Expressions, and Predicates.

For example, if you want to execute an expansion by business days, you must perform a normal expansion and then apply business functions on all of the expanded rows using a derived table to get the desired expansion by business days.

Expand Interval Literal Expressions

The following table explains the meanings of the expand interval syntax variables for the EXPAND ON clause.

Anchor Name Literal Expressions

The following table lists the ANCHOR anchor_name variables for the EXPAND ON clause.

Example : Expansion on an Anchor Point Using WEEK_DAY

Create a table named tdate with the following definition.

     CREATE SET TABLE tdate (

       id       INTEGER,

       quantity INTEGER,

       pd       PERIOD(DATE))

     PRIMARY INDEX (id);

You insert two rows into tdate so its contents are as follows.

11

110

2005-02-03, 2005-06-20

10

100

2004-01-03, 2004-05-20

Submit a SELECT statement against tdate that specifies an EXPAND ON clause anchored by a day of the week, Monday, so that each expanded row begins from a Monday, as specified in the statement, and the duration of each expanded period is seven days.

     SELECT id, BEGIN(bg) 

     FROM tdate

     EXPAND ON pd AS bg BY ANCHOR MONDAY;

Teradata Database returns tdate details for each week of a given period, beginning on the first Monday from the eligible data, as you specified in the BY ANCHOR clause of the statement.

Because the first row in tdate starts on a Thursday, not a Monday, the expanded row starts on the next sequential Monday date, which is February 7, and then continues in weekly granular increments.

Example : Expansion on an Anchor Point Using ANCHOR_SECOND

Create a table named t2 with the following definition.

     CREATE TABLE t2 (

       id       INTEGER

       quantity INTEGER

       pd       PERIOD(DATE))

       PRIMARY INDEX (id);

The input row for the example statement is of type TIMESTAMP and contains the following timestamp period data. (2011-01-01 10:15:20.000001, 2011-01-01 10:15:25.000009).

Submit the following SELECT statement on table t2.

     SELECT BEGIN(expd) 

     FROM t2 

     EXPAND ON pd AS expd BY ANCHOR ANCHOR_SECOND;

The result set contains 5 rows because the input row has a period of 5 seconds, beginning with 10:15:20.000001 and ending with 10:15:25.000009.

begin expd

----------

2011-01-01 10:15:21.000000

2011-01-01 10:15:22.000000

2011-01-01 10:15:23.000000

2011-01-01 10:15:24.000000

2011-01-01 10:15:25.000000

Example : Expansion Over a UNION Operator

Suppose you create a table named tdate1 with the following definition.     

     CREATE SET TABLE tdate1 (

       id       INTEGER,

       quantity INTEGER,

       pd       PERIOD(DATE))

     PRIMARY INDEX (id);

Table tdate1 contains the following rows.

12

120

2006-02-03, 2006-06-20

13

130

2005-01-03, 2005-05-20

You now submit the following unioned SELECT statement against tdate, as defined in “Example 1: Expansion on an Anchor Point Using WEEK_DAY” on page 200, and tdate1 that specifies an EXPAND ON clause for both statements, each having a one month interval granularity.

     SELECT id, quantity, expd 

     FROM tdate

     EXPAND ON pd AS expd BY INTERVAL '1' MONTH 

     UNION 

     SELECT id, quantity, expd 

     FROM tdate1 

     EXPAND ON pd AS expd BY INTERVAL '1' MONTH;

In this example, Teradata Database first expands the rows in tables tdate and tdate1 and then unions the resulting rows from the queries on the expanded results.

Also note that Teradata Database returns a warning to the requestor that some rows in the expanded result might have an expanded period duration that is less than the duration of the specified interval.

Teradata Database returns tdate1 details for each month of a given period for these two queries and then unions the result rows as follows.

Example : EXPAND ON MONTH_BEGIN and MONTH_END

This example shows how to use MONTH_BEGIN and MONTH_END in an EXPAND ON clause.

First create the ttimestamp table as follows.

     CREATE SET TABLE ttimestamp (

       id       INTEGER,

       quantity INTEGER,

       pd       PERIOD(TIMESTAMP(0)))

     PRIMARY INDEX (id);

Table ttimestamp contains the following rows.

11

110

2005-02-01 01:10:40, 2005-06-20 05:20:50

10

100

2004-01-03 01:10:40, 2004-05-31 20:20:50

When you specify an EXPAND ON clause by MONTH_BEGIN or MONTH_END, every expanded row starts from either the MONTH_BEGIN value or from the MONTH_END value for that month, and the granularity of each expanded period is one month. In this example, the table data is shown with the default session time zone set to INTERVAL ‘00:00’ HOUR TO MINUTE.

     SET TIME ZONE INTERVAL ‘00:00’ HOUR TO MINUTE;

The following SELECT statement specifies an EXPAND … BY MONTH_BEGIN.

     SELECT id, quantity, BEGIN(bg)

     FROM ttimestamp 

     EXPAND ON pd AS bg BY ANCHOR MONTH_BEGIN;

Each row is expanded at the default time literal value 00:00:00+00:00 for each MONTH_BEGIN value.

id

--

qty

---

bg

--

11

110

2005-03-01 00:00:00

11

110

2005-04-01 00:00:00

11

110

2005-05-01 00:00:00

11

110

2005-06-01 00:00:00

10

100

2004-02-01 00:00:00

10

100

2004-03-01 00:00:00

10

100

2004-04-01 00:00:00

10

100

2004-05-01 00:00:00

The following SELECT statement specifies an EXPAND … BY ANCHOR MONTH_END, but is otherwise identical to the previous statement.

     SELECT id, quantity, BEGIN(bg) 

     FROM ttimestamp 

     EXPAND ON pd AS bg BY ANCHOR MONTH_END;

Each row is expanded at the default time literal value 23:59:59+00:00 at each month end.

id

--

quantity

--------

bg

--

11

110

2005-02-28 23:59:59

11

110

2005-03-31 23:59:59

11

110

2005-04-30 23:59:59

11

110

2005-05-31 23:59:59

10

100

2004-01-31 23:59:59

10

100

2004-02-29 23:59:59

10

100

2004-03-31 23:59:59

10

100

2004-04-30 23:59:59

Example : EXPAND ON and DISTINCT

This example shows how Teradata Database performs the DISTINCT operation after expansion occurs.

     CREATE SET TABLE products (

       product_id       INTEGER,

       product_price    DECIMAL(5,2),

       product_duration PERIOD(DATE))

     PRIMARY INDEX (product_id);

Assume that you have the following rows in products.

When you specify a DISTINCT operator in the query expression and the expanded column in the select list of your query, Teradata Database performs the DISTINCT operation after expansion and removes the duplicate rows from the expanded result (see the example below).

     SELECT DISTINCT product_id, pd 

     FROM products 

     EXPAND ON product_duration AS pd BY ANCHOR PERIOD MONTH_BEGIN;

This SELECT statement returns the following response set.

Example : Same Expansion in Two Different Sessions Using Different Time Zone Intervals

The beginning bound of the expanding period value is adjusted to start at the time specified by time_literal before it expands the rows. The anchor point, for example MONTH_BEGIN, is computed based on the session time zone. As a result, the output can be different for two sessions that are at two different time zones.

In this example the time zone for the first session is set to INTERVAL -’01:00’ HOUR TO MINUTE, and the time zone for the second session is set to INTERVAL ‘02:00’ HOUR TO MINUTE.

The output of the identical SELECT statement submitted in the two time zones differs, with the statement submitted in the second session returning one additional row (shaded in red).

You set the time zone for the first session as follows and then submit the indicated SELECT statement anchored on MONTH_BEGIN:

     SET TIME ZONE INTERVAL -'01:00' HOUR TO MINUTE;

     SELECT id, quantity, BEGIN(pd) AS bg 

     FROM ttimestamp 

     EXPAND ON PERIOD bg BY ANCHOR MONTH_BEGIN;

id

--

quantity

--------

bg

--

11

110

2005-03-01 00:00:00

2005-04-01 00:00:00

2005-05-01 00:00:00

2005-06-01 00:00:00

10

100

2004-02-01 00:00:00

100

2004-03-01 00:00:00

100

2004-04-01 00:00:00

100

2004-05-01 00:00:00

You set the time zone for the second session as follows and then submit the indicated SELECT statement anchored on MONTH_END:

     SET TIME ZONE INTERVAL '02:00' HOUR TO MINUTE;

     SELECT id, quantity, BEGIN(pd) AS bg 

     FROM ttimestamp 

     EXPAND ON PERIOD bg BY ANCHOR MONTH_BEGIN;

id

--

quantity

--------

bg

--

11

110

2005-02-01 00:00:00

11

110

2005-03-01 00:00:00

11

110

2005-04-01 00:00:00

11

110

2005-05-01 00:00:00

11

110

2005-06-01 00:00:00

10

100

2004-02-01 00:00:00

10

100

2004-03-01 00:00:00

10

100

2004-04-01 00:00:00

10

100

2004-05-01 00:00:00E

Example : Same Expansion in Two Different Sessions in Different Time Zones

The beginning bound of the expanding period value is adjusted to start at the time specified by time_literal before it expands the rows. The anchor point, for example MONTH_BEGIN, is computed based on the session time zone. As a result, the output can be different for two sessions that are at two different time zones.

In the following example, the time literal defaults to 00:00:00 at the session time zone because the EXPAND ON clause input row does not specify a time zone (because the value of duration has a PERIOD(TIMESTAMP) data type). After the time literal is converted to UTC, it is the previous day; therefore, the previous day-to-month begin is checked with the row, and when it is returned, Teradata Database adds the session time.

First you create the following table.

     CREATE SET TABLE test (

       testid   INTEGER,

       duration PERIOD(TIMESTAMP))

     PRIMARY INDEX (testid);

Table test contains the following row.

testid

------

duration (at UTC)

-----------------

ABC

2002-01-31 15:30:00, 2002-05-31 15:00:00

You then perform the following anchor point expansion by MONTH_BEGIN with a default time zone literal.

     SET TIME ZONE INTERVAL '09:00' HOUR TO MINUTE;

     SELECT BEGIN(xyz) 

     FROM test 

     EXPAND ON duration AS xyz BY ANCHOR MONTH_BEGIN;

This statement returns the following rows:

BEGIN(xyz)

----------

2002-03-01 00:00:00

2002-04-01 00:00:00

2002-05-01 00:00:00

In the following example, the time literal is 20:00:00 at session time zone because the input row does not specify a time zone (because the value of duration has a PERIOD(TIMESTAMP) data type). After the time literal is converted to UTC, it is the next day; therefore, Teradata Database checks the next day to month end with the row, and when it is returns the row, it adds the session time.

You have the following table.

     CREATE SET TABLE test1 (

       testid   INTEGER,

       duration PERIOD(TIMESTAMP))

     PRIMARY INDEX (testid);

Table test1 contains the following row.

testid

------

duration (at UTC)

-----------------

ABC

2005-12-03 04:30:00, 2006-04-01 

You perform the following anchor period expansion by MONTH_END with a time literal and with a default session time zone.

     SET TIME ZONE INTERVAL -'07:00' HOUR TO MINUTE;

     SELECT BEGIN(xyz)

     FROM test1 

     EXPAND ON duration AS xyz BY ANCHOR MONTH_END AT TIME ‘20:00:00’;

BEGIN(XYZ)

----------

2005-12-31 20:00:00

2006-01-31 20:00:00

2006-02-28 20:00:00

In the next example, the time literal value is 07:00:00 at time zone +10:00. After the time literal is converted to UTC, the time is 21:00 on the previous day. Therefore, Teradata Database checks the previous day to month end value with the row and, when the time series value is returned by the statement, Teradata Database adds the session time, which is 00:00.

You have the following table.

     CREATE SET TABLE test2 (

       testid   INTEGER,

       duration PERIOD(TIMESTAMP))

     PRIMARY INDEX (testid);

Table test2 contains the following row.

testid

------

duration (at UTC)

-----------------

timeseries

2005-12-30 22:30:00, 2006-04-29 18:00:00

You perform the following anchor period expansion by MONTH_END, specifying both a time literal and a time zone.

     SET TIME ZONE INTERVAL '00:00' HOUR TO MINUTE;

     SELECT timeseries 

     FROM test2

     EXPAND ON duration AS timeseries BY ANCHOR PERIOD MONTH_END 

                                         AT TIME '07:00:00+10:00';

This statement returns the following row set.

timeseries

----------

2005-12-30 21:00:00, 2006-01-30 21:00:00

2006-01-30 21:00:00, 2006-02-27 21:00:00

2006-02-27 21:00:00, 2006-03-30 21:00:00

2006-03-30 21:00:00, 2006-04-29 21:00:00

Example : EXPAND ON and OLAP Functions

This example shows the use of the EXPAND ON clause in a SELECT statement that also specifies an OLAP function. In this case, the OLAP function specified is RANK. For details about the RANK function, see SQL Functions, Operators, Expressions, and Predicates.

First create the player_history table as follows.

     CREATE SET TABLE player_history (

       player_id INTEGER,	

       duration  PERIOD(DATE),

       grade     CHARACTER(1))

     PRIMARY INDEX (player_id);

The player_history table contains the following rows.

The following SELECT statement specifies the RANK OLAP function in the select list.

     SELECT playerid, BEGIN(expd), RANK(grade ASC) AS a, grade

     FROM player_history

     WHERE player_id = 1000 QUALIFY a < 3 

     EXPAND ON duration AS expd BY ANCHOR MONDAY;

The query returns the following response set.

Example : EXPAND ON With a Join

This example shows using a join with the EXPAND ON clause.

First create the student table.

     CREATE SET TABLE student (

       id                 INTEGER,

       name               CHARACTER(10) CHARACTER SET LATIN 

                          NOT CASESPECIFIC,

       duration_begin_end PERIOD(DATE))

     PRIMARY INDEX (id);

The student table contains the following row.

101

ABC

2004-01-01, 2004-12-31

Now create the course table.

     CREATE SET TABLE course (

       name             CHARACTER(10) CHARACTER SET LATIN 

                        NOT CASESPECIFIC,

       student_id       INTEGER,

       course_begin_end PERIOD(DATE))

     PRIMARY INDEX (name);

The course table contains the following rows.

CPP

101

2004-08-01,2004-08-30

Java

101

2004-07-01,2004-07-30

C

101

2004-04-01,2004-06-30

Submit a SELECT statement that returns the month a student was enrolled in a particular course.

This statement joins the student table (expanded on an interval literal and aliased as dt) with the course table using a mix of equality and inequality predicates in its WHERE clause.

     SELECT course.name, EXTRACT(MONTH FROM BEGIN(expd) 

     FROM (SELECT student_id, expd 

           FROM student

           EXPAND ON duration_begin_end AS expd BY INTERVAL '1' MONTH)

                     AS dt, course AS c

     WHERE c.student_id = dt.id 

     AND   (BEGIN(c.course_begin_end) < END(expd) 

     AND    BEGIN(expd) < END(c.course_begin_end)  

     AND    dt.id = 101;

This statement returns the following five rows.

course-name

-----------

extract(month from expd)

------------------------

C

4

C

5

C

6

CPP

8

Java

7

Teradata Database also returns a 9308 warning message for this statement.

Example : EXPAND ON For an Anchored Interval

This example shows the use of an anchored interval for doing anchor period and anchor point expansions. For an anchor period expansion, the expanded period value must overlap the expanding period, while for an anchor point expansion, the begin value of the expanded period value must be contained in the expanding period, which is a more restrictive condition.

First create the sold_products table

     CREATE SET TABLE sold_products, NO FALLBACK  (

       product_id       INTEGER,

       product_price    DECIMAL(10,2),

       product_duration PERIOD(DATE))

     PRIMARY INDEX (product_id);

The sold_products table contains the following rows.

1000

100.00

2007-02-15, 2007-08-11

1001

 99.99

2007-03-04, 2007-05-01

The following SELECT statement specifies an anchor period of MONTH_BEGIN. This is an anchor period expansion.

     SELECT product_id, product_price, product_duration, expd

     FROM sold_products

     EXPAND ON product_duration AS expd BY ANCHOR PERIOD MONTH_BEGIN;

The statement returns the following nine rows.

product_id

----------

product_price

-------------

product_duration

----------------

expd

----

1000

100.00

2007-02-15,2007-08-11

2007-02-01,2007-03-01

1000

100.00

2007-02-15,2007-08-11

2007-03-01,2007-04-01

1000

100.00

2007-02-15,2007-08-11

2007-04-01,2007-05-01

1000

100.00

2007-02-15,2007-08-11

2007-05-01,2007-06-01

1000

100.00

2007-02-15,2007-08-11

2007-06-01,2007-07-01

1000

100.00

2007-02-15,2007-08-11

2007-07-01,2007-08-01

1000

100.00

2007-02-15,2007-08-11

2007-08-01,2007-09-01

1001

 99.99

2007-03-04,2007-05-01

2007-03-01,2007-04-01

1001

 99.99

2007-03-04,2007-05-01

2007-04-01,2007-05-01

For an anchor point expansion done on the same data, the shaded rows would not appear, as the following example shows.

Note the following distinction between anchor point and anchor period expansions.

Submit the following SELECT statement, which differs from the previous statement only in specifying the BEGIN bound function on product_duration instead of simply specifying the column name. This is an anchor point expansion done on the same data as the previous anchor period expansion.

     SELECT product_id, product_price, product_duration, BEGIN(expd)

     FROM sold_products

     EXPAND ON product_duration AS expd BY ANCHOR MONTH_BEGIN;

This statement returns seven rows, rather than nine, with the rows shaded in red from the previous example not appearing in the result set.

product_id

----------

product_price

-------------

product_duration

----------------

begin(expd)

-----------

1000

100.00

2007-02-15,2007-08-11

2007-03-01

1000

100.00

2007-02-15,2007-08-11

2007-04-01

1000

100.00

2007-02-15,2007-08-11

2007-05-01

1000

100.00

2007-02-15,2007-08-11

2007-06-01

1000

100.00

2007-02-15,2007-08-11

2007-07-01

1000

100.00

2007-02-15,2007-08-11

2007-08-01

1001

 99.99

2007-03-04,2007-05-01

2007-04-01

Example : EXPAND ON and Span Grouping

This example shows the use of the EXPAND ON clause with grouping on a span of entries from the select list.

First create the stock table.

     CREATE SET TABLE stock (

       stock_id        INTEGER,

       stock_quantity  INTEGER,

       begin_end_date  PERIOD(DATE))

     PRIMARY INDEX (stockid);

The stock table contains the following rows.

100

200

2005-10-10,2005-11-15

101

 20

2005-06-01,2005-08-31

This example shows how you can compute a weighted average for the stock_quantity column on a monthly basis.

Assume that udf_agspan is an aggregate UDF that adds the stock quantity for given month of a year and then divides the sum by the number of days in that month. This gives a different result when compared to the AVG function when the row is not spanning the whole month.

     SELECT udf_agspan(stock_quantity, 

            EXTRACT(YEAR FROM BEGIN(expdcol)), 

            EXTRACT(MONTH FROM BEGIN(expdcol)))

            (FORMAT ‘-----9.999’) AS wavg,

            EXTRACT(YEAR FROM BEGIN(expdcol) AS yr,

            EXTRACT(MONTH FROM BEGIN(expdcol) AS mn,

            stock_id

     FROM (SELECT stock.*, expdcol

           FROM stock 

           EXPAND ON begin_end_date AS expdcol BY INTERVAL '1'DAY) AS dt

     GROUP BY 2,3,4;

This statement returns the following rows.

wavg

--------

yr

----

mn

--

stock_id

--------

141.935

 93.333

2005

11

100

 20.000	

2005

06

101

 20.000

2005

07

101

 19.355

2005

08

Example : EXPAND ON for a Moving Average

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.

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

2006-01-04 15:00:00 , 2006-01-04 18:00:00

1000

2006-01-05 09:00:00 , 2006-01-05 12:00:00

1000

2006-01-05 12:00:00 , 2006-01-01 15:00:00

1000

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

21.00

2006-01-01 12:00:00 , 2006-01-01 15:00:00

19.00

2006-01-01 15:00:00 , 2006-01-01 18:00:00

22.00

2006-01-02 09:00:00 , 2006-01-02 12:00:00

23.00	

2006-01-02 12:00:00 , 2006-01-02 15:00:00

24.00

2006-01-02 15:00:00 , 2006-01-02 18:00:00

18.00

2006-01-03 09:00:00 , 2006-01-03 12:00:00

2006-01-03 12:00:00 , 2006-01-03 15:00:00

25.00

2006-01-03 15:00:00 , 2006-01-03 18:00:00

29.00

2006-01-04 09:00:00 , 2006-01-04 12:00:00

26.00

2006-01-04 12:00:00 , 2006-01-04 15:00:00

26.00

2006-01-04 15:00:00 , 2006-01-04 18:00:00

26.00

2006-01-05 09:00:00 , 2006-01-05 12:00:00

24.00

2006-01-05 12:00:00 , 2006-01-01 15:00:00

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;

Example : EXPAND ON for a WEEK_BEGIN Anchor Point

This example expands employee using a WEEK_BEGIN anchor point.

Assume the following table definition.

     CREATE SET TABLE employee, NO FALLBACK (

       eid       INTEGER,

       ename     CHARACTER(20) CHARACTER SET LATIN NOT CASESPECIFIC,

       jobperiod PERIOD(DATE))

     PRIMARY INDEX (eid);

Table employee contains the following single row.

employee

eid

---

ename

-----

jobperiod

---------

1001

Xavier

2008-06-02,2008-06-24

Expand employee by WEEK_BEGIN.

     SELECT eid, ename, BEGIN(expd) AS tsp 

     FROM employee

     EXPAND ON jobperiod expd BY ANCHOR WEEK_BEGIN;

In this example, each expanded row value starts on a Monday because the week starts on a Monday.

employee

eid

---

ename

-----

tsp

---

1001

Xavier

2008-06-09

1001

Xavier

2008-06-16

1001

Xavier

2008-06-23

Example : EXPAND ON for a QUARTER_BEGIN Anchor Period

This example expands employee using a QUARTER_BEGIN anchor period.

     SELECT eid, ename, BEGIN(expd) AS tsp 

     FROM employee

     EXPAND ON jobdperiod expd BY ANCHOR PERIOD QUARTER_BEGIN;

eid

---

ename

-----

tsp

---

1001

Xavier

2008-04-01

Example : Join Before Expansion

This example shows how Teradata Database joins the tables specified in an EXPAND ON clause when the specified period expression specifies a column from a table that is not specified in the FROM clause (see step 5 of the EXPLAIN output, highlighted in boldface type).

     CREATE SET TABLE DR.t3, NO FALLBACK , NO BEFORE JOURNAL, 

                             NO AFTER JOURNAL, CHECKSUM = DEFAULT (

       a  INTEGER,

       b  INTEGER,

       pd PERIOD(TIMESTAMP(6)))

     PRIMARY INDEX (a);

     CREATE SET TABLE DR.t4, NO FALLBACK, NO BEFORE JOURNAL, 

                             NO AFTER JOURNAL, CHECKSUM = DEFAULT (

       x  INTEGER NOT NULL,

       y  INTEGER NOT NULL,

       pd PERIOD(DATE))

     PRIMARY INDEX (x);

     EXPLAIN SELECT expd 

             FROM t4 

             EXPAND ON t3.pd AS expd;

 *** Help information returned. 27 rows.

 *** Total elapsed time was 1 second.

Explanation

------------------------------------------------------------------------

  1) First, we lock a distinct DF2."pseudo table" for read on a

     RowHash to prevent global deadlock for DF2.t4.

  2) Next, we lock a distinct DF2."pseudo table" for read on a

     RowHash to prevent global deadlock for DF2.t3.

  3) We lock DF2.t4 for read, and we lock DF2.t3 for read.

  4) We do an all-AMPs RETRIEVE step from DF2.t4 by way of an

     all-rows scan with no residual conditions into Spool 3 (all_amps),

     which is duplicated on all AMPs.  The size of Spool 3 is estimated

     with low confidence to be 4 rows.  The estimated time for this

     step is 0.03 seconds.

  5) We do an all-AMPs JOIN step from DF2.t3 by way of an all-rows

     scan with no residual conditions, which is joined to Spool 3 (Last

     Use) by way of an all-rows scan. DF2.t3 and Spool 3 are joined

     using a product join, with a join condition of ("(1=1)").  The

     result goes into Spool 2 (all_amps), which is built locally on the

     AMPs.  The size of Spool 2 is estimated with low confidence to be

     4 rows (116 bytes).  The estimated time for this step is 0.05

     seconds.

  6) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of

     an all-rows scan and EXPAND ON (DF2.t3.pd) into Spool 1

     (group_amps), which is built locally on the AMPs.  The size of

     Spool 1 is estimated with no confidence to be 120 rows (5,400

     bytes).

  7) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 1 are sent back to the user as the result of

     statement 1.

Example : Nullified EXPAND Operation

This example shows how when an expanded column is not specified in the select list of a query, but a DISTINCT operator is specified, the EXPAND operation is nullified.

     CREATE SET TABLE df2.t1, NO FALLBACK, NO BEFORE JOURNAL, 

                              NO AFTER JOURNAL, CHECKSUM = DEFAULT,

                              DEFAULT MERGEBLOCKRATIO (

       i  INTEGER,

       j  INTEGER,

       pd PERIOD(DATE) FORMAT 'yyyy-mm-dd') 

     PRIMARY INDEX (i);

The first SELECT statement in this example does not specify the DISTINCT operator.

     EXPLAIN SELECT i,j 

             FROM t1 

             EXPAND ON pd AS expd BY INTERVAL '1' DAY;

 *** Help information returned. 17 rows.

 *** Total elapsed time was 1 second.

Explanation

------------------------------------------------------------------------

  1) First, we lock a distinct DF2."pseudo table" for read on a

     RowHash to prevent global deadlock for DF2.t1.

  2) Next, we lock DF2.t1 for read.

  3) We do an all-AMPs RETRIEVE step from DF2.t1 by way of an

     all-rows scan with no residual conditions into Spool 2 (all_amps),

     which is built locally on the AMPs.  The size of Spool 2 is

     estimated with low confidence to be 2 rows (74 bytes).  The

     estimated time for this step is 0.01 seconds.

  4) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of

     an all-rows scan and EXPAND ON (DF2.t1.pd) into Spool 1

     (all_amps), which is built locally on the AMPs.  The size of Spool

     1 is estimated with no confidence to be 60 rows (2,580 bytes).

  5) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 1 are sent back to the user as the result of

     statement 1.

The following SELECT statement does specify the DISTINCT operator. Note the difference in the EXPLAIN report, where you can see that this output is identical to the previous output with the exception that step 4 of the previous EXPLAIN output (highlighted in boldface type) is missing because the DISTINCT operator has nullified the effect of the EXPAND ON specification, so it is not performed.

     EXPLAIN SELECT DISTINCT i,j 

             FROM t1 

             EXPAND ON pd AS expd BY INTERVAL '1' day;

 *** Help information returned. 15 rows.

 *** Total elapsed time was 1 second.

Explanation

---------------------------------------------------------------------------

  1) First, we lock a distinct DF2."pseudo table" for read on a

     RowHash to prevent global deadlock for DF2.t1.

  2) Next, we lock DF2.t1 for read.

  3) We do an all-AMPs RETRIEVE step from DF2.t1 by way of an

     all-rows scan with no residual conditions into Spool 1 (all_amps),

     which is built locally on the AMPs.  Then we do a SORT to order

     Spool 1 by the sort key in spool field1 (DF2.t1.i, DF2.t1.j)

     eliminating duplicate rows.  The size of Spool 1 is estimated with

     low confidence to be 2 rows (102 bytes).  The estimated time for

     this step is 0.01 seconds.

  4) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 1 are sent back to the user as the result of

     statement 1.  The total estimated time is 0.01 seconds.

Example : Null Expansion Period Producing a Null Expanded Value

This example shows how a null expansion period produces a null expanded value. In this example, the value for PERIOD(DATE) in column pd is null, so the expansion on pd, expd, is also null.

     CREATE SET TABLE DF2.t4, NO FALLBACK, NO BEFORE JOURNAL, 

                              NO AFTER JOURNAL, CHECKSUM = DEFAULT (

       x  INTEGER NOT NULL,

       y  INTEGER NOT NULL,

       pd PERIOD(DATE))

     PRIMARY INDEX (x);

First show that column pd is null.

     SELECT * 

     FROM t4;

      *** Query completed. One row found. 3 columns returned.

      *** Total elapsed time was 1 second.

      x            y           pd

     -----------  -----------  -----------------------------------------

              10           30  ?

Then show that the expansion on pd aliased as expd, is also null.

     SELECT x, expd  

     FROM t4 

     EXPAND ON pd AS expd;

      *** Query completed. One row found. 2 columns returned.

      *** Total elapsed time was 1 second.

               x  expd

     -----------  ------------------------------------------------

              10  ?

For More Information

For more information about the EXPAND ON clause, see Temporal Table Support.