嗨!你来啦!如果说前面文章我们学习的SQL的基础知识,让你从青铜修炼到了星耀,那么今天我们即将学习的SQL的窗口函数,将会带你一路到王者,carry全场,6到飞起!现在我们一起来开始我们的升级之旅吧! ①窗口函数有什么用? 在工作中,常常会遇到组内排名问题,如经典的topN问题,这时就轮到SQL的高级功能窗口函数出场啦!窗口函数可以进行排序,生成序列号等一般的聚合函数无法实现的高级操作。 而且,一个非常棒的情况就是:Mysql从8.0版本开始,也和Sql Server、Oracle一样支持在查询中使用窗口函数啦! 下面我将通过实例介绍窗口函数并举例分组排序函数的使用。 ②什么是窗口函数? 窗口函数也称为OLAP函数(Online Anallytical Processing),意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。 窗口函数语法:其中[]中的内容可以省略 窗口函数大体可以分为以下两种: 需要注意的是: 接下来,我们通过几颗栗子来看一下几种专用的窗口函数怎么使用吧! ①专用窗口函数rank 栗子:下表为班级表(图1),请在每个班内排名,并得到如图2的结果。 窗口函数兼具分组和排序两种功能(图2中橘色部分为partition分组,蓝色箭头所示为order by 排序)。 通过partition by分组后的记录集合称为窗口,然而partition by不是窗口函数所必须的,如果没有指定PARTITION BY子句,窗口就没有限制。换种说法就是:如果没有显示指定分区,则默认分区就是把整个查询结果集当作一个分区。有一点不太明显,这里提出来:同一个查询中的不同函数,可能会有不同的分区描述。 你可能会想问:窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么,为什么还要用窗口函数呢? 这是因为: ②其他专用窗口函数: 除了rank函数,专用窗口函数还有dense_rank, row_number有什么区别呢? 是不是有点抽象,没关系,下面我们来以几颗栗子看一下专用窗口函数的用法和区别: ★排名问题(如对学生的成绩进行排名): 从结果表可以看出: 栗子:对商品价格按三种方式排序。 ★topN问题(如查询各科成绩的前两名): score表 经典topN问题:每组最大的N条记录。这类问题涉及到“既要分组,又要排序”的情况,要能想到用窗口函数来实现。 聚合窗口函数和上面提到的专用窗口函数用法完全相同,只需要把聚合函数写在窗口函数的位置即可,但是函数后面括号里面不能为空,需要指定聚合的列名。 依然以班级表为栗子,看一下窗口函数是聚合函数时,会出来什么结果: 可以看出:求和,平均、计数、最大最小值都是针对自身记录、以及自身记录之上的所有数据进行计算。如sum是求累计和,count是累计计数,avg是累计后求平均。如果想要知道所有人成绩的总和、平均等聚合结果,看最后一行即可。 ★聚合函数作为窗口函数的作用: 聚合函数作为窗口函数,可以在每一行的数据里直观的看到,截止到本行数据,统计数据是多少(最大值、最小值等)。同时可以看出每一行数据,对整体统计数据的影响。 栗子:现有“成绩表”,记录了每个学生各科的成绩。表内容如下: 查找单科成绩高于该科目平均成绩的学生名单。 窗口函数就是将表以窗口为单位进行分割,并在其中进行排序的函数。其中还包含在窗口中指定更加详细的汇总范围的备选功能,该备选功能中的汇总范围称为框架。 这里我们直接用聚合函数avg的窗口函数举例说明: 指定最靠近的3行做为汇总对象: 指定框架(汇总范围):这里使用的rows(行)和preceding(之前)两个关键字,将框架指定为截止到之前XX行。因此rows 2 preceding就是将框架指定为截止到之前2行,也就是将作为汇总对象的记录限定为自身记录及前2行的平均。,例如,刚才例子中的0002、0003、0004行数据,就是一个“框架” 假设求学号0004学生的current_avg,当前行为83,前1行记录为89,前两行记录为95,所以(83+89+95)/3=89。 每一行得到的结果,都是当前行和前面2行的平均(共3行)。想要计算当前行与前n行(共n+1行)的平均时,只要调整rows…preceding中间的数字即可。 ★窗口的移动平均的作用: 由于这里可以通过preceding关键字调整作用范围,在以下场景中非常适用: 在公司业绩名单排名中,可以通过移动平均,直观地查看到与相邻名次业绩的平均、求和等统计数据。 好啦,关于窗口函数的学习就到这里啦,最后,依然是一张思维导图,带你一起复习一下窗口函数: 现在,我宣布,SQL关于数据分析的系统知识学习到此结束,恭喜你有掌握了一门新技能,成为SQL王者! 后面的文章将告诉你,我们如何用学到的这项技能进行数据分析,和我一起期待接下来的项目实战吧! 谢谢你呀,一路上有你的陪伴真的很开心!希望你可以一直在,无论路有多长,我们一起走! 喜欢的话,动动手指点个赞吧,你的每个喜欢,都是我码字的动力!祝你早安、午安、晚安!See you !1.什么是窗口函数
<窗口函数> over ([partition by <用于分组的列名>]
order by <用于排序的列名>)
2.如何使用专用窗口函数?
select *,
rank() over (PARTITION BY 班级
ORDER BY 成绩 DESC ) as ranking
from 班级表;
select *,
rank() over (order by 成绩 desc) as ranking,
dense_rank() over (order by 成绩 desc) as dese_rank,
row_number() over (order by 成绩 desc) as row_numfrom 班级表
select product_name, product_type, sale_price,
rank () over (order by sale_price) as ranking,
dense_rank () over (order by sale_price) as dense_ranking,
row_number () over (order by sale_price) as row_num
from Product
select *
from (select *,
row_number() over (partition by 课程号 order by 成绩 desc) as ranking
from score) as a
where ranking ?= 2;
-- topN问题 sql模板
select * from (select *,
row_number() over (partition by 要分组的列名
order by 要排序的列名 desc) as ranking
from 表名) as a where ranking ?= N;
3.如何使用聚合窗口函数
select *,
sum(成绩) over (order by 学号) as current_sum,
avg(成绩) over (order by 学号) as current_avg,
count(成绩) over (order by 学号) as current_count,
max(成绩) over (order by 学号) as current_max,
min(成绩) over (order by 学号) as current_min
from 班级表
select * from (select *,
avg(成绩) over (partition by 科目) as avg_score
from 成绩表) as b
where 成绩 ? avg_score;
4.窗口函数的移动平均
select *,
avg(成绩) over (order by 学号 rows 2 preceding)
as current_avg
from 班级表;