Oracle row_number() over( order by )的经典案例
-
使用row_number()函数对订单进行编号，按照订单时间倒序。（此需求多用于分页）
-
所有订单按照客户进行分组，并按照客户下的订单的金额倒序排列。
-
筛选出客户第一次下的订单。
-
筛选出客户在‘2011年1月1日之后的第一次下的订单。
-
只保留每个客户的最近的一次订单，其余的订单删掉。（常用于删除重复数据）
-
统计每一个客户所有的订单中金额最大，并统计该订单是客户第几次购买； 参考 https://www.cnblogs.com/willingtolove/p/10623841.html#_label1 下方为 完整的代码实例 （纯手敲）
-- 参考 https://www.cnblogs.com/willingtolove/p/10623841.html#_label1
create table OrderInfo(
Id int PRIMARY KEY NOT NULL,
UserId varchar(50) NOT NULL,
TotalPrice float NOT NULL,
OrderTime date NOT NULL
);
select sysdate from dual;
select * from OrderInfo;
INSERT INTO OrderInfo
(id,UserId
,TotalPrice
,OrderTime)
VALUES
(1,'1', 111, to_date('2011-01-01 15:33:44','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO OrderInfo
(id,UserId
,TotalPrice
,OrderTime)
VALUES (2,'1', 112, to_date('2011-01-02 15:33:44','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO OrderInfo
(id,UserId
,TotalPrice
,OrderTime)
VALUES
(3,'3', 311, to_date('2013-01-01 15:33:44','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO OrderInfo
(id,UserId
,TotalPrice
,OrderTime)
VALUES
(4,'3', 312, to_date('2013-01-02 15:33:44','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO OrderInfo
(id,UserId
,TotalPrice
,OrderTime)
VALUES
(5,'2', 211, to_date('2012-01-01 15:33:44','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO OrderInfo
(id,UserId
,TotalPrice
,OrderTime)
VALUES
(6,'2', 212,to_date('2012-01-02 15:33:44','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO OrderInfo
(id,UserId
,TotalPrice
,OrderTime)
VALUES
(7,'1', 113, to_date('2011-01-03 15:33:44','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO OrderInfo
(id,UserId
,TotalPrice
,OrderTime)
VALUES
(8,'2', 213, to_date('2012-01-03 15:33:44','yyyy-mm-dd hh24:mi:ss'));
INSERT INTO OrderInfo
(id,UserId
,TotalPrice
,OrderTime)
VALUES
(9,'3', 313,to_date('2013-01-03 15:33:44','yyyy-mm-dd hh24:mi:ss'));
-- 1.使用row_number()函数对订单进行编号，按照订单时间倒序。（此需求多用于分页）
select O.*,row_number() over(order by O.ORDERTIME desc) as rowIndex from OrderInfo O;
-- 分页场景：每页三条数据 取第二页
with
baseDate
as
(
-- 按要求进行编号
select Id,UserId,TotalPrice,OrderTime,ROW_NUMBER() over (order by OrderTime desc) as rowIndex from OrderInfo
)
select * from baseDate where rowIndex>3 and rowIndex<7;
-- 2. 所有订单按照客户进行分组，并按照客户下的订单的金额倒序排列。
select O.*,row_number() over(partition by O.UserId order by O.TOTALPRICE desc)as rowIndex from OrderInfo O;
-- 3. 筛选出客户第一次下的订单。
with
baseDate
as
(
select O.* ,row_number() over(partition by O.USERID order by O.ORDERTIME )as rowIndex from OrderInfo O
)
select * from baseDate where rowIndex=1;
-- 4. 筛选出客户在‘2011年1月1日之后的第一次下的订单。
-- 注意：在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行。
with
baseDate
as
(
select O.*,row_number() over(partition by O.USERID order by O.ORDERTIME)as rowIndex from OrderInfo O
where O.ORDERTIME> to_date('2011-01-01 15:33:44','yyyy-mm-dd hh24:mi:ss')
)
select * from baseDate where rowIndex = 1;
-- 5. 只保留每个客户的最近的一次订单，其余的订单删掉。（常用于删除重复数据）
-- <> == !=
with
baseDate
as
(
select O.*,row_number() over(partition by O.USERID order by O.ORDERTIME desc) from OrderInfo O
)
delete from baseDate where rowIndex<>1;
-- 6. 统计每一个客户所有的订单中金额最大，并统计该订单是客户第几次购买；
with
baseDate
as
(
select O.*,row_number() over(partition by O.USERID order by O.ORDERTIME )rowIndex from OrderInfo O
),
basePrice
as
(
select O.*,row_number() over(partition by O.USERID order by O.TOTALPRICE desc) rowIndex from OrderInfo O
)
select * from baseDate where Id in( select Id from basePrice where rowIndex=1);
-- 删除表
drop table OrderInfo;