MySQL数据库基础篇SQL窗口函数示例解析教程

目录
  • 本文简介
  • 正文介绍
  • 聚合函数 + over()
  • 排序函数 + over()
  • ntile()函数 + over()
  • 偏移函数 + over()

本文简介

前段时间，黄同学写了一篇《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行。

以上就是MySQL数据库基础篇窗口函数示例解析教程的详细内容，更多关于MySQL窗口函数基础的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySQL必备基础之分组函数 聚合函数 分组查询详解

    MySQL必备基础之分组函数 聚合函数 分组查询详解

    目录 一.简单使用 二.搭配DISTINCT去重 三.COUNT()详细介绍 四.分组查询 一.简单使用 SUM:求和(一般用于处理数值型) AVG:平均(一般用于处理数值型) MAX:最大(也可以用于处理字符串和日期) MIN:最小(也可以用于处理字符串和日期) COUNT:数量(统计非空值的数据个数) 以上分组函数都忽略空NULL值的数据 SELECT SUM(salary) AS 和,AVG(salary) AS 平均,MAX(salary) AS 最大,MIN(salary) AS 最小

  • mysql 计算函数详情

    mysql 计算函数详情

    目录 2.字段拼接 2.举一些mysql 函数例子 2.1 符处理 2.2 期处理 2.3 值处理 函数没有SQL的可移植性强 能运行在多个系统上的代码称为可移植的(portable).相对来说,多数SQL语句是可移植的,在SQL实现之间有差异时,这些差异通常不那么难处理. 而函数的可移植性却不强.几乎每种主要的DBMS的实现都支持其他实现不支持的函数,而且有时差异还很大. 为了代码的可移植,许多SQL程序员不赞成使用特殊实现的功能.虽然这样做很有好处, 但不总是利于应用程序的性能.如果不使用这

  • MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

    MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费

    目录 一.背景介绍 二.建表语句和插入数据 创建表格 插入数据 三.窗口函数分类介绍 四.窗口函数应用 1. 聚合函数 + over()搭配 2. 排序函数 + over()搭配 3. ntile()函数 + over()搭配 4. 偏移函数 + over()搭配 一.背景介绍 今天,野鸡大学高(三)班的月考成绩出来了,这里先给大家公布一下各位同学的考试成绩. 接着,在给大家公布一下各位同学的生活消费情况. 下面我们利用上述考试成绩和生活消费记录,利用mysql做一个简单的分析. 当然,从本文标

  • MySQL空间数据存储及函数

    MySQL空间数据存储及函数

    目录 一.数据类型 1.什么是MySQL空间数据 2.什么是geojson 3.格式化空间数据类型(geometry相互转换geojson) 二.空间分析 1.根据点位及半径,生成缓冲区 三.判断点位所在城市 四.常用的空间函数 前言: 不久前开发了一个地图相关的后端项目,需要提供一些点线面相关的存储.查询.分析相关的操作,于是对MySQL空间函数进行充分调研并应用在项目中;MySQL为空间数据存储及处理提供了专用的类型geometry(支持所有的空间结构),还有有细分类型Point, Line

  • MySQL实例精讲单行函数以及字符数学日期流程控制

    MySQL实例精讲单行函数以及字符数学日期流程控制

    目录 一.字符函数 1.大小写控制函数 2.字符控制函数 二.数学函数 三.日期函数 四.其他函数 五.流程控制函数 一.字符函数 1.大小写控制函数 ①UPPER():转换成大写 SELECT UPPER('Hello'); ②LOWER():转换成小写 SELECT LOWER('Hello'); 2.字符控制函数 ①LENGTH():获取参数值的字节个数 SELECT LENGTH('叶绿体不忘呼吸aaaa'); ②CONCAT():拼接字符串 SELECT CONCAT('Hello',

  • 非常实用的MySQL函数全面总结详解示例分析教程

    非常实用的MySQL函数全面总结详解示例分析教程

    目录 1.MySQL中关于函数的说明 2.单行函数分类 3.字符函数 4.数学函数 5.日期时间函数 6.其它常用系统函数 7.流程控制函数 8.聚合函数 1)聚合函数的功能和分类: 2)聚合函数的简单使用 3)五个聚合函数中传入的参数,所支持的数据类型有哪些? 4)聚合函数和group by的使用"最重要": 1.MySQL中关于函数的说明 "概念":类似java.python中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名: "好处":

  • 浅谈MySQL函数

    目录 1.数学函数 2.字符串函数 3.日期函数 4.加密函数  主要MySQL函数介绍又以下: 数学函数 字符串函数 时间函数 加密函数 1.数学函数 注意: 每个函数前面都需要加 : SELECT . 数学函数 ABS() 返回绝对值 如: (-100) 值 : 100 PI() 返回Π的圆规率 如 (不用写) 值 : 3.1415926 CEIL() 向上取整数 如:(3.14) 值 :4 ( 注意第三个是i ) FLOOR() 向下取整数 如: (3.14) 值 :3 POW(x,y)

  • mysql函数全面总结

    目录 1.MySQL中常用字符串函数 2.数值函数 3.日期和时间函数 4.流程函数 5.其他常用函数 1.MySQL中常用字符串函数 函数 功能 CANCAT(S1,S2,-Sn)  连接 S1,S2,-Sn 为一个字符串 INSERT(str,x,y,instr)  将字符串 str 从第 x 位置开始,y 个字符长的子串替换为字符串 instr LOWER(str)  将字符串 str 中所有字符变为小写 UPPER(str)  将字符串 str 中所有字符变为大写  LEFT(str ,

  • MySQL数据库基础篇SQL窗口函数示例解析教程

    MySQL数据库基础篇SQL窗口函数示例解析教程

    目录 本文简介 正文介绍 聚合函数 + over() 排序函数 + over() ntile()函数 + over() 偏移函数 + over() 本文简介 前段时间,黄同学写了一篇<MySQL窗口实战>文章(文章如下),但是里面大多数是以实战练习为主,没有做详细的解释. 传送门:MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费 于是,私信了月牙美女,看看她能否写一篇<窗口函数基础篇>,正好和之前那篇文章配套.这不,很快她就写好了,今天就给大家做一个分享,旨在和大家交流学

  • MySQL数据库基础篇之入门基础命令小结

    本文实例讲述了MySQL数据库入门基础命令.分享给大家供大家参考,具体如下: 在日常工作与学习中,无论是开发.运维.还是测试,对于数据库的学习是不可避免的,同时也是日常工作的必备技术之一.在互联网公司,开源产品线比较多,互联网企业所用的数据库占比较重的还是MySQL. 在刚刚出炉的 2019 年3月份数据库流行度排行榜上,第一梯队的前三个数据库产品都获得了显著的加分增长. 其中 Oracle 上升了15.12分,MySQL上升了30.96分,SQL Server则上升了 7.79分.以下是前20

  • MySQL数据库基础入门之常用命令小结

    本文实例讲述了MySQL数据库基础入门之常用命令.分享给大家供大家参考,具体如下: 接上一篇:MySQL数据库主从同步实战过程 mysql命令介绍 mysql 是数据库管理命令 通过mysql --help来查看相关参数及使用说明 mysql --help #mysql数据库管理命令 Usage: mysql [OPTIONS] [database] #语法格式 --help #查看帮助文档 --auto-rehash #自动补全功能 -A, --no-auto-rehash #不需自动补全 -

  • MYSQL数据库基础之Join操作原理

    MYSQL数据库基础之Join操作原理

    Join使用的是Nested-Loop Join算法,Nested-Loop Join有三种 select * from t1 join t2 on t1.a = t2.a; -- a 100条数据, b 1000条数据 Simple Nested-Loop Join 会遍历t1全表,t1作为驱动表,t1中的每一条数据都会到t2中做一次全表查询,该过程会比较100*1000次. 每次在t2中做全表查询时,全表扫描可就不保证在内存里了,Buffer Pool会淘汰,有可能在磁盘. Block Ne

  • Python实现mysql数据库中的SQL文件生成和导入

    Python实现mysql数据库中的SQL文件生成和导入

    目录 1.将mysql数据导出到SQL文件中(数据库存在的情况) 2.将现有的sql文件数据导入到数据库中(前提数据库存在) 3.利用Navicat导出SQL文件和导入SQL文件 1)从数据库导出SQL文件 2)导入SQL文件到数据库 1.将mysql数据导出到SQL文件中(数据库存在的情况) 主要需要修改数据库的相关信息,端口号.用户名.密码等 其中数据库得存在,不然会报错 : #!/usr/bin/env python # -*- coding: utf-8 -*- # @descripti

  • MySQL数据库基础学习之JSON函数各类操作详解

    目录 前言 一.JSON语法规则 二.JSON函数 1.JSON_CONTAINS(json_doc,value)函数 2.JSON_SEARCH()函数 3.JSON_PRETTY(json_doc)函数 4.JSON_DEPTH(json_doc)函数 5.JSON_LENGTH(json_doc[,path])函数 6.JSON_KEYS(json_doc[,path])函数 7. JSON_INSERT(json_doc,path,val[,path,val] ...)函数 8.JSON

  • MySql数据库基础知识点总结

    本文实例讲述了MySql数据库基础知识点.分享给大家供大家参考,具体如下: 数据库基本操作 1)创建数据库 基本语法: create database 数据库名称 : 创建应该名称为itcast的数据库.sql语法如下: ``create database ` itcast `:`` 需要主要的是,为了避免用户自定义的名称与系统命令冲突,最好使用反引号(``)包括数据库名称/字段名称和数据表名称 如果创建数据库存在,则程序会报错,为了防止此情况发生,再创建数据库可以使用"if not exist

  • IDEA连接MySQL数据库并执行SQL语句使用数据图文详解

    目录 一.IDEA连接MySQL数据库 (一)首先新建普通Java项目 (二)连接数据库 二.使用数据库的数据 (一)新建Java类 Test (二)下载MySQL驱动Jar包 (三)返回IDEA,新建文件夹lib (四)非常重要(添加到库) (五)编写Test类 (六)运行主函数,得到数据 三.总结 一.IDEA连接MySQL数据库 (一)首先新建普通Java项目 (二)连接数据库 1.点击右侧DataBase 2.点击加号,找到MySQL,添加数据库 3.输入用户名和密码,点击Test Co

  • MySQL数据库基础命令大全(收藏)

    整理了一下mysql基础命令,分享一下 #启动,停止,重启MySql服务 service mysql start|stop|restart /etc/init.d/mysql restart|start|restart #连接MySQL数据库 mysql -h127.0.0.1 -uroot -p123456 #查看MySQL版本 select version() #显示数据库 show databases #使用数据库 use [database] #当前选择的数据库 select datab

  • Mysql数据库之常用sql语句进阶与总结

    本文实例讲述了Mysql数据库之常用sql语句.分享给大家供大家参考,具体如下: 前面讲述了Mysql sql基本语句.这里继续总结一下SQL语句的进阶内容. SQL语句进阶 1.查询字段: ----查询所有字段 select * from 表名; ----查询指定字段 select 字段名,字段名- from 表名; ----多数据表连接查询时 select 表名.字段名,表名.字段名 - from 表名; ----使用as给表起别名 select 表别名.字段名 from 表名 as 表别名

随机推荐