Examples - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

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.

 

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.

 

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.

 

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 182, 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.

 

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.

 

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.

 

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.

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;
 

The output of the same SELECT statement submitted in the second session returning one additional row (shaded in red):

     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;
 

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.

 

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:

 

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:

 

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’;
 

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.

 

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.

 

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.

 

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.

 

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.

 

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.

 

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, with the original two rows highlighted in red:

 

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.

 

IF the expansion is an …

THEN the …

anchor period expansion

expanded period value must overlap the expanding period.

anchor point expansion

begin value of the expanded period value must be contained within the expanding period.

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.

 

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.

 

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 provides 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.

 

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.

 

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;

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.

 

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.

 

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;
 

Example : Join Before Expansion

Following are the table definitions for this example:

     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);

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 .

     SELECT expd 
             FROM t4 
             EXPAND ON t3.pd AS expd;

An EXPLAIN shows a JOIN with t3.

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;

An EXPLAIN shows an EXPAND ON t1.pd.

The following SELECT statement specifies the DISTINCT operator.

     SELECT DISTINCT i,j 
             FROM t1 
             EXPAND ON pd AS expd BY INTERVAL '1' day;
 

An EXPLAIN shows a SORT and elimination of duplicate rows. The DISTINCT operator makes the EXPAND ON step unnecessary.

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 about the EXPAND ON clause, see Temporal Table Support.