sql语句中开窗函数的使用
postgresql和8.0版本之后的mysql,均支持开窗函数。
开窗函数主要分为两类:
一、排序函数:
row_number() over(partition by xxx order by yyy)
partition by xxx 表示按照xxx字段分区(分区就是分组的意思),如果没有partition by子句的话,所有的记录当做一个分区。order by yyy 表示各分区按照yyy字段排序,即使yyy字段值一样,排名也不一样,从1一直往上加。
示例:
数据准备:
CREATE TABLE `t_score` (
`id` int NOT NULL AUTO_INCREMENT,
`class_id` int NOT NULL COMMENT '班级id',
`student_id` varchar(8) NOT NULL COMMENT '学号',
`score` int NOT NULL COMMENT '成绩',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t_score (class_id, student_id, score) values (1, '1-1', 10), (1, '1-2', 30), (1, '1-3', 20), (1, '1-4', 40), (1, '1-5', 50);
insert into t_score (class_id, student_id, score) values (2, '2-1', 50), (2, '2-2', 40), (2, '2-3', 10), (2, '2-4', 20), (2, '2-5', 30);
insert into t_score (class_id, student_id, score) values (3, '3-1', 15), (3, '3-2', 55), (3, '3-3', 35), (3, '3-4', 45), (3, '3-5', 25);
insert into t_score (class_id, student_id, score) values (4, '4-1', 80), (4, '4-2', 40), (4, '4-3', 20), (4, '4-4', 60), (4, '4-5', 10);
需求:查询每个班成绩前两名和后两名的学号、成绩。
with r as (select *, row_number() over(partition by class_id order by score desc) rn from t_score),
t as (select *, row_number() over(partition by class_id order by score) rn from t_score)
select class_id, student_id, score from r where rn <= 2
union all
select class_id, student_id, score from t where rn <= 2;
核心脚本是select *, row_number() over(partition by class_id order by score desc) rn from t_score,这一行脚本将t_score表的数据根据class_id进行分组,并把每个分组的数据按照score从大到小排序。row_number() over()的值,就是每一行记录在所在分组中的排名,值是1、2、3等等,从1开始。用了row_number() over(partition by xxx order by yyy)查排名时,不仅仅可以同时查分组字段和排序字段,还可以查其他任意字段,不会报语法错误的。
一旦碰见分组后查前几的问题,就应该立即想到用row_number() over()。
二.聚合函数:
聚合函数,over()中带或者不带order by,是两种完全不同的表现。
情况1:partition后面不带order by
sum(xxx) over(partition by yyy) // 分组后求每个分组xxx字段值的总和
count(1) over(partition by xxx) // 分组后求每个分组的记录数
max(xxx) over(partition by yyy) // 分组后求每个分组xxx字段的最大值
avg(xxx) over(partition by yyy) // 分组后求每个分组xxx字段的平均值
示例1:select *, sum(score) over(partition by class_id) from t_score;
除了返回原本记录外,每行记录最后面多了一列,每个分组的各行记录该列的值一样,值是该分组的总score。
上例中,sum(score)换成count(1)的话,每行记录最右侧列的值是该记录所属分组的记录数。
sum(score)换成max(score)的话,每行记录最右侧列的值是该记录所属分组的最大score。
sum(score)换成min(score)的话,每行记录最右侧列的值是该记录所属分组的最小score。
sum(score)换成avg(score)的话,每行记录最右侧列的值是该记录所属分组的平均score。
情况2:partition后面带order by
sum(xxx) over(partition by yyy order by zzz) // 值是根据yyy分组后,每个分组内部根据zzz排序,当前记录和上面所有记录的xxx字段的和
示例2:select *, sum(score) over(partition by class_id order by score) from t_score;
返回是,每个分组内部按照score升序,且每一行记录的最后一列的值是截止到该行score的总和。
上例中,sum(score)换成count(1)的话,则返回的是,每个分组内部按照score升序,且每一行记录的最后一列的值是本组截止到该行的记录数。和select *, row_number() over(partition by class_id order by score) from t_score;表现一致。
sum(score)换成max(score)的话,则返回的是,每个分组内部按照score升序,且每一行记录的最后一列的值是截止到该行score的最大值。
sum(score)换成min(score)的话,则返回的是,每个分组内部按照score升序,且每一行记录的最后一列的值是截止到该行score的最小值。
sum(score)换成avg(score)的话,则返回的是,每个分组内部按照score升序,且每一行记录的最后一列的值是截止到该行score的平均值。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步