oracle分析函数之rank学习记录

1. 构造演示数据

create table students(id number(5),name varchar2(10),chinese number(3),math number(3),english number(3),class varchar2(10));

insert into students(id,name,chinese,math,english,class) values(1,'张一',67,72,88,'一班');
insert into students(id,name,chinese,math,english,class) values(2,'张二',62,72,81,'一班');
insert into students(id,name,chinese,math,english,class) values(3,'张三',92,92,86,'一班');
insert into students(id,name,chinese,math,english,class) values(4,'李一',93,78,86,'二班');
insert into students(id,name,chinese,math,english,class) values(5,'李二',78,85,86,'二班');
insert into students(id,name,chinese,math,english,class) values(6,'李三',87,83,89,'二班');
insert into students(id,name,chinese,math,english,class) values(7,'李四',87,83,89,'二班');

insert into students(id,name,chinese,math,english,class) values(8,'张四',92,92,86,'一班');

insert into students(id,name,chinese,math,english,class) values(8,'张五',92,92,86,'一班');

commit;
select * from students;

select name,chinese,trunc(avg(chinese) over()) from students a; --全年级chinese平均分

NAME CHINESETRUNC(AVG(CHINESE)OVER())
张一 67 83
张二 62 83
张三 92 83
李一 93 83
李二 78 83
李三 87 83
李四 87 83
张四 92 83
张五 92 83

select name,chinese,rank() over(order by chinese desc) from students;--按chinese分数排名

NAME CHINESERANK()OVER(ORDERBYCHINESEDESC)
李一 93 1
张四 92 2
张三 92 2
张五 92 2
李四 87 5
李三 87 5
李二 78 7
张一 67 8
张二 62 9

select class,name,chinese,rank() over(partition by class order by chinese desc ) from students ;--分班按chinese分数排名,

CLASS NAME CHINESE RANK()OVER(PARTITIONBYCLASSORD
二班 李一 93 1
二班 李四 87 2
二班 李三 87 2
二班 李二 78 4
一班 张五92  1
一班 张四 92 1
一班 张三 92 1
一班 张一 67 4
一班 张二 62 5

注: 有相同级别并列时,会跳过下一级

select class,name,chinese,dense_rank() over(partition by class order by chinese desc ) from students ;--分班按chinese分数排名

CLASS NAME CHINESE DENSE_RANK()OVER(PARTITIONBYCL
二班 李一 931
二班 李三 872
二班 李四 872
二班 李二 783
一班 张三 921
一班 张四 921
一班 张一 672
一班 张二 623

注: 有相同级别也不会跳过下一级别