Given a sort expression list, this Ordered Analytical function derives a new column which assumes a value between 0 and 1 indicating the rank of the rows as a percentage of rows when sorted by the sort expression list. The formula used for PERCENT_RANK is (R - 1) / (N - 1) where R is the rank of the row and N is the number of rows overall or in the partition.
As with the RANK function, when the column or expression has the same value for multiple rows (say M rows), they are all assigned the same percent rank, while the following M-1 percent rank values are not assigned. When one or more Partition Columns are specified, the percent ranks are computed separately over the rows in each partition. Note from the formula used for PERCENT_RANK that if there is only one row to be ranked in the table or partition, division by zero will result and give a numeric overflow error. Rows options are not available with the Percent Rank function.
A Teradata Warehouse Miner enhancement to the Percent 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 Percent Rank function into a variable, the following tree element is created.
Sort expressions can be built up in the Sort Expressions folder, and Partition Columns can be built up in that folder. The enhancement to the Percent 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 Percent Rank, or highlight it and click Properties.
The default is to Include null values in the analysis, but that can be disabled here.