Rank - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.5
Published
February 2018
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
qhj1503087326201.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

Given a sort expression list, this Ordered Analytical function derives a new column indicating the rank of the rows when sorted by the specified sort expression list. When the column or expression has the same value for multiple rows (say M rows), they are all assigned the same rank, while the following M-1 rank values are not assigned. For example, column values 3,3,3,2,1 could be assigned rank values of 1,1,1,4,5. When one or more Partition Columns are specified, the ranks are determined separately over the rows in each partition (the ranking process is reset for each new partition). Rows options are not available with the Rank function.

A Teradata Warehouse Miner enhancement to the Rank function is offered to optionally request that NULL values in any element of the sort expression list cause the row to be excluded in the ranking process. Support is also provided for the WITH TIES option of the RANK function (provided the version of Teradata in use is 14.10 or later). The following table lists possible values for this option.

Rank Values
With Ties Option Description
(none) When the WITH TIES option is not specified, the result is the same as if WITH TIES LOW was specified.
LOW Equal values are given the same rank as the first value is given. This is the default behavior. For example, the values 10-10-10-20-30 are ranked 1-1-1-4-5, skipping ranks 2 and 3.
HIGH Equal values are given the same rank as the last value would be given if each tie were given an incrementally increasing rank. For example, 10-10-10-20-30 are ranked 3-3-3-4-5, skipping 1,2.
AVG Equal values are given the average of the ranks that would be given if each tie were given an incrementally increasing rank, returning a decimal rather than an integer value. For example, 10-10-10-20-30 are ranked 2.0-2.0-2.0-3.0-4.0 because 2.0 is the average of 1, 2 and 3.
DENSE Equal values are given the same rank and the next value is given the next rank, so that ranks are not skipped. Using this option is equivalent to using the DENSE RANK function. For example, 10-10-10-20-30 are ranked 1-1-1-2-3.

When dragging a Rank function into a variable, the following tree element is created.

Variable Creation > Input > Variables: SQL Elements pane - Logical > Rank

Sort expressions can be built up in the Sort Expressions folder, Partition Columns can be built up in that folder. The WITH TIES option and the enhancement to the Rank function to optionally request that NULL values in any element of the sort expression list cause the row to be excluded in the ranking process is enabled through the Properties panel. Double-click on Rank, or highlight it and click Properties.

Variable Creation > Input > Variables: SQL Elements pane - Logical > Rank Properties

The default is to Include null values in the analysis, but that can be disabled here.
The WITH TIES option is only visible if Teradata 14.10 or later is in use.