Oracle应用之开窗函数笔记及应用场景
Oracle应用之开窗函数笔记及应用场景
SmileNicky
发布于 2022-05-07 16:38:19
发布于 2022-05-07 16:38:19
介绍Oracle的开窗函数之前先介绍一下分析函数，因为开窗函数也属于分析函数
分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
上面是开窗函数的简单介绍。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化
oracle开窗函数有很多，用的比较多的是over(…)，使用的话一般是和order、partition by、row_number()、rank()、dense_rank()几个函数一起使用
例子：成绩表的例子，
学号 | 姓名 | 班级 | 成绩 |
|---|---|---|---|
111 | 小王 | 2 | 92 |
123 | 小李 | 1 | 90 |
134 | 小钱 | 1 | 92 |
145 | 小顺 | 2 | 100 |
数据表为t_score，字段分别为stuId，stuName，classId ，score
create table t_score(
stuId varchar2(20),
stuName varchar2(50),
classId number,
score float
);
insert into t_score(stuId,stuName,classId,score) values('111','小王',1,92);
insert into t_score(stuId,stuName,classId,score) values('123','小李',1,90);
insert into t_score(stuId,stuName,classId,score) values('134','小钱',1,92);
insert into t_score(stuId,stuName,classId,score) values('145','小顺',1,100);over函数和row_number一起使用： 开窗函数和row_number函数一起使用的话，就是返回一行，不过这里其实不适合用来统计，因为统计成绩的话，可以存在分数一样的两条数据，而row_number只返回一条数据。
select *
from (select stuId, stuName, classId,
row_number() over(partition by classId order by score desc) rn
from t_score)
where rn = 1;over函数和rank一起使用： rank函数功能也是排序，这里的话，假如有分数一样的两条数据的情况，如图sql是根据score排序的，有两人并列第二名，实际的第3名就被算成第4名了，如图，rn=4
select stuId,
stuName,
classId,
rank() over(partition by classId order by score desc) rn
from t_score;
在这里插入图片描述
over函数和dense_rank一起使用： dense_rank函数是rank函数的补充，假如有分数一样的两条数据，出现了两人并列第二名的情况，实际的第3名算的是正确的
select stuId,
stuName,
classId,
dense_rank() over(partition by classId order by score desc) rn
from t_score;
在这里插入图片描述
ok，这种是同班级的情况，对于不同班级的情况，修改数据
INSERT INTO t_score(stuId,stuName,classId,score) VALUES('111','小王',2,92);
INSERT INTO t_score(stuId,stuName,classId,score) VALUES('123','小李',1,90);
INSERT INTO t_score(stuId,stuName,classId,score) VALUES('134','小钱',1,92);
INSERT INTO t_score(stuId,stuName,classId,score) VALUES('145','小顺',2,100);
在这里插入图片描述
可以看出，对办件进行分组再按成绩排序，oracle的分析函数功能还是做得比较齐全的
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2018-12-31，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
