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 Teradata Vantageā¢ - SQL Functions, Expressions, and Predicates, B035-1145.
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.
player_id | duration | grade |
1000 | 2007-06-03, 2007-07-03 | A |
1000 | 2007-07-03, 2007-08-03 | B |
1000 | 2007-08-03, 2007-09-03 | C |
1001 | 2007-07-03, 2007-08-03 | A |
1001 | 2007-08-03, 2007-09-03 | D |
1001 | 2007-09-03, 2007-10-03 | E |
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.
player_id | BEGIN(expd) | a | grade |
1000 | 2007-06-04 | 1 | A |
1000 | 2007-06-11 | 1 | A |
1000 | 2007-06-18 | 1 | A |
1000 | 2007-06-25 | 1 | A |
1000 | 2007-07-02 | 1 | A |
1000 | 2007-07-09 | 2 | B |
1000 | 2007-07-16 | 2 | B |
1000 | 2007-07-23 | 2 | B |
1000 | 2007-07-30 | 2 | B |