ROW_NUMBER() OVER(partition by 分组列 order by 排序列)

多用于对数据的快速排序和分组，也可用于分页，简单的逻辑判断。

先创建一个测试用表和数据。

-- Create table
create table Test_Orders
(
  id                  NUMBER(19) not null,
  User_Id              VARCHAR2(50 CHAR),
  Total_Price          FLOAT,
  created_time        date
)
tablespace WLOA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 16K
    next 8K
    minextents 1
    maxextents unlimited
  )
nologging;
---------------------
declare
duid integer;
otime date;
begin
	select trunc(dbms_random.value(0,100)) into duid from dual;
	insert into Test_Orders (ID,User_Id,Total_Price,created_time) VALUES (duid,'1', 111,to_date('2017-01-01','yyyy-mm-dd'));
	commit;

	select trunc(dbms_random.value(0,100)) into duid from dual;
	select to_date('2017-01-01','yyyy-mm-dd') into otime from dual;
	insert into Test_Orders (ID,User_Id,Total_Price,created_time) VALUES (duid,'1', 112,otime);
	commit;

	select trunc(dbms_random.value(0,100)) into duid from dual;
	select to_date('2019-01-01','yyyy-mm-dd') into otime from dual;
	insert into Test_Orders (ID,User_Id,Total_Price,created_time) VALUES (duid,'3', 311, otime);
	commit;

	select trunc(dbms_random.value(0,100)) into duid from dual;
	select to_date('2019-01-02','yyyy-mm-dd') into otime from dual;
	insert into Test_Orders (ID,User_Id,Total_Price,created_time) VALUES (duid,'3', 312, otime);
	commit;

	select trunc(dbms_random.value(0,100)) into duid from dual;
	select to_date('2018-01-01','yyyy-mm-dd') into otime from dual;
	insert into Test_Orders (ID,User_Id,Total_Price,created_time) VALUES (duid,'2', 211, otime);
	commit;

	select trunc(dbms_random.value(0,100)) into duid from dual;
	select to_date('2018-01-02','yyyy-mm-dd') into otime from dual;
	insert into Test_Orders (ID,User_Id,Total_Price,created_time) VALUES (duid,'2', 212, otime);
	commit;

	select trunc(dbms_random.value(0,100)) into duid from dual;
	select to_date('2017-01-03','yyyy-mm-dd') into otime from dual;
	insert into Test_Orders (ID,User_Id,Total_Price,created_time) VALUES (duid,'1', 113, otime);
	commit;

	select trunc(dbms_random.value(0,100)) into duid from dual;
	select to_date('2018-01-03','yyyy-mm-dd') into otime from dual;
	insert into Test_Orders (ID,User_Id,Total_Price,created_time) VALUES (duid,'2', 213, otime);
	commit;

	select trunc(dbms_random.value(0,100)) into duid from dual;
	select to_date('2019-01-03','yyyy-mm-dd') into otime from dual;
	insert into Test_Orders (ID,User_Id,Total_Price,created_time) VALUES (duid,'3', 313, otime);
	commit;
end;

Test_orders表的数据

1、使用row_number()函数对订单进行编号，按照创建时间倒序。（此需求多用于分页）

select Id,User_Id,Total_Price,created_time,
ROW_NUMBER() over (order by created_time desc) as rowIndex 
from Test_Orders

2、分页场景：每页3条数据，取第2页

with
baseDate
as
(
    select Id,User_Id,Total_Price,created_time,
	ROW_NUMBER() over (order by created_time desc) as rowIndex from Test_Orders
)
select * from baseDate where rowIndex>3 and rowIndex<7

yongcheng.min

 

3、所有订单按照用户进行分组，并按照用户下的订单的金额倒序排列。

select Id,User_Id,created_time,
ROW_NUMBER() over(partition by User_Id order by Total_Price desc) as rowIndex 
from Test_Orders

yongcheng.min

 

4、筛选出用户第一次下的订单。

思路：利用rowIndex来判断订单是用户第几次下单；

with
baseDate
as
(
    select Id,User_Id,Total_Price,created_time,
	ROW_NUMBER() over (partition by User_Id order by created_time) as rowIndex 
	from Test_Orders
)
select * from baseDate where rowIndex=1

yongcheng.min

 

5、筛选出用户在‘2017年1月1日之后的第一次下的订单。
--思路：在分组排序之前进行实践筛选；
--注意：在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行。

with
baseDate
as
(
    select Id,User_Id,Total_Price,created_time,
	ROW_NUMBER() over (partition by User_Id order by created_time) as rowIndex 
	from Test_Orders
    where to_char(created_time,'yyyy-MM-dd')>'2017-1-1'
)
select * from baseDate where rowIndex=1

ypngcheng.min

 

6、统计每一个用户所有的订单中金额最大，并统计该订单是用户第几次购买；
--思路：
--1）先按照用户户进行分组，然后按照用户下单的时间进行正序排列，并编号（rowIndex），生成临时表baseDate；
--2）再按照用户进行分组，然后按照用户下单的金额进行倒序排列，并编号（rowIndex），生成临时表basePrice；
--3）最后取basePrice中编号为1的数据，然后根据id到baseDate中去查，即可；

with
baseDate
as
(
    select Id,User_Id,Total_Price,created_time,
	ROW_NUMBER() over (partition by User_Id order by created_time) as rowIndex 
	from Test_Orders
),
basePrice
as
(
    select Id,User_Id,created_time,
	ROW_NUMBER() over(partition by User_Id order by Total_Price desc) as rowIndex 
	from Test_Orders
)
select * from baseDate 
where Id in (
    select Id from basePrice where rowIndex=1
)

yongcheng.min

 

7、只保留每个用户的最近的一次订单，其余的订单删掉。（常用于删除重复数据）

with
deleteDate
as
(
    select Id,User_Id,Total_Price,created_time,
	ROW_NUMBER() over (partition by User_Id order by created_time desc) as rowIndex 
	from Test_Orders
)
delete from deleteDate where (rowIndex > 1 OR rowIndex < 1)