使用SQL获取排名，如何处理并列排名？看似很简单，实则另有门道！

kk的DBA随笔 2024-12-03

192

假设有一个 students 表，包含学生的 student_id（学生ID），student_name（学生姓名），和 score（学生成绩）。我们想按成绩计算班级排名。

SELECT 
    student_id,
    student_name,
    score,
    RANK() OVER (ORDER BY score DESC) AS class_rank
FROM 
    students;

这就是窗口函数在实际应用中作用的小小展现。

一，简介

窗口函数也叫分析函数，也叫OLAP函数，通过 partition by 分组，这里的窗口表示范围，，可以不指定 PARATITION BY, 会将这个表当成一个大窗口。

二，应用场景

（1）用于分区排序

（2）动态 Group By

（3）Top N

（4）累计计算

（5）层次查询

三，窗口函数的种类

窗口函数大体可以分为以下两种：

1、能够作为窗口函数的聚合函数（ SUM 、 AVG 、 COUNT 、 MAX 、 MIN ）。

2、RANK 、 DENSE _ RANK 、 ROW _ NUMBER 等专用窗口函数。

专用窗口函数：

（1）RANK 函数

RANK 函数
计算排序时，如果存在相同位次的记录，则会跳过之后的位次，并且占位。
比如：有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……

DENSE_RANK 函数
同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
比如：有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……

记得有一道面试图是排序成绩，会有人并列并列名次，这个场景就需要用到 DENSE_RANK 函数。

ROW_NUMBER 函数
赋予唯一的连续位次，不会出现并列名次。
比如：有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

四，窗口函数语法

select ename,job,sal,    rank() over (PARTITION BY job
                 ORDER BY  sal) as rankin
from emp;

其中，可以把

    rank() over (PARTITION BY job                 ORDER BY  sal)

一块看，然后起了一个别名。

PARTITION BY 能够设定分组和排序的对象范围。

为了按照工作进行分组和排序，指定了 job。ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照工资的升序进行排列，我们指定了 sal 。

五，使用范围：只能在 SELECT 子句当中

六，作为窗口函数使用的聚合函数

（1）进行累积结果

如：

select ename,sal,	SUM(sal) over (ORDER BY ename) as current_sum
from emp;

使用 SUM 函数时，并不像 RANK 或者 ROW _ NUMBER 那样括号中的内容为空，需要在括号内指定作为汇总对象的列。

七，指定框架（汇总范围）

select name,price,avg (price) over (order by name rows 2 preceding) as moving_avg from product;

这里我们使用了 ROWS （“行”）和 PRECEDING （“之前”）两个关键字，将框架指定为 “截止到之前 ~ 行”，因此 “ ROWS 2 PRECEDING ” 就是将框架指定为 “截止到之前 2 行”，也就是将作为汇总对象的记录限定为如下的 “最靠近的 3 行”。

最靠近的 3 行 = 自身（当前记录）+ 之前第 1 行的记录 + 之前第 2 行的记录

八，计算移动平均

由于框架是根据当前记录来确定的，因此和固定的窗口不同，其范围会随着当前记录的变化而变化。这样的统计方法称为移动平均（moving average）。由于这种方法在希望实时把握 “最近状态” 时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。

使用关键字 FOLLOWING （“之后”）替换 PRECEDING ，就可以指定 “截止到之后 ~ 行” 作为框架了。

如：计算移动平均—同时指定前后行


select name,price,avg (price) over (order by name rows between 1 preceding and 1 following) as moving_avg from product;

九，两个 order by

OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。在 SELECT 语句的最后，使用 ORDER BY 子句进行指定按照 ranking 列进行排列，结果才会顺序显示，但是如果使用了，会打乱原本窗口函数出来的显示结果。

所以，一般来说，在窗口函数里面用了 order by，外面就不要再用 order by 了。

十，实操

1. 建表语句

create table product_sale_all(year CHAR(25) NOT NULL, -- 年份字段，字符类型CHAR
product_name VARCHAR(100) NOT NULL, -- 产品名称，字符类型VARCHAR
product_category VARCHAR(32) NOT NULL, --产品所属类别，字符类型VARCHAR
sale_num INT -- 产品销售额总和，整数类型INT
);