15.00 - CUME_DIST - 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)
Last Update
2018-09-24

CUME_DIST

Purpose  

Calculates the cumulative distribution of a value in a group of values.

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 980 and the “QUALIFY Clause” in SQL Data Manipulation Language.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Using CUME_DIST

CUME_DIST is similar to PERCENT_RANK. Unlike PERCENT_RANK, which considers the RANK value in the presence of ties, CUME_DIST uses the highest tied rank, that is, the position of the last tied value when there are peers. CUME_DIST is the ratio of that position in the partition (RANK-HIGH/NUM ROWS).

Results

The range of values returned by CUME_DIST is >0 to <=1.

Example  

The following SELECT statement:

SELECT lname, serviceyrs,
 CUME_DIST()OVER(ORDER BY serviceyrs)
 FROM schooltbl
 GROUP BY 1,2;

returns the cumulative distribution by service years for teachers listed in schooltbl.

 

lname

serviceyrs

CUME_DIST

Adams

10

0.333333

Peters

10

0.333333

Murray

10

0.333333

Rogers

15

0.444333

Franklin

16

0.555333

Smith

20

0.888889

Ford

20

0.888889

Derby

20

0.888889

Baker

20

1.000000