Oracle / PLSQL: CUME_DIST Function
This Oracle tutorial explains how to use the Oracle/PLSQL CUME_DIST function with syntax and examples.
Description
The Oracle/PLSQL CUME_DIST function returns the cumulative distribution of a value in a group of values. The CUME_DIST function will return a value that is >0 and <=1.
The CUME_DIST function can be used two ways - as an Aggregate function or as an Analytic function.
CUME_DIST Function Syntax #1 - Used as an Aggregate Function
As an Aggregate function, the CUME_DIST function returns the relative position of a row within a group of rows.
The syntax for the CUME_DIST function when used as an Aggregate function is:
CUME_DIST( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )
Parameters or Arguments
expression1 .. expression_n can be one or more expressions which identify a unique row in the group.
Returns
The CUME_DIST function returns a numeric value.
Note
- There must be the same number of expressions in the first expression list as there is in the ORDER BY clause.
- The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.
Applies To
The CUME_DIST function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
Example (as an Aggregate Function)
Let's look at some Oracle CUME_DIST function examples and explore how to use the CUME_DIST function in Oracle/PLSQL.
For example:
select CUME_DIST(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from employees;
The SQL statement above would return the cumulative distribution of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.
CUME_DIST Function Syntax #2 - Used as an Analytic Function
As an Analytic function, the CUME_DIST function returns the relative position of a value within a group of values.
The syntax for the CUME_DIST function when used as an Analytic function is:
CUME_DIST() OVER ( [ query_partition_clause] ORDER BY clause )
Returns
The CUME_DIST function returns a numeric value.
Applies To
The CUME_DIST function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example (as an Analytic Function)
Let's look at some Oracle CUME_DIST function examples and explore how to use the CUME_DIST function in Oracle/PLSQL.
For example:
select employee_name, salary, CUME_DIST() OVER (PARTITION BY department ORDER BY salary) from employees where department = 'Marketing';
The SQL statement above would return the salary percentile for each employee in the Marketing department.
Advertisements