RANK (ANSI) Function Example | Teradata Vantage - Example: Using RANK(ANSI) with Time Series Data - Advanced SQL Engine - Teradata Database

Time Series Tables and Operations

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
cxa1555383531762.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1208
lifecycle
previous
Product Category
Teradata Vantageā„¢

The following example shows how to use RANK(ANSI) to rank the averages of particular timebuckets across an entire time series.

The examples use the tables and data setup in Table and Data Definition for Time Series Aggregates Examples.

Perform the AVG before using RANK(ANSI). The AVG function can be used with both Primary Time Index (PTI) and non-PTI tables. The following examples get the averages from the time series data.

/*PTI Table*/
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE)
FROM OCEAN_BUOYS
WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00' AND BUOYID=0
GROUP BY TIME (MINUTES(10) AND BUOYID)
ORDER BY 1,2,3; 

/*Non-PTI Table*/
SELECT $TD_TIMECODE_RANGE, $TD_GROUP_BY_TIME, BUOYID, AVG(TEMPERATURE)
FROM OCEAN_BUOYS_NONPTI
WHERE TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00'
GROUP BY TIME (MINUTES(10) AND BUOYID)
USING TIMECODE(TIMECODE)
ORDER BY 1,2,3;

Result:

The results of both queries are identical so only one result set is shown.
TIMECODE_RANGE GROUP BY TIME (MINUTES(10)) BUOYID AVG (TEMPERATURE)
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') 1 0 54
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') 2 0 55
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00' 7 1 74
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') 13 14 50
('2014-01-06 10:10:00.000000+00:00', '2014-01-06 10:20:00.000000+00:00') 14 14 43

Use RANK(ANSI) to rank the averages for each individual time series:

/*PTI Table*/
SEL TIMECODE_RANGE, TBN, BUOYID, AVG_TEMPERATURE, RANK() OVER (PARTITION BY BUOYID ORDER BY AVG_TEMPERATURE) FROM ( 
    SELECT $TD_TIMECODE_RANGE as TIMECODE_RANGE, $TD_GROUP_BY_TIME as TBN, BUOYID, AVG(TEMPERATURE) AS AVG_TEMPERATURE 
    FROM OCEAN_BUOYS 
    WHERE TD_TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00'
    GROUP BY TIME (MINUTES(10) AND BUOYID) 
) AS NESTED_AVG_TABLE 
ORDER BY 3,5;

/*Non-PTI Table*/
SEL TIMECODE_RANGE, TBN, BUOYID, AVG_TEMPERATURE, RANK() OVER (PARTITION BY BUOYID ORDER BY AVG_TEMPERATURE) FROM ( 
    SELECT $TD_TIMECODE_RANGE as TIMECODE_RANGE, $TD_GROUP_BY_TIME as TBN, BUOYID, AVG(TEMPERATURE) AS AVG_TEMPERATURE 
    FROM OCEAN_BUOYS_NONPTI
    WHERE TIMECODE BETWEEN TIMESTAMP '2014-01-06 08:00:00' AND TIMESTAMP '2014-01-06 10:30:00'
    GROUP BY TIME (MINUTES(10) AND BUOYID) 
    USING TIMECODE(TIMECODE)
) AS NESTED_AVG_TABLE 
ORDER BY 3,5;

Result:

The results of both queries are identical so only one result set is shown.
TIMECODE_RANGE TBN BUOYID AVG (TEMPERATURE) RANK
('2014-01-06 08:00:00.000000+00:00', '2014-01-06 08:10:00.000000+00:00') 1 0 54 1
('2014-01-06 08:10:00.000000+00:00', '2014-01-06 08:20:00.000000+00:00') 2 0 55 2
('2014-01-06 09:00:00.000000+00:00', '2014-01-06 09:10:00.000000+00:00') 7 1 74 1
('2014-01-06 10:10:00.000000+00:00', '2014-01-06 10:20:00.000000+00:00') 13 44 43 1
('2014-01-06 10:00:00.000000+00:00', '2014-01-06 10:10:00.000000+00:00') 13 44 50 2