rank 开窗函数_Oracle 的开窗函数 rank,dense_rank,row_number

1、开窗函数和分组函数的区别

分组函数是指按照某列或者某些列分组后进行某种计算，比如计数，求和等聚合函数进行计算。

开窗函数是指基于某列或某些列让数据有序，数据行数和原始数据数相同，依然能曾现个体数据的原貌。

事例数据

create table student_scores(

stu_id varchar2(10),--学号

stu_name varchar2(20),--姓名

course varchar2(5),--课程

score number(5,2),--分数

constraint pk_stuid_course primary key (stu_id,course)

);

insert into student_scores(stu_id,stu_name,course,score) values(‘10001‘,‘周杰伦‘,‘语文‘,‘85‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10001‘,‘周杰伦‘,‘数学‘,‘96‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10001‘,‘周杰伦‘,‘英语‘,‘67‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10002‘,‘谢霆锋‘,‘语文‘,‘71‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10002‘,‘谢霆锋‘,‘数学‘,‘97‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10002‘,‘谢霆锋‘,‘英语‘,‘100‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10003‘,‘苏有朋‘,‘语文‘,‘79‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10003‘,‘苏有朋‘,‘数学‘,‘65‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10003‘,‘苏有朋‘,‘英语‘,‘80‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10004‘,‘刘德华‘,‘语文‘,‘41‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10004‘,‘刘德华‘,‘数学‘,‘61‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10004‘,‘刘德华‘,‘英语‘,‘62‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10005‘,‘小龙女‘,‘语文‘,‘94‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10005‘,‘小龙女‘,‘数学‘,‘95‘);

insert into student_scores(stu_id,stu_name,course,score) values(‘10005‘,‘小龙女‘,‘英语‘,‘96‘);

2、分组函数：group by

按照课程进行分组，可以进行如下计算

select course,count(*) cou,avg(score) avgs,max(score) maxs,min(score) mins

from student_scores

group by course;

COURSE COU AVGS MAXS MINS

1 数学 5 82.8 97 61

2 语文 5 74 94 41

3 英语 5 81 100 62

3、开窗函数：rank() ,dense_rank(),row_number()

每个学科按照分数倒序排名

select stu_id,stu_name,course,score,rank() over(partition by course order by score desc) rankfrom student_scores;

STU_ID STU_NAME COURSE SCORE RANK

1 10002 谢霆锋 数学 97.00 1

2 10001 周杰伦 数学 96.00 2

3 10005 小龙女 数学 95.00 3

4 10003 苏有朋 数学 65.00 4

5 10004 刘德华 数学 61.00 5

6 10002 谢霆锋 英语 100.00 1

7 10005 小龙女 英语 96.00 2

8 10003 苏有朋 英语 80.00 3

9 10001 周杰伦 英语 67.00 4

10 10004 刘德华 英语 62.00 5

11 10005 小龙女 语文 94.00 1

12 10001 周杰伦 语文 85.00 2

13 10003 苏有朋 语文 79.00 3

14 10002 谢霆锋 语文 71.00 4

15 10004 刘德华 语文 41.00 5

注意分组函数和开窗函数针对数据在维度上的变化，分组函数使个体数据小时。开窗函数保留个体。