Syntax
CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)
Purpose
You can call this function to calculate the 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 (0.00 sec)
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 (0.00 sec)