15.00 - PERCENT_RANK - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

PERCENT_RANK

Purpose  

Returns the relative rank of rows for a value_expression.

Type

ANSI SQL:2011 window function.

Syntax  

where:

 

Syntax element …

Specifies …

OVER

how the values, grouped according to the PARTITION BY and RESET WHEN clauses and named by value_expression in the ORDER BY clause, are ranked.

PARTITION BY

in its column_reference the column, or columns, according to which ranking resets.

PARTITION BY is optional. If there are no PARTITION BY or RESET WHEN clauses, then the entire result set, specified by the ORDER BY clause, constitutes a single group or partition.

PARTITION BY clause is also called the window partition clause.

ORDER BY

in its value_expression the column, or columns, being ranked.

ASC

that the results are to be ordered in ascending sort order.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

that the results are to be ordered in descending sort order.

If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

NULLS FIRST

that NULL results are to be listed first.

NULLS LAST

that NULL results are to be listed last.

RESET WHEN

the group or partition over which the function operates, depending on the evaluation of the specified condition. If the condition evaluates to TRUE, a new dynamic partition is created inside the specified window partition.

RESET WHEN is optional. If there are no RESET WHEN or PARTITION BY clauses, then the entire result set constitutes a single partition.

If RESET WHEN is specified, then the ORDER BY clause must be specified also.

condition

a conditional expression used to determine conditional partitioning. The condition in the RESET WHEN clause is equivalent in scope to the condition in a QUALIFY clause with the additional constraint that nested ordered analytical functions cannot specify a RESET WHEN clause. In addition, you cannot specify SELECT as a nested subquery within the condition.

The condition is applied to the rows in all designated window partitions to create sub-partitions within the particular window partitions.

For more information, see “RESET WHEN Condition Rules” on page 980and the “QUALIFY Clause” in SQL Data Manipulation Language.

ANSI Compliance

This is ANSI SQL:2011 compliant.

The RESET WHEN clause is a Teradata extension to the ANSI SQL standard.

Computation

The formula for PERCENT_RANK is:

where:

 

This variable …

Represents the …

RK

rank of the row

NR

number of rows in the window partition

The assigned rank of a row is defined as 1 (one) plus the number of rows that precede the row and are not peers of it.

PERCENT_RANK is expressed as an approximate numeric ratio between 0.0 and 1.0.

 

PERCENT_RANK has this value …

FOR the result row assigned this rank …

0.0

1.

1.0

highest in the result.

Result Type and Attributes

For PERCENT_RANK() OVER (PARTITION BY x ORDER BY y direction), the data type, format, and title are as follows:

 

Data Type

Format

Title

REAL

the default format for DECIMAL(7,6).

Percent_Rank(y direction)

For an explanation of the formatting characters in the format, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

Example  

Determine the relative rank, called the percent_rank, of Christmas sales.

The following query:

   SELECT sales_amt, 
   PERCENT_RANK() OVER (ORDER BY sales_amt)
   FROM xsales;

might return the following results. Note that the relative rank is returned in ascending order, the default when no sort order is specified and that the currency is not reported explicitly.

 

sales_amt

Percent_Rank

100.00

0.000000

120.00

0.125000

130.00

0.250000

140.00

0.375000

143.00

0.500000

147.00

0.625000

150.00

0.750000

155.00

0.875000

160.00

1.000000

Example  

Determine the rank and the relative rank of Christmas sales.

   SELECT sales_amt,
   RANK() OVER (ORDER BY sales_amt),
   PERCENT_RANK () OVER (ORDER BY sales_amt)
   FROM xsales;
 

sales_amt

Rank

Percent_Rank

100.00

1

0.000000

120.00

2

0.125000

130.00

3

0.250000

140.00

4

0.375000

143.00

5

0.500000

147.00

6

0.625000

150.00

7

0.750000

155.00

8

0.875000

160.00

9

1.000000

Example  

The following SQL statement illustrates the difference between PERCENT_RANK and cumulative distribution.

SELECT sales_amt,
 PERCENT_RANK() OVER (ORDER BY sales_amt),
 CUME_DIST() OVER (ORDER BY sales_amt)
 FROM xsales;
 

sales_amt

PERCENT_Rank

CUME_DIST

100.

.000000

0.125000

120.

.142857

0.250000

130

.285714

.375000

140.

.428571

.500000

147.

.571429

.625000

150.

.714286

.750000

155.

.857143

.875000

160.

1.000000

1.000000