MySQL数据库基础篇SQL窗口函数示例解析教程
作者:数据分析与统计学之美 发布时间:2024-01-24 22:20:54
本文简介
前段时间,黄同学写了一篇《MySQL窗口实战》文章(文章如下),但是里面大多数是以实战练习为主,没有做详细的解释。
传送门:MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费
于是,私信了月牙美女,看看她能否写一篇《窗口函数基础篇》,正好和之前那篇文章配套。这不,很快她就写好了,今天就给大家做一个分享,旨在和大家交流学习哦!
下面是月牙的玩笑简介,附带大图一张哦。
正文介绍
窗口函数,也被称为 “开窗函数”,MySQL8.0以后,就可以使用这些函数了。
我们在力扣刷题的时候,不难发现,比较困难的题目经常会涉及到窗口函数的应用,可以说窗口函数,是检验我们的SQL水平是否到达熟练水平的一个标尺。
窗口函数的格式为: 聚合函数+over()
窗口是描述over()括号内划定的内容,这个内容就是窗口函数的作用域,即操作的数据都在over()的范围内。
对于窗口函数,我个人的理解是给源数据开一扇可以滑动的窗口,在窗口移动的时候可以对其中的数据进行附加计算,如移动平均、分组排序等,窗口可以是一行多行甚至是所有行。
窗口函数,还可以对多组数据进行同步排序、聚合等运算,针对group by子句或where处理后的结果进行操作,只能写入select子句里。
灵魂画手上线,用Excel简单做了个简易版的窗口函数的演示图:
聚合函数 + over()
基本语法:
sum/avg(被加工的字段名) over(partition by 分组的字段名 order by 排序的字段名 rows between … and …)
含义: 表示用partition by分组后针对每个组别进行求和或者求均值。
--包括本行以内和前3行:rows between 6 preceding and current row
--包括本行以内和后3行:rows between current row and 3 following
--包括本行和之前所有的行:rows between unbounded preceding and current row
--包括本行和之后所有的行:rows between current row and unbounded following
--从前3行到下1行(总共包含5行数据):rows between 3 preceding and 1 following
还有一些其它的聚合函数,例如max、min、count,它们的语法结构都类似。
排序函数 + over()
row_number()、rank()、dense_rank()这三个函数,都是对select查询到的结果进行排序,我们来看看这三者的区别。
row_number()
: 为不重复的连续排序,从1开始,为查询到的数据依次生成不重复的序号进行排序
基本语法——row_number() over(order by 需要排序的字段asc/desc);
rank()
: 为跳跃排序,结果相同的两个数据并列,为下一个数据空出所占的名次,即相同排名会占位
基本语法——rank() over(order by 需要排序的字段 asc/desc);
dense_rank()
: 为有重复的连续排序,结果相同的两个数据并列,不为下一个数据空出所占的名次,即相同排名不占位
基本语法——dense_rank() over(order by 需要排序的字段 asc/desc);
我们用一张图来表示这三者间的关系:
ntile()函数 + over()
基本语法: ntile(n) over(partition by…order by…)其中n表示被切分的段数。
ntile(n)用于将分组数据平均切分成n块,如果切分的每组数量不均等,则第一组分得的数据更多。
ntile()函数通常用于比如求年级前10%成绩的学生,则n取值为10,用where筛选出第一组的数据。
偏移函数 + over()
基本语法1:前N行:lead(str, n, default) over(partition by …order by …)
基本语法2:后N行:lag(str, n, default) over(partition by …order by …)
str表示字段名,n表示前/后n行数据,默认值为1,default表示如果取值范围已经超过整个表的返回值,可以不填,不填默认返回N/A。
偏移函数,用于取出同一字段的前N行数据或后N行数据,作为单独的列,这里需要特别注意一下的是,lead代表前N行,lag代表后N行。
来源:https://huang-tong-xue.blog.csdn.net/article/details/116201478
猜你喜欢
- 图像特征-SIFT尺度不变特征变换1.1图像尺度空间在一定的范围内,无论物体是大还是小,人眼都可以分辨出来,然后计算机要具有相同的能力却很难
- Pytorch中的model.train() 和 model.eval() 原理与用法一、两种模式pytorch可以给我们提供两种方式来切换
- 今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们
- 我们已经知道,null 没有任何的属性值,并且无法获取其实体(existence)值。所以 null.property 返回的是错误(err
- 要绘制单个点,可使用函数scatter(),并向其传递一对x和y坐标,它将在指定位置绘制一个点:"""使用sc
- 标量标量由普通小写字母表示(例如,x、y和z)。我们用 R \mathbb{R} R表示所有(连续)实数标量的空间。标量由只有一个元素的张量
- python3中,list有个reverse函数,用来反转列表元素,但是如果想要反转部分元素呢?a = [1,2,3,4,5]a[0:3].
- 本文实例讲述了Python自定义函数实现求两个数最大公约数、最小公倍数。分享给大家供大家参考,具体如下:1. 求最小公倍数的算法:最小公倍数
- 基于python3+OpenCV的人脸和眼睛识别,供大家参考,具体内容如下一、OpenCV人脸检测的xml文件下载人脸检测和眼睛检测要用到h
- 首先,‘and’、‘or’和‘not&a
- 本文所述实例可以实现基于Python的查看图片报纸《参考消息》并将当天的图片报纸自动下载到本地供查看的功能,具体实现代码如下:# codin
- 导语今天在写 SQL 的时候,遇到一个问题。需求是这样的,查询数据,按照评分倒序、近一周访问量倒序,这样进行排序。问题是常规的写法,将 da
- 前言MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_que
- python中不存在所谓的传值调用,一切传递的都是对象的引用,也可以认为是传址。一、可变对象和不可变对象Python在heap中分配的对象分
- 第一种方法:第一步:先看报错窗口2003 can't connect to MySQL server on '127.0.0
- 首先看一下这三个函数:rtrim() ltrim() trim();rtrim()定义以及用法: rtrim() 函数移除字符串右侧的空白字
- 安装TensorFlow在Windows上,真是让我心力交瘁,想死的心都有了,在Windows上做开发真的让人发狂。首先说一下我的经历,本来
- 内容摘要:统计在线人数的方法很多,可以使用Application来统计在线人数,也可以使用IP来统计在线人数。各有优点。本文介绍了通过判断S
- 假设现在有如下N条记录 表明叫book id author title 1 aaa AAA 2 bbb BBB 3 ccc CCC 4 dd
- 本文实例讲述了python实现分析apache和nginx日志文件并输出访客ip列表的方法。分享给大家供大家参考。具体如下:这里使用pyth