Dense Rank - Teradata Warehouse Miner

Teradata® Warehouse Miner™ User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.6
Published
November 2018
Language
English (United States)
Last Update
2018-12-07
dita:mapPath
gxn1538171534877.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, they are all assigned the same rank, while the rank value of the next row is that rank plus 1.

For example, column values 10,10,10,50,70 would be assigned rank values of 1,1,1,2,3. (as opposed to 1,1,1,4,5 with the Rank function). 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 Dense Rank function.

A Teradata Warehouse Miner enhancement to the Dense 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.

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

Variable Creation > Input > Variables: SQL Elements pane - Ordered Analytical Functions > Dense Rank

Sort expressions can be built up in the Sort Expressions folder, Partition Columns can be built up in that folder. The enhancement to the Dense 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 Dense Rank, or highlight it and click Properties to set the properties for this function.

Variable Creation > Input > Variables: SQL Elements pane - Ordered Analytical Functions > Dense Rank Properties

The default is to Include NULL values in the analysis, but that can be disabled here.