CUME_DIST

Syntax

CUME_DIST() OVER ([query_partition_clause] order_by_clause)

Purpose

This function calculates the cumulative distribution value of a row in a group (partition). It returns a value greater than 0 and less than or equal to 1. It determines the cumulative distribution value of a row by calculating the ratio of the position of the row (number of rows less than or equal to the data) in the sorted order 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 and sort the data in ascending order of salary. Calculate the ratio of each row's data to the sorted column in 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