CUME_DIST

声明

CUME_DIST() OVER ([query_partition_clause] order_by_clause)

说明

该函数用于计算行在分组（分区）中的累积分布值，返回值为大于 0 小于等于 1 的值。它通过计算排序后当前行的位置（小于等于该数据的行数）与分区中所有行的总数的比率来确定行的累积分布值。

示例

1. 创建表 employees。

   CREATE TABLE employees(last_name CHAR(10), salary DECIMAL, job_id CHAR(32));

2. 向表 employees 中插入测试数据。

   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. 按 job_id 分组并在薪水升序排列的情况下，每行数据在窗口内的排序列上的占比。

   SELECT last_name, CUME_DIST() OVER(PARTITION BY job_id ORDER BY salary) CUME_DIST FROM employees;

   返回结果如下：

   +-----------+-----------+
   | last_name | CUME_DIST |
   +-----------+-----------+
   | TOM       |       0.5 |
   | IRIS      |       0.5 |
   | MIKE      |      0.75 |
   | LILY      |         1 |
   | JIM       |         1 |
   +-----------+-----------+
   5 rows in set (0.002 sec)