ORACLE一般排名、百分比排名等排序函数详解

太过爱你忘了你带给我的痛 2024-03-16 20:56 54阅读 0赞

#### ORACLE一般排名、百分比排名等排序函数 ####

*  一般排名
 *   *  RANK
     *  DENSE\_RANK
     *  ROW\_NUMBER
 *  百分比排名
 *   *  PERCENT\_RANK
     *  CUME\_DIST
     *  区别

## 一般排名 ##

### RANK ###

用于计算一组值中某个值的排名。当有多行具有相同的值时，RANK 函数会返回相同的排名。它会将并列排名的行数加到并列排名上以计算下一个排名。因此，排名可能不是连续的数字。

SELECT employee_name, salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees;

上文中，我们使用 RANK 函数计算 employees 表中每个员工的工资排名。我们使用 ORDER BY 子句按工资降序排列，并将排名结果作为 salary\_rank 列返回。  
RANK 函数可以用作聚合函数或分析函数。作为聚合函数，RANK 函数返回一组行中某一行的排名。作为分析函数，RANK 函数返回查询中每一行相对于其他行的排名。

### DENSE\_RANK ###

用于计算一组值中某个值的排名。当有多行具有相同的值时，DENSE\_RANK 函数会返回相同的排名。与 RANK 函数不同，DENSE\_RANK 函数不会跳过排名，即使有并列排名的行。

SELECT employee_name, salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees;

在这个示例中，我们使用 DENSE\_RANK 函数计算 employees 表中每个员工的工资排名。我们使用 ORDER BY 子句按工资降序排列，并将排名结果作为 salary\_rank 列返回。  
RANK 和 DENSE\_RANK 都是 Oracle 中的分析函数，它们用于计算一组值中某个值的排名。它们之间的主要区别在于如何处理并列排名。

RANK 函数会为并列排名的行分配相同的排名，然后跳过下一个排名。例如，如果有 3 个项目排名为 2，则下一个排名将为 5。而 DENSE\_RANK 函数则会为您的有序分区内的排名提供连续的排名。并列排名的行数不会被跳过

### ROW\_NUMBER ###

用于为结果集中的每一行分配一个唯一的数字。与 RANK 和 DENSE\_RANK 函数不同，ROW\_NUMBER 函数不会为并列排名的行返回相同的数字。

SELECT employee_name, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
    FROM employees;

在这个示例中，我们使用 ROW\_NUMBER 函数为 employees 表中每个员工分配一个唯一的数字。我们使用 ORDER BY 子句按工资降序排列，并将结果作为 row\_num 列返回。

## 百分比排名 ##

### PERCENT\_RANK ###

PERCENT\_RANK 是一个分析函数，它以0到1之间的分数形式返回某个值在数据分区中的排名。它与 CUME\_DIST 函数类似，但是第一行的 PERCENT\_RANK 值为0。返回值为 NUMBER 类型。  
这是一个例子，它计算了2023年每个销售人员的销售百分位数1：

SELECT salesman_id, sales,
    ROUND(PERCENT_RANK() OVER(ORDER BY sales DESC) * 100, 2) || '%' percent_rank
    FROM salesman_performance
    WHERE YEAR = 2023;

这个例子中，PERCENT\_RANK 函数用于计算每个销售人员的销售额在所有销售人员中的排名。ORDER BY 子句按销售额降序排列，ROUND 函数将结果保留两位小数。  
如果多个数值相同，那么它们的 PERCENT\_RANK 值也相同。  
对于序列 1 2 2 3 3 4 4 5，每个值对应的 PERCENT\_RANK 值如下：

值 PERCENT\_RANK  
1 0  
2 0.14285714285714285  
2 0.14285714285714285  
3 0.42857142857142855  
3 0.42857142857142855  
4 0.7142857142857143  
4 0.7142857142857143  
5 1  
PERCENT\_RANK 的计算公式为：(排名 - 1) / (总数 - 1)。其中，排名指的是值在序列中的排名（从1开始），总数指的是序列中元素的总数。  
在序列 1 2 2 3 3 4 4 5 中，值 3 的排名为 4，因为它是第四个不同的值。序列中元素的总数为 8。因此，值 3 对应的 PERCENT\_RANK 值为 (4 - 1) / (8 - 1) = 0.42857142857142855。

### CUME\_DIST ###

CUME\_DIST 是一个分析函数，它计算给定值在其分区中的累积分布。它返回的是小于或等于当前值的行数与总行数的比值。CUME\_DIST 的返回值在 0 到 1 之间，包括 0 和 1。

下面是一个例子，它计算了每个员工薪水在其部门中的累积分布：

SELECT employee_name, department_id, salary,
           CUME_DIST() OVER (PARTITION BY department_id 
           				     ORDER BY salary) cume_dist
    FROM employees;

这个例子中，CUME\_DIST 函数用于计算每个员工的薪水在其部门中的累积分布。PARTITION BY 子句按部门分区，ORDER BY 子句按薪水升序排列。  
对于序列 1 2 2 3 3 4 4 5，每个值对应的 CUME\_DIST 值如下：

值 CUME\_DIST  
1 0.125  
2 0.375  
2 0.375  
3 0.625  
3 0.625  
4 0.875  
4 0.875  
5 1  
CUME\_DIST 的计算公式为：小于或等于当前值的行数 / 总行数。

### 区别 ###

CUME\_DIST 和 PERCENT\_RANK 都是分析函数，可以用来计算值在其分区中的相对位置。它们的主要区别在于计算公式不同。

CUME\_DIST 的计算公式为：小于或等于当前值的行数 / 总行数。  
PERCENT\_RANK 的计算公式为：(排名 - 1) / (总数 - 1)。其中，排名指的是值在序列中的排名（从1开始），总数指的是序列中元素的总数。  
这两个函数都返回 0 到 1 之间的值，包括 0 和 1。但是它们的计算方式不同，因此对于同一个序列，它们返回的结果也可能不同。  
对于序列 1 2 2 3 3.5 4 4 5，每个值对应的 CUME\_DIST 值如下：

值 CUME\_DIST  
1 0.125  
2 0.375  
2 0.375  
3 0.5  
3.5 0.625  
4 0.875  
4 0.875  
5 1

对于序列 1 2 2 3 3.5 4 4 5，每个值对应的 PERCENT\_RANK 值如下：  
值 PERCENT\_RANK  
1 0  
2 0.14285714285714285  
2 0.14285714285714285  
3 0.42857142857142855  
3.5 0.5714285714285714  
4 0.7142857142857143  
4 0.7142857142857143  
5 1