CUME_DIST

Syntax

CUME_DIST() OVER ([query_partition_clause] order_by_clause)

Purpose

This function calculates the cumulative distribution value of a row within a group (partition) and returns a value greater than 0 and less than or equal to 1. It calculates the cumulative distribution value of a row by dividing the number of rows that are less than or equal to the current row after sorting by 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. Calculate the cumulative distribution value of each row in the window based on the job_id column and the ascending order of salary.

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

   The return 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