SQL入门之第二三讲——窗口函数的介绍

发布时间:2024-07-19 10:01

窗口函数,是一种分析型的OLAP函数,OLAP是 online analytical processing 的简称,意思是对数据库数据进行实时分析处理。而且面试过程中出现的频率非常高,几乎是大厂的 面试必考题

窗口函数是比较之前的内容较为难懂一点,是属于高阶知识,一般这个窗口函数会放在select语句中。

基本语法:

<窗口函数> OVER (
	PARTITION BY <用于分组的列名> --可选 
	ORDER BY <用于排序的列名> --可选
)

上面的 <窗口函数> 这个位置可以放下面两种函数:

  1. 聚合函数:如sum、avgmin、max、coun等
  2. 专用窗口函数:如rank、dense_rank、row_number

一般这个窗口函数会放在select语句中

1.聚合函数

  • 窗口函数也是用来进行分组排序的,与聚合函数+GROUP BYDE 效果类似。但是窗口函数所产生的记录不会聚合到一起,每一行数据都生成一条记录。

实例1:在成绩表中,找到Sid为7-10的学生,并计算了每个学生的总分,并且总分按照正序显示出来。

select 
Sid as \'学生编号\',
sum(score) as \'总分\'
from scores
where Sid BETWEEN 6 AND 11
GROUP BY Sid
ORDER BY sum(score)

\"SQL入门之第二三讲——窗口函数的介绍_第1张图片\"

如上我们知道了学生的总分,但是我还想知道学生的每科成绩的分数,怎么办?

  • 如果用上面的语句在select 后面加上Cid,和Score 两列,在GROUP BY 子句中也需要加上Cid,和Score 两列
  • 那么查询出来的数据就不是我们想要的,总分计算的是单独每科的,而不是我们想要的总分,如图:
select 
Sid as \'学生编号\',
Cid as \'科目编号\',
score as \'学生各科成绩\',
sum(score) as \'总分\'
from scores
where Sid BETWEEN 6 AND 11
GROUP BY Sid,Cid,score

\"SQL入门之第二三讲——窗口函数的介绍_第2张图片\"

  • 这时候如果我们想要知道学生的总分,学生每科成绩,课程编号,学生编号等数据,我们就需要使用窗口函数

实例2:

select 
Sid as \'学生编号\',
Cid as \'科目编号\',
score as \'学生各科成绩\',
sum(score) OVER (PARTITION BY Sid) as \'总分\'
from scores
where Sid between 6 and 11
group by Sid,Cid,score
order by sum(score)

\"SQL入门之第二三讲——窗口函数的介绍_第3张图片\"
补充知识:PARTITION BY的含义

  • PARTITION BY 是分区的意思,与GROUP BY 分组的意思是一样的。
  • 如果不写 PARTITION BY 就代表整个数据属于一个分区。
  • 如果上面的SQL语局不写 PARTITION BY Sid ,会如何?
select 
Sid as \'学生编号\',
Cid as \'科目编号\',
score as \'学生各科成绩\',
sum(score) OVER () as \'总分\'
from scores
where Sid between 6 and 11
group by Sid,Cid,score
order by sum(score)

结果是会将所有的学生总分相加显示出来
\"SQL入门之第二三讲——窗口函数的介绍_第4张图片\"

2.专用窗口函数

常用的专用窗口函数有:

(1)获取数据排名

  • ROW_NUMBER() :不考虑并列名次的情况下,例如前三名分数都是88,88,77,排名是1,2,3
  • RANK():如果有并列名次的情况下,会自动占用下一名次的位置。例如,前三名分数都是88,88,77,排名是1,1,3
  • DENSE_RANK() :如果有并列名次的情况下,不占用下一名次的位置,例如前三名分数都是88,88,77,排名是1,1,2

实例3:在成绩表中,找出Sid为6-11的学生,并计算成绩从高到低的排名

select *,
	ROW_NUMBER() OVER (
	order by score
	) as \'成绩排名\'
from scores
where Sid between 6 and 11;

\"SQL入门之第二三讲——窗口函数的介绍_第5张图片\"

如果将 ROW_NUMBER() 替换成 RANK(),返回结果是这样的:
\"SQL入门之第二三讲——窗口函数的介绍_第6张图片\"

(2)获取第一名或者最后一名

  • FIRST_VALUE(<列名>) :获取第一名
  • LAST_VALUE(<列名>) :获取最后一名

实例4:在成绩表中,找到Sid为6-11的学生, 获取每个学生的最高成绩

select *,
FIRST_VALUE(score) OVER (
	PARTITION BY Sid
	ORDER BY score DESC
) as \'最高成绩\'
from scores
where Sid between 6 and 11

\"SQL入门之第二三讲——窗口函数的介绍_第7张图片\"

(3)偏移函数

  • LEAD(<列名>,<数值n>):从当前访问向下偏移N行的数据
  • LAG(<列名>,<数值n>):从当前访问向上偏移N行的数据
  • NTH_VALUE(<列名>,<数值n>):从结果集中的第N行获取数据

实例5:获取下面第2行偏移分数

select *,
LEAD(score,2) OVER (ORDER BY Sid DESC) as \'获取下面第2行score值\'
from scores
where Sid between 7 and 9

\"SQL入门之第二三讲——窗口函数的介绍_第8张图片\"

(4)分布函数

  • CUME_DIST():分组内小于,等于当前rank值的行数/分组内的总行数
  • PERCENT_RANK():返回某列每行的百分比排序,每行按照公式(rank-1)/ (row-1) 进行计算
  • NTILE():将结果集整体分为N组,并展现出某一条数据被分配在那个组中

ItVuer - 免责声明 - 关于我们 - 联系我们

本网站信息来源于互联网,如有侵权请联系:561261067@qq.com

桂ICP备16001015号