`cume_dist` analytic window function

Applies to: check marked yes Databricks SQL Databricks Runtime

Returns the position of a value relative to all values in the partition.

Syntax

cume_dist() over_clause

Arguments

over_clause: The clause describing the windowing. See: Window functions.

Returns

A DOUBLE.

The OVER clause of the window function must include an ORDER BY clause. If the order is not unique the duplicates share the same relative later position. cume_dist() over(order by expr) is similar, but not identical to rank() over(order by position) / count(*) since rank ranking window function produces the earliest absolute order.

Examples

> SELECT a, b, cume_dist() OVER (PARTITION BY a ORDER BY b)
    FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
 A1 1   0.6666666666666666
 A1 1   0.6666666666666666
 A1 2   1.0
 A2 3   1.0