Name
CUME_DIST
Computes the relative rank of a hypothetical row within a group of rows, using the following equation:
(rows_preceding_hypothetical
+rows_peered_with_hypothetical
) /rows_in_group
Bear in mind that the rows_in_group
value includes the hypothetical row that you are proposing when you call the function.
ANSI SQL Standard Syntax
CUME_DIST(value_list
) WITHIN GROUP (ORDER BYsort_list
)value_list
::=expression
[,expression
...]sort_list
::=sort_item
[,sort_item
...]sort_item
::=expression
[ASC | DESC] [NULLS FIRST | NULLS LAST]
Items in the value_list
correspond by position to items in the sort_list
. Therefore, both lists must have the same number of expressions.
Oracle
Oracle follows the ANSI SQL syntax and implements the following analytic syntax:
CUME_DIST( ) OVER ([partioning
]ordering
)
For an explanation of the partioning
and order
clauses, see the section later in this chapter titled “ANSI SQL Window Functions.”
MySQL, PostgreSQL, and SQL Server
These platforms do not implement the CUME_DIST aggregate function.
Example
The following example determines the relative rank of the hypothetical new row (num=4, odd=1)
within each group of rows from test4, where groups are distinguished by the values in the odd column:
SELECT * FROM test4;
NUM ODD ---------- ---------- 0 0 1 1 2 0 3 1 3 1 4 0 5 1SELECT odd, CUME_DIST(4,1) WITHIN GROUP (ORDER BY num, odd)
FROM test4 GROUP BY odd;
ODD CUME_DIST(4,1)WITHINGROUP(ORDERBYNUM,ODD) ---------- ----------------------------------------- 0 1 ...
Get SQL in a Nutshell, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.