CUME_DIST

Syntax

CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)

Purpose

CUME_DIST() calculates the cumulative distribution of a row in a group (partition). Specifically, CUME_DIST() calculates the percentage of rows with partition values less than or equal to the value in the current row to the total number of rows. The return value is greater than 0 but less than or equal to 1.

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. Partition the data by job_id, sort the data by salary in ascending order, and query the cumulative distribution of each row in the window.

   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