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()
每个学科按照分数倒序排名
| 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 |
注意分组函数和开窗函数针对数据在维度上的变化,分组函数使个体数据小时。开窗函数保留个体。