Oracle over(partition by）函数的用法

主要介绍over(partition by）的使用过程于group by 和partition by的区别

现有数据结构如下：

create table ABCS
(
  UPDATE_DATE           DATE not null,--日期
  EMPOLYEE_NAME         NVARCHAR2(80),--人员
  SALARY                       NUMBER --薪资
)

INSERT INTO ABCS VALUES (2020/7/1,    张三,    12);
INSERT INTO ABCS VALUES (2020/7/2,    张三,    11);
INSERT INTO ABCS VALUES (2020/7/2,    李四,    20);
INSERT INTO ABCS VALUES (2020/7/1,    张三,    20);
INSERT INTO ABCS VALUES (2020/7/1,    张三,    20);
INSERT INTO ABCS VALUES (2020/6/1,    张三,    12);
INSERT INTO ABCS VALUES (2020/6/1,    李四,    10);
INSERT INTO ABCS VALUES (2020/6/2,    张三,    11);
INSERT INTO ABCS VALUES (2020/6/2,    李四,    20);
INSERT INTO ABCS VALUES (2020/6/1,    张三,    20);

需求：取时间最大的一条记录显示

不用over（patition by）函数：

• 1.根据MAX()过滤出最大的时间取第一条。

select t.update_date,
       t.salary,
       t.empolyee_name  
  from abcs t 
  where t.update_date=(select max(t.update_date) from abcs t)  
    and rownum=1 

运行结果：能找出时间都是最大的所有数据，加上rownum=1就是选其中一条。

• 2.排序后取第一条。

select *
  from (select t.update_date, t.salary, t.empolyee_name
          from abcs t
         order by t.update_date desc)
 where rownum = 1

运行结果：与1相比建议用这条查一条数据的，数据量大的话

 

以上是只能显示一条数据的，下面来看数据量大，多条重复数据，根据分组排序取一条显示。

用over（patition by）函数：

• 3.row_number()  over(partition by 分组字段  order by 排序字段   排序方式) 

 
 SELECT *
   FROM (select t.update_date,
                t.salary,
                t.empolyee_name,
                row_number() over(partition by t.empolyee_name order by t.update_date desc) mm
           from abcs t)
  where mm = 1;

运行结果：可以看到包装在里面的SQL是根据时间排序，根据人员分组，然后选择mm=1排在第一位的数据

• 4.rank()  over(partition by 分组字段  order by 排序字段   排序方式) 

 SELECT *
   FROM (select t.update_date,
                t.salary,
                t.empolyee_name,
                rank() over(partition by t.empolyee_name order by t.update_date asc) mm
           from abcs t)
  where mm = 1;

运行结果：首先为了与3做一个区分，可以看到当排序的字段与分组的字段一致时，mm的值会相等，这就是rank()与row_number()不一样的地方，刚好这个排序在最末尾那我们把DESC换成ASC后得到上面的代码段再运行看看，再选择mm=1的时候张三的那组就有两条数据了。

• 5.dense_rank() over(partition by 分组字段  order by 排序字段   排序方式) 

 SELECT *
   FROM (select t.update_date,
                t.salary,
                t.empolyee_name,
                dense_rank() over(partition by t.empolyee_name order by t.update_date desc
                ) mm
           from abcs t)
  where mm = 1;

运行结果：dense_rank()是连续排序，与4比较会发现虽然会有两个mm=2的行还是会按循序从3开始排列。

• 6.sum(汇总字段) over(partition by 分组字段  order by 排序字段   排序方式) 

__一般SUM函数的用法：根据用户分组汇总 

select t.empolyee_name, sum(t.salary)
    from abcs t
   group by t.empolyee_name

过程分析：

A.sum(汇总字段) over(order by 排序字段),通过下面的列子可以看到根据排序字段(t.update_date)依次累加。

select t.update_date,
          t.salary,
          t.empolyee_name,
          sum(t.salary) over(order by t.update_date) as sumQty
     from abcs t

B.sum(汇总字段) over(partition by 分组字段 order by 排序字段)，加上分组之后可以看到运行结果是先分组后再根据排序字段进行累加

select t.update_date,
          t.salary,
          t.empolyee_name,
          sum(t.salary) over(partition by t.empolyee_name order by t.update_date) as sumQty
     from abcs t

C.sum(汇总字段) over(partition by 分组字段 ),去掉排序的，分组的汇总字段会一次性汇总。如果B列中分组字段和排序字段相同那么跟C的效果是一样的。如果其他字段一样还加上去重distinct只显示一条。

select t.update_date,
          t.salary,
          t.empolyee_name,
          sum(t.salary) over(partition by t.empolyee_name ) as sumQty
     from abcs t

以上两种分组group by 和partition by的区别：partition by在增加排序后能对排序字段进行累加