17.05 - Example: EXPAND ON and OLAP Functions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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