SQL窗口函数OVER用法介绍_F11 - 专业站长和开发者的学习网站

分享到

SQL窗口函数OVER用法介绍

Mysql 来源：互联网作者：佚名发布时间：2022-08-26 20:38:49 人浏览

摘要

OVER的定义 OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。语法 OVER ( [ PARTITION BY column ] [ OR

OVER的定义

OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

语法

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] [ROWS|RANGE BETWEEN 边界规则1 and 边界规则2])

PARTITION BY 子句进行分组；

ORDER BY 子句进行排序；

ROWS|RANGE 框架是对窗口进行进一步的分区，框架有两种范围限定方式：一种是使用ROWS子句，通过指定当前行之前或之后的固定数目的行来限制分区中的行数；另一种是RANGE子句，按照排序列的当前值，根据相同值来确定分区中的行数。
窗口函数OVER()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。

RANGE表示按照值的范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义；边界规则的可取值见下表

用法

OVER开窗函数必须与聚合函数或排序函数一起使用，聚合函数一般指SUM(),MAX(),MIN,COUNT(),AVG()等常见函数。排序函数一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。

示例

创建测试表

create table Employee

(

ID INT PRIMARY KEY

,Name VARCHAR(20)

,GroupName VARCHAR(20)

,Salary INT

) INSERT into Employee VALUES(1, '小明', '开发部', 8000), (4, '小张', '开发部', 7600), (5, '小白', '开发部', 7000), (8, '小王', '财务部', 5000), (9, null, '财务部', NULL), (15, '小刘', '财务部', 6000), (16, '小高', '行政部', 4500), (18, '小王', '行政部', 4000), (23, '小李', '行政部', 4500), (29, '小吴', '行政部', 4700)

;

SUM后的开窗函数

select *

,SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资

,SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资

,SUM(Salary) OVER(ORDER BY ID) 累计工资

,SUM(Salary) OVER() 总工资

from Employee

结果如下：

其中开窗函数的每个含义不同，我们来具体解读一下：

1	SUM(Salary) OVER (PARTITION BY Groupname)

只对PARTITION BY后面的列Groupname进行分组，分组后求解Salary的和。

1	SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)

对PARTITION BY后面的列Groupname进行分组，然后按ORDER BY 后的ID进行排序，然后在组内对Salary进行累加处理。

1	SUM(Salary) OVER (ORDER BY ID)

只对ORDER BY 后的ID内容进行排序，对排完序后的Salary进行累加处理。

1	SUM(Salary) OVER ()

对Salary进行汇总处理

排序

ROW_NUMBER()

定义：ROW_NUMBER()函数作用就是将SELECT查询到的数据进行排序，每一条数据加一个序号

select *

,ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 班内排序

,ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS 总排序

from Scores

;

RANK()

select ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK]

from Scores

;

select RANK() OVER (ORDER BY SCORE DESC) AS [RANK]

from Scores

;

NTILE()

定义：NTILE(N)函数是将数据分成N块，然后再添加1~N的序号。

select *

,NTILE(1) OVER (ORDER BY SCORE DESC) AS 分区后排序

from Scores

;

select *

,NTILE(2) OVER (ORDER BY SCORE DESC) AS 分区后排序

from Scores

;

select *

,NTILE(3) OVER (ORDER BY SCORE DESC) AS 分区后排序

from Scores

;

ROWS|RANGE

select *

,SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID ROWS between CURRENT ROW and 5 FOLLOWING) 从当前行开始后5行的和

from Employee

参考

https://www.modb.pro/db/42912

您可能感兴趣的文章 :

原文链接 : https://blog.csdn.net/m0_37953759/article/details/123076115

Tag : sql(75)函数(71)

深入了解MySQL中的慢查询

一、什么是慢查询什么是MySQL慢查询呢？其实就是查询的SQL语句耗费较长的时间。具体耗费多久算慢查询呢？这其实因人而异，有些公司慢
MySQL中with rollup的用法及说明

MySQL with rollup的用法当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。而在groupby后面还可以加入withcube和withrollup等关
mysql分组统计并求出百分比的方法

mysql分组统计并求出百分比 1、mysql 分组统计并列出百分比 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 SELECT point_id, pname_cn, play_
30种SQL语句优化的方法总结

1）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2）应尽量避免在 where 子句中使用!=或操作符
达梦数据库获取SQL实际执行计划的方法

环境说明：操作系统：银河麒麟V10 数据库：DM8 相关关键字：DM数据库、SQL实际执行计划一、set autotrace trace disql下执行set autotrace trace开启
MySQL数据库约束的介绍

基本介绍约束用于确保数据库的数据满足特定的商业规则在mysql中，约束包括：not null，unique，primary key，foreign key 和check5种 1.primary key(主键
MySQL索引的介绍

1. MySQL 索引的最左前缀原则左前缀原则是联合索引在使用时要遵循的原则，查询索引可以使用联合索引的一部分，但是必须从最左侧开始。
windows下Mysql多实例部署的操作方法

当存在多个项目的时候，需要同时部署时，且只有一台服务器时，哪么就需要部署Mysql多个实例，原理很简单，多个mysql服务运行使用不同的
MySQL客户端/服务器运行架构介绍

之前对MySQL的认知只限于会写些SQL，本篇开始进行对MySQL进行深入的学习，记录和整理下自己对MySQL不熟悉的地方。如果有需要可以关注我的
mysql8.0主从复制搭建与配置方案

mysql主从搭建环境：ubuntu20.04.1，mysql：8.0.22。主：192.168.87.3 备：192.168.87.6 安装数据库 1 2 3 sudo apt-get install mysql-server sudo apt-get install mysql