Example: EXPAND ON and OLAP Functions - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

This example shows the use of the EXPAND ON clause in a SELECT statement that also specifies an OLAP function. 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