The Oracle SQL aggregate and analytic function CUME_DIST calculates the cumulative distribution of a value in a group of values. The range of values returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value.
This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, makes the calculation, and returns NUMBER.
APPLIES TO: Oracle 9i, Oracle 10g, Oracle 11g
SYNTAX: CUME_DIST(value) OVER (partition_clause order_by_clause)
AGGREGATE EXAMPLE: The following example calculates the cumulative distribution of a hypothetical employee with a salary of $15,500 and commission rate of 5% among the employees in the sample table employees:
SELECT
CUME_DIST(15500, .05) WITHIN GROUP (ORDER BY emp.salary,
emp.commission_pct) cume_dist
FROM
employees emp;
| cume_dist |
|
0.972222222222222 |
ANALYTIC EXAMPLE: The following example calculates the salary percentile for each employee in the purchasing division. For example, 40% of clerks have salaries less than or equal to Himuro.
SELECT
emp.job_id,
emp.last_name,
emp.salary,
CUME_DIST() OVER (PARTITION BY emp.job_id ORDER BY emp.salary) AS cume_dist
FROM
employees emp
WHERE
upper(emp.job_id) LIKE ‘PU%’;
| job_id |
last_name |
salary |
cume_dist |
|
PU_CLERK |
Colmenares |
2500 |
0.2 |
|
PU_CLERK |
Himuro |
2600 |
0.4 |
|
PU_CLERK |
Tobias |
2800 |
0.6 |
|
PU_CLERK |
Baida |
2900 |
0.8 |
|
PU_CLERK |
Khoo |
3100 |
1 |
|
PU_MAN |
Raphaely |
11000 |
1 |
OVERVIEW: Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a ‘window’ and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
