SQL中的开窗函数详解可代替聚合函数使用

这篇文章主要介绍了SQL中的开窗函数详解可代替聚合函数使用,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下

在没学习开窗函数之前,我们都知道,用了分组之后,查询字段就只能是分组字段和聚合的字段,这带来了极大的不方便,有时我们查询时需要分组,又需要查询不分组的字段,每次都要又到子查询,这样显得sql语句复杂难懂,给维护代码的人带来很大的痛苦,然而开窗函数出现了,曙光也来临了。如果要想更具体了解开窗函数,请看书《程序员的SQL金典》,开窗函数在mysql不能使用。

开窗函数与聚合函数一样,都是对行的集合组进行聚合计算。它用于为行定义一个窗口(这里的窗口是指运算将要操作的行的集合),它对一组值进行操作,不需要使用group by语句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。定义看不懂不要紧,会用就行。

举个简单例子 查询每个工资小于5000的员工信息(姓名,城市 年龄 薪水),并且显示小于5000的员工个数,尝试使用下面语句:

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) FROM T_Person WHERE FSALARY<5000

消息 8120,级别 16,状态 1,第 1 行
选择列表中的列 'T_Person.FName' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。

可以使用子查询实现,语句:

SELECT FName, FCITY, FAGE, FSalary, ( SELECT COUNT(FName) FROM T_Person WHERE FSALARY<5000 ) PersonNum FROM T_Person
WHERE FSALARY<5000

结果:

SQL中的开窗函数详解可代替聚合函数使用

使用开窗函数实现,查询结果一模一样,就不粘贴了:

SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) OVER() as PersonNum FROM T_Person
WHERE FSALARY<5000

1.开窗函数格式:函数名(列) OVER(选项)

2.聚合开窗函数格式:聚合函数(列) OVER(PARTITION BY 字段)

over关键字把聚合函数当成聚合开窗函数而不是聚合函数,SQL标准允许将所有的聚合函数用做聚合开窗函数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号为空,则开窗函数会对结果集合的所有行进行聚合运算。

PARTITION BY来定义行的分区来进行聚合运算,与group by 不同,partition by 字句创建的分区是独立于结果集的,创建的分区只是用于进行聚合运算,而且不同的开窗函数所创建的分区不互相影响,例如:查询所有人员的信息,并查询所属城市的人员数以及同年龄的人员数:

SELECT FName,FCITY, FAGE, FSalary, COUNT(FName) OVER(PARTITION BY FCITY) CityNum,
COUNT(FName) OVER(PARTITION BY FAGE) AgeNum FROM T_Person ORDER by FCITY

SQL中的开窗函数详解可代替聚合函数使用

查询所有人员的信息,并查询所属城市的人员数,每个城市的人按照年龄排序语句:

SELECT FName,FCITY, FAGE, FSalary, COUNT(FName) OVER(PARTITION BY FCITY ORDER BY FAGE) CityNum FROM T_Person

SQL中的开窗函数详解可代替聚合函数使用

3.排序开窗函数格式:排序函数() OVER(ORDER BY 字段)

(1)主要函数有ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()

ROW_NUMBER() 加行号,一般可以用于分页查询(现在被offset fetch取代 ),对于没有主键列的表加行号作用很明显,删除重复数据等。

按照薪水高低给所有人员排序,同样薪水的排名不一样,可以用row_number(),

with a as
(
SELECT FName, FSalary, FCity, FAge, ROW_NUMBER() over(ORDER BY FSalary) as RowNum FROM T_Person
)
SELECT * FROM a

SQL中的开窗函数详解可代替聚合函数使用

使用rank()将每个城市的薪水排行,值一样的同一个排名,出现两个第一名的时候,排在两个第一名后的排名将是第三名

SELECT FName, FSalary, FCity, FAge, RANK() over(PARTITION BY FCITY ORDER BY FSalary) as RankNum FROM T_Person

SQL中的开窗函数详解可代替聚合函数使用

使用dense_rank()将每个城市的薪水排行,值一样的同一个排名,出现两个第一名的时候,排在两个第一名后的排名将是第三名

SQL中的开窗函数详解可代替聚合函数使用

ntile(数字) over(order by 字段):数字表示一组多少个数,并根据数量得出分组的数量

SELECT *,NTILE(5) OVER(ORDER BY FSalary) AS NileNum FROM T_Person

SQL中的开窗函数详解可代替聚合函数使用

总结

到此这篇关于SQL中的开窗函数详解可代替聚合函数使用的文章就介绍到这了,更多相关SQL 开窗函数内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持钦钦技术栈!

原文链接:https://www.cnblogs.com/hobelee/p/12495333.html

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/24085/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-09-21 2:47:24
下一篇 2022-09-21 2:47:33

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!