Syntax
CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)
Purpose
CUME_DIST() calculates the cumulative distribution of a value. The return value is greater than 0 but less than or equal to 1. As an analytic function, CUME_DIST() calculates the proportion of data that is smaller than the value of a specific column of the current row in ascending order.
Examples
Given that the data is grouped by job_id and sorted by salary in ascending order, the function returns the proportion of each row of data on the sorting column of the window.
obclient> CREATE TABLE employees(last_name CHAR(10), salary DECIMAL, job_id CHAR(32));
INSERT INTO employees VALUES('JIM', 2000, 'CLEANER');
INSERT INTO employees VALUES('MIKE', 12000, 'ENGINEERING');
INSERT INTO employees VALUES('LILY', 13000, 'ENGINEERING');
INSERT INTO employees VALUES('IRIS', 11000, 'ENGINEERING');
INSERT INTO employees VALUES('TOM', 11000, 'ENGINEERING');
COMMIT;
Query OK, 1 row affected
obclient> SELECT last_name, CUME_DIST() OVER(PARTITION BY job_id ORDER BY salary) cume_dist FROM employees;
+-----------+----------------------------------+
| last_name | cume_dist |
+-----------+----------------------------------+
| JIM | 1.000000000000000000000000000000 |
| TOM | 0.500000000000000000000000000000 |
| IRIS | 0.500000000000000000000000000000 |
| MIKE | 0.750000000000000000000000000000 |
| LILY | 1.000000000000000000000000000000 |
+-----------+----------------------------------+
5 rows in set