Oracle开窗函数详解

【函数格式】

        分析函数 () OVER ( [PARTITION BY 分组字段] [ORDER BY 排序字段])

【参数说明】

        分析函数包括以下几类：

                聚合函数：count()、sum()、max()、 min()、avg()等；

                排序函数：rank()、dense_rank()、row_number()等；

                偏移函数：lag()、lead()；

        partition by：表示根据指定字段进行分组，可省略；

        order by：表示根据指定字段进行排序，可省略；

【函数解释】

        开窗函数可以根据指定的分组字段将数据分成多个组，再通过分析函数获取每个组的分析数据，并且每一组可以返回多行结果，不像聚合函数每组只能返回一行结果。

【创建样例数据】

创建一张学生成绩表，SQL语句如下，

        -- Create table
create table T_STUDENT_SCORE
(
  class   VARCHAR2(20) not null,
  student VARCHAR2(20) not null,
  subject VARCHAR2(20) not null,
  score   NUMBER not null
);
-- Add comments to the table 
comment on table T_STUDENT_SCORE
  is '学生成绩表-展示用';
-- Add comments to the columns 
comment on column T_STUDENT_SCORE.class
  is '班级';
comment on column T_STUDENT_SCORE.student
  is '学生';
comment on column T_STUDENT_SCORE.subject
  is '科目';
comment on column T_STUDENT_SCORE.score
  is '成绩';
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_STUDENT_SCORE
  add constraint PK_T_STUDENT_SCORE primary key (CLASS, STUDENT, SUBJECT);

往表中写入以下数据

【样例展示1】

获取每个班每个学生自身的各科成绩的排名情况，SQL语句如下，其中rank()表示对排序字段值相同的行排名进行并列，排名不连续。

select class,student,subject,score,rank() over(partition by class,student order by score desc) from T_STUDENT_SCORE

查询结果如下图

【样例展示2】

获取每个班每科成绩的各个学生的排名情况，SQL语句如下，其中dense_rank()表示对排序字段值相同的行排名进行并列，排名连续。

select class,subject,student,score,dense_rank() over(partition by class,subject order by score desc) from T_STUDENT_SCORE

查询结果如下图

【样例展示3】

获取每一科成绩各个学生的排名情况，SQL语句如下，其中row_number()表为每一个分组内的所有行按顺序生成一个连续的序号，排序字段值相同的行不并列，也就是说，同一组内不会出现序号相同的行。

select subject,class,student,score,row_number() over(partition by subject order by score desc) from T_STUDENT_SCORE

查询结果如下图

【样例展示4】

获取每个班级每个学生的所有科目的平均分，SQL语句如下，其中avg()表示求平均数。

select class,student,subject,score,avg(score) over(partition by class,student) from T_STUDENT_SCORE

查询结果如下图

【样例展示5】

获取按班级、学生分组对各科成绩进行累加的结果，SQL语句如下，其中sum()表示求和。

select class,student,subject,score,sum(score) over(partition by class,student order by score desc) from T_STUDENT_SCORE

查询结果如下图