分组排序：ROW_NUMBER() OVER()函数用法详解

1、场景介绍

在电商中，经常出现下面的场景：

统计出每一个各户的所有订单并按每一个客户下的订单的金额降序排序，同时给每一个客户的订单进行编号。这样就知道每个客户下几单了，最高金额是多少。

这种情况下需要使用SQL的分组排序的功能：row_number() over() 函数，其语法格式：

row_number() over(partition by 分组列 order by 排序列 desc)

需要注意的是：在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行。

2、SQL实战

drop table if exists `t_orders`;

create table t_orders(
       customerID varchar(10) not null,
       name varchar(10) null,
       address varchar(10) null,
       totalPrice int null
);

insert into t_orders(customerID,name,address,totalPrice) values(1,'Tom','China',8000);
insert into t_orders(customerID,name,address,totalPrice) values(1,'Tom','China',6500);
insert into t_orders(customerID,name,address,totalPrice) values(2,'Sam','China',13000);
insert into t_orders(customerID,name,address,totalPrice) values(2,'Sam','China',4500);
insert into t_orders(customerID,name,address,totalPrice) values(3,'David','China',3000);
insert into t_orders(customerID,name,address,totalPrice) values(3,'David','China',20000);
insert into t_orders(customerID,name,address,totalPrice) values(4,'Alex','China',30000);
insert into t_orders(customerID,name,address,totalPrice) values(5,'Alan','China',1800);

分组排序SQL语句：

select customerID,name,totalPrice,
row_number() over(partition by customerID  order by totalPrice desc) as `rank` 
from t_orders;

运行结果为：

然后可以找出每个顾客最大的订单：

select * from(select customerID,name,totalPrice,
row_number() over(partition by customerID  order by totalPrice) as `rank` from t_orders ) t 
where t.rank <2

运行结果为：

需要明白：over里面的分组及排序的执行晚于“where，group by，order by”的执行，如下所示：

select customerID,name,totalPrice,
row_number() over(partition by customerID  order by totalPrice desc) as `rank` 
from t_orders where totalPrice > 4000;

运行结果为：

3、不分组只排序

row_number() over() 函数多用于分组之后排序，其实可以不分组只排序，如下所示：

select customerID,name,totalPrice,
row_number() over(order by totalPrice desc) as `rank` 
from t_orders;

运行结果为：

4、小结

本文给大家介绍了非全局排序（分组排序）的场景以及ROW_NUMBER() OVER()函数的用法。欢迎大家学习！