CUME_DIST

Syntax

CUME_DIST() OVER ([query_partition_clause] order_by_clause)

Description

The CUME_DIST function calculates the cumulative distribution value of a row within a group (partition). It returns a value greater than 0 and less than or equal to 1. The function determines the cumulative distribution value by calculating the ratio of the current row's position in the sorted order (number of rows less than or equal to the current data) to the total number of rows in the partition.

Examples

1. Create a table named employees.

   CREATE TABLE employees(last_name CHAR(10), salary DECIMAL, job_id CHAR(32));

2. Insert test data into the employees table.

   INSERT INTO employees VALUES('TOM', 11000, 'ENGINEERING');
   INSERT INTO employees VALUES('IRIS', 11000, 'ENGINEERING');
   INSERT INTO employees VALUES('MIKE', 12000, 'ENGINEERING');
   INSERT INTO employees VALUES('LILY', 13000, 'ENGINEERING');
   INSERT INTO employees VALUES('JIM', 2000, 'CLEANER');

3. Group the data by job_id, sort the data in ascending order of salary, and calculate the cumulative distribution value of each row within the window.

   SELECT last_name, CUME_DIST() OVER(PARTITION BY job_id ORDER BY salary) CUME_DIST FROM employees;

   The returned result is as follows:

   +-----------+-----------+
   | last_name | CUME_DIST |
   +-----------+-----------+
   | TOM       |       0.5 |
   | IRIS      |       0.5 |
   | MIKE      |      0.75 |
   | LILY      |         1 |
   | JIM       |         1 |
   +-----------+-----------+
   5 rows in set (0.002 sec)