Oracle row_number() over( order by )的经典案例

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

2. 所有订单按照客户进行分组，并按照客户下的订单的金额倒序排列。

3. 筛选出客户第一次下的订单。

4. 筛选出客户在‘2011年1月1日之后的第一次下的订单。

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

6. 统计每一个客户所有的订单中金额最大，并统计该订单是客户第几次购买； 参考 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;

Oracle row_number() over( order by )的经典案例