Name
CUME_DIST
Synopsis
Computes the relative rank of a hypothetical row within a group of rows, where that relative rank is computed as follows:
(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.
SQL2003 Syntax
In the following syntax, items in the
value_list
correspond by position to items
in the sort_list
. Therefore, both lists
must have the same number of expressions.
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]
Oracle
Oracle follows the SQL2003 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 Section 4.3.
DB2, 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) ---------- ----------------------------------------- ...
Get SQL in a Nutshell, 2nd 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.