百度360必应搜狗淘宝本站头条
当前位置:网站首页 > 技术分类 > 正文

通俗易懂的学会:SQL窗口函数

ztj100 2024-12-13 17:24 18 浏览 0 评论

?一.窗口函数有什么用?

在日常工作中,经常会遇到需要在每组内排名,比如下面的业务需求:

排名问题:每个部门按业绩来排名

topN问题:找出每个部门排名前N的员工进行奖励

面对这类需求,就需要使用sql的高级功能窗口函数了。

二.什么是窗口函数?

窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。

窗口函数的基本语法如下:

Bash
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

那么语法中的<窗口函数>都有哪些呢?

<窗口函数>的位置,可以放以下两种函数:

1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2) 聚合函数,如sum. avg, count, max, min等

因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中

三.如何使用?

接下来,就结合实例,给大家介绍几种窗口函数的用法。

1.专用窗口函数rank

例如下图,是班级表中的内容

如果我们想在每个班级内按成绩排名,得到下面的结果。

以班级“1”为例,这个班级的成绩“95”排在第1位,这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内,按成绩排名了。

得到上面结果的sql语句代码如下:

Bash
select *, rank() over (partition by 班级 order by 成绩 desc) as rankingfrom 班级表

我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”,这句话可以分为两部分:

1)每个班级内:按班级分组

partition by用来对表分组。在这个例子中,所以我们指定了按“班级”分组(partition by 班级)

2)按成绩排名

order by子句的功能是对分组后的结果进行排序,默认是按照升序(asc)排列。在本例中(order by 成绩 desc)是按成绩这一列排序,加了desc关键词表示降序排列。

通过下图,我们就可以理解partiition by(分组)和order by(在组内排序)的作用了。

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么,为什么还要用窗口函数呢?

这是因为,group by分组汇总后改变了表的行数,一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

相信通过这个例子,你已经明白了这个窗口函数的使用:

Bash
select *, rank() over (partition by 班级 order by 成绩 desc) as rankingfrom 班级表

现在我们说回来,为什么叫“窗口”函数呢?这是因为partition by分组后的结果称为“窗口”,这里的窗口不是我们家里的门窗,而是表示“范围”的意思。

简单来说,窗口函数有以下功能:

1)同时具有分组和排序的功能

2)不减少原表的行数

3)语法如下:

Bash
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

2.其他专业窗口函数

专用窗口函数rank, dense_rank, row_number有什么区别呢?

它们的区别我举个例子,你们一下就能看懂:

Bash
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 班级表

得到结果:

从上面的结果可以看出:

rank函数:这个例子中是5位,5位,5位,8位,也就是如果有并列名次的行,会占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,4。

dense_rank函数:这个例子中是5位,5位,5位,6位,也就是如果有并列名次的行,不占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,2。

row_number函数:这个例子中是5位,6位,7位,8位,也就是不考虑并列名次的情况。比如前3名是并列的名次,排名是正常的1,2,3,4。

这三个函数的区别如下:

最后,需要强调的一点是:在上述的这三个专用窗口函数中,函数后面的括号不需要任何参数,保持()空着就可以。

现在,大家对窗口函数有一个基本了解了吗?

3.聚合函数作为窗口函数

聚和窗口函数和上面提到的专用窗口函数用法完全相同,只需要把聚合函数写在窗口函数的位置即可,但是函数后面括号里面不能为空,需要指定聚合的列名。

我们来看一下窗口函数是聚合函数时,会出来什么结果:

Bash
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_minfrom 班级表

得到结果:

有发现什么吗?我单独用sum举个例子:

如上图,聚合函数sum在窗口函数中,是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号,在使用sum窗口函数后的结果,是对0001,0002,0003,0004号的成绩求和,若是0005号,则结果是0001号~0005号成绩的求和,以此类推。

不仅是sum求和,平均、计数、最大最小值,也是同理,都是针对自身记录、以及自身记录之上的所有数据进行计算,现在再结合刚才得到的结果(下图),是不是理解起来容易多了?

比如0005号后面的聚合窗口函数结果是:学号0001~0005五人成绩的总和、平均、计数及最大最小值。

如果想要知道所有人成绩的总和、平均等聚合结果,看最后一行即可。

这样使用窗口函数有什么用呢?

聚合函数作为窗口函数,可以在每一行的数据里直观的看到,截止到本行数据,统计数据是多少(最大值、最小值等)。同时可以看出每一行数据,对整体统计数据的影响。

四.注意事项

partition子句可是省略,省略就是不指定分组,结果如下,只是按成绩由高到低进行了排序:

Bash
select *, rank() over (order by 成绩 desc) as rankingfrom 班级表

得到结果:

但是,这就失去了窗口函数的功能,所以一般不要这么使用。

四.总结

1.窗口函数语法

Bash
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

<窗口函数>的位置,可以放以下两种函数:

1) 专用窗口函数,比如rank, dense_rank, row_number等

2) 聚合函数,如sum. avg, count, max, min等

2.窗口函数有以下功能:

1)同时具有分组(partition by)和排序(order by)的功能

2)不减少原表的行数,所以经常用来在每组内排名

3.注意事项

窗口函数原则上只能写在select子句中

4.窗口函数使用场景

1)业务需求“在每组内排名”,比如:

排名问题:每个部门按业绩来排名

topN问题:找出每个部门排名前N的员工进行奖励

下一次会跟大家分享一些窗口函数的面试题,从而让各位在面试、工作中都能遇到这类问题,就想到哦,这用窗口函数就可以解决。

推荐:如何提升你的分析技能,实现升职加薪?

相关推荐

Win10预览版10532已知问题汇总(微软win11正式版已知问题一览)

IT之家讯微软已向Insider用户推送了Win10预览版10532更新,本次更新对右键菜单、《Windows反馈》应用以及Edge浏览器进行了改进。除此之外还包含一些Bug,汇总如下,有意升级Wi...

Gabe Aul正测试Win10 Mobile 10532,Insider用户还需等

IT之家讯本月中旬微软向Insider用户推送了Win10Mobile预览版10512,该版本修复了一些Bug,增强了系统稳定性,但依然存在一些问题。今天,微软Insider项目负责人GabeAu...

微软开始推送Win10预览版10532快速版更新

8月28日消息,刚才,微软推送了Win10Build10532快速版,修复了之前的Bug,并带来了三项改进。主要来说,这次的更新改进了右键菜单的UI,使其更具Modern风格(见上图)。此外,更新...

Win10预览版10532更新内容大全(windows10更新预览版)

IT之家讯今天凌晨微软向Insider用户推送了Win10预览版10532快速版更新,本次更新主要带来了三处改进,汇总如下:o改进右键菜单,外观更加Modern。这是基于网友要求界面一致的反馈做出...

无法升级Win10预览版10532?也许Hyper-V在搞鬼

根据IT之家网友的反映,安装了微软虚拟机Hyper-V的Win10预览版用户无法成功升级Build10532版本,安装过程中会被要求回滚系统。很多朋友在尝试关闭虚拟机之后重启安装程序,结果仍然无法顺...

Win10预览版10532界面兴起“酷黑”风潮

Win10预览版10532的界面改动还是较为明显的,主要体现在右键菜单上面。总体来看,该版本的右键菜单间距更宽,视觉上更大气,操作上更便于触控。具体来说,任务栏右键菜单的变化最为明显。除了增加选项的宽...

Win10预览版10532上手图集(windows10预览版下载)

IT之家讯8月28日,微软今天推送了Win10预览版10532快速版更新,在该版本中,微软主要是加强细节上调整,并且主要是增强Edge浏览器性能等。在Windows10预览版10532中,微软改进了...

Win10预览版10532上手视频亮点演示

IT之家讯8月28日消息,今天凌晨微软向WindowsInsider快速通道用户推送了Win10预览版10532。在Windows10预览版10532中,微软改进了右键菜单,外观更加现代化。另外还...

第二篇 前端框架Vue.js(vue前端框架技术)

前端三大核心是网页开发的基础,Vue则是基于它们构建的“生产力工具”。通俗理解就是HTML是化妆的工具如眉笔,CSS是化妆品如口红,JavaScript是化妆后的互动,而Vue就是化妆助手。有了化妆工...

基于SpringBoot + vue2实现的旅游推荐管理系统

项目描述...

基于Vue以及iView组件的后端管理UI模板——iview-admin

介绍iView-admin是一套后端管理界面模板,基于Vue2.0,iView(现在为ViewUI)组件是一套完整的基于Vue的高质量组件库,虽然Github上有一套非常火的基于ElementUI...

别再说你会SPA开发了,这5个核心你真的搞懂了吗?

前言此spa非彼spa,不是你所熟知的spa。你所熟知的spa作者肯定是没有你熟悉的。我们这里指的是在前端开发中的一种模型,叫作单页应用程序,顾名思义,就是整个项目只有一个页面,而页面中的内容是动态的...

React.js Top20面试题(react.js中文官网)

概述作为React开发者,对框架的关键概念和原则有扎实的理解是很重要的。考虑到这一点,我整理了一份包含20个重要问题的清单,每个React开发者都应该知道,无论他们是在面试工作还是只是想提高技能。...

美媒:特朗普签署行政令后,FBI又发现约2400份、总计超14000页涉肯尼迪遇刺案文件

来源:环球时报新媒体1月23日特朗普下令公布肯尼迪遇刺案相关机密文件图源:美媒综合福克斯新闻网和Axios网站10日报道,在总统特朗普签署行政令,要求公布“肯尼迪遇刺案”相关政府机密文件之后,美国...

2021 年 Node.js 开发人员学习路线图

Node.js自发布以来,已成为业界重要破局者之一。Uber、Medium、PayPal和沃尔玛等大型企业,纷纷将技术栈转向Node.js。Node.js支持开发功能强大的应用,例如实时追踪...

取消回复欢迎 发表评论: