Declaration
CUME_DIST() OVER ([query_partition_clause] order_by_clause)
Description
This function calculates the cumulative distribution value of a row within a group (partition), returning a value greater than 0 and less than or equal to 1. It determines the cumulative distribution value by calculating the ratio of the row's position in the sorted dataset (the number of rows less than or equal to the current value) to the total number of rows in the partition.
Examples
Create the
employeestable.CREATE TABLE employees(last_name CHAR(10), salary DECIMAL, job_id CHAR(32));Insert test data into the
employeestable.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');The proportion of each row's value in the sorting column within the window, grouped by
job_idand sorted in 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