四种排序开窗函数

发表于 更新于 分类于 阅读次数：

参考
https://zhuanlan.zhihu.com/p/484290987
SqlServer四种排序：ROW_NUMBER()/RANK()/DENSE_RANK()/ntile() over()

总结：

使用方法 区别
row_number() over(partition by col1 order by col2) 若有并列的排名，序号递增。序号从1到n连续。
e.g., 两个人都排第3，则排序为：1,2,3,4,...
rank() over(partition by col1 order by col2) 若有并列的排名，会占用下一名次的。序号从1到n不连续。
e.g., 两个人都排第3，则排序为：1,2,3,3,5,...
dense_rank() over(partition by col1 order by col2) 若有并列的排名，不会占用下一名次的。序号从1到n连续。
e.g., 两个人都排第3，则排序为：1,2,3,3,4,...
ntile(n) over(partition by col1 order by col2) 将每个分区内排序后的结果均分成N个桶，排序对应的数字为桶号。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1
1 ROW_NUMBER()

例：table1: 学生考试成绩表

name score date
Alice 90 2023-06-01
Alice 87 2023-06-08
Bob 77 2023-06-01

想要取每个学生最近一次的考试成绩：

1
2
3
4
5
6
7
8
9
select name, score, `date`
from 
(
    select 
        * ,
        row_number() over(partition by name order by `date` desc) as rn 
     from table1
)t1 
where rn=1

结果如下：

name score date
Alice 87 2023-06-08
Bob 77 2023-06-01

中间结果：

name score date rn
Alice 87 2023-06-08 1
Alice 90 2023-06-01 2
Bob 77 2023-06-01 1

当排序出现重复值时，结果如下：

name score date rn
Alice 87 2023-06-08 1
Alice 90 2023-06-01 2
Bob 80 2023-07-01 1
Bob 77 2023-06-01 2
Bob 71 2023-06-01 3
1.1 结合 case when 使用

另外，排序规则还可结合case when 使用：

参考 再看case when 在row_number中的使用

例：用户群组有不同的状态：status=1 代表近期有过交易，status=2 代表已开户但近期未交易，status=3 代表未开户。 规则： 1、首先根据status由小到大排序，即近期有过交易的优先，未开户的排最后。 2、status=1时，根据被联系次数由高到低排；status=2时，先根据被联系次数由高到低排，相等的情况下根据开户时间由近到远排；status=3时，根据被联系次数由低到高排。

1
row_number() over(partition by group_id order by status, case when status=1 or status=2 then call_count end desc, case when status=2 then open_account_time end desc, case when status=3 then call_count end) as row_rank
2 RANK()

与ROW_NUMBER() 的区别主要在于对于重复值的处理

例：

1
2
3
4
5
SELECT 
    a.userName
    ,a.userid
    ,RANK() OVER(ORDER by a.userName) AS RN 
FROM dbo.t_user a

image-20210511113347415

这时排序的应该为3的值，因为出现重复数据，所以也变成了2，但是最后的行数和最后编号的值是一样的。

3 DENSE RANK()

例：

1
2
3
4
5
SELECT 
    a.userName
    ,a.userid
    ,DENSE_RANK() over(ORDER by a.userName) AS RN 
FROM dbo.t_user a

image-20210511113527128

保持了重复的数据RN值相同，但值得注意的是第4行数据变成了3，也就是说行数和最后的编号是发生了变化的。

4 NTILE()

SQL Server Ntile()函数 - SQL Server教程™

NTILE() 括号里面是几，就会分成几组。

例如5行数据， 分成2组: NTILE(2) -> 前面3个，后面2个，默认把多余的行分给前面； 分成3组：NTILE(3) -> 第一组2个，第二组2个，第三组1个

例：

1
2
3
4
5
SELECT 
    a.userName
    ,a.userid
    ,NTILE(2) over(ORDER by a.userName) AS RN 
FROM dbo.t_user a

image-20210511113708257

1
2
3
4
5
SELECT 
    a.userName
    ,a.userid
    ,NTILE(3) over(ORDER by a.userName) AS RN 
FROM dbo.t_user a

image-20210511113739831

image-20210726163619866

4.1 rand()

另：还可通过rand()，结合ntile() 实现随机均分 例：一共1600个用户，想随机均分为4组，每组400个

1
2
3
4
select 
    login_id
    ,ntile(4) over(order by rand()) as group_id
from ...
5 其他类似的开窗函数
5.1 sum() over()

进行累加

1
2
3
4
5
6
select 
    login_id
    ,amt
    ,create_time
    ,sum(amt) over(partition by login_id order by create_time) as cumsum
from xxx

结果如下：

login_id amt create_time cumsum
1001 100 2023-06-01 100
1001 50 2023-06-07 150
1002 24 2023-06-02 24
5.2 max() over()

max(A) over(partition by B order by C)

根据B分组，组内通过C排序，取A最大值作为这个字段的值。

1
2
3
4
select 
    *, 
    max(age) over(partition by class order by age desc) as max_age
from xxx
class name age max_age
class1 bob 30 30
class1 alice 20 30
class2 cathy 25 25