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 ascendingly sorted by salary, the function returns the proportion of each row of data on the sorting column of the window:
obclient> CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
INSERT INTO EXPLOYEES VALUES('IRIS', 11000, 'ENGINEERING');
INSERT INTO EXPLOYEES 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 EXPLOYEES;
+-----------+----------------------------------+
| LAST_NAME | CUME_DIST |
+-----------+----------------------------------+
| JIM | 1.000000000000000000000000000000 |
| TOM | 0.500000000000000000000000000000 |
| IRIS | 0.500000000000000000000000000000 |
| MIKE | 0.750000000000000000000000000000 |
| lily | 1.000000000000000000000000000000 |
+-----------+----------------------------------+
5 rows in set