和聚合函数对应着看,聚合函数是通过指定一列来统计单行数据的。开窗函数是多行。
建表(Oracle):
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);
insert into t_score(stuId,stuName,classId,score) values('121','小A',2,92);
insert into t_score(stuId,stuName,classId,score) values('131','小B',2,90);
insert into t_score(stuId,stuName,classId,score) values('141','小C',3,92);
insert into t_score(stuId,stuName,classId,score) values('151','小D',3,100);
分析函数:
场景: 查询每个班的第一名
步骤一:每个班的学生在自己的班级里排序
select
-- 表的原始字段
stuId,
stuName,
classId,
score,
-- 多查一个排序字段
row_number() over(partition by classId order by score desc) rn
from t_score
步骤二:找到想要的排名,以第一名为例
-- row_number() 不存在并列,一定是1.2.3....N
select *
from (select
-- 表的原始字段
stuId,
stuName,
classId,
score,
-- 多查一个排序字段
-- row_number是一个内置函数,用来生成序号, over加在聚合函数后面表示row_number当作窗口函数(多行),而不是聚合函数(一行),partition by表示用指定字段进行分区,即依据字段值的不同来做分区表(以classid分区,则可以看作每个班级都是一个独立的分区表),因此后面的order by也是在分区表中生效,不会在全表里排序
row_number() over(partition by classId order by score desc) rn
from t_score)
where rn = 1; -- rn 就是序号, rn=1表示排序后的第一个,rn=2表示第二个,以此类推
分析:首先通过窗口函数把每个班的学生按班级为单位做了分区,然后用分数做降序排列。这样就得到了一个有序号的,以班级为单位的分区表。最后只要找排名(rn=?)即可。
补充:rank()函数,统计并列情况
-- rank 存在并列的情况, 1.2.2.4....N
-- dense_rank 1.2.2.3.4...N
select stuId,
stuName,
classId,
score,
-- 聚合函数rank后面加上over() 表示这是一个开窗函数,即查出来是多行(group by出来是一行),每行都有一个开窗函数的结果,partition by是指以哪个字段做分区,classid做分区,则表示1.2.3 三个班都是独立的分区表,后面的order by也是在独立分区表里面排序
rank() over(partition by classId order by score desc) rn
from t_score;
使用rank函数会把数据相同的几项的序号认为是一样的,后面的排序会跳过并列的数量。