深入浅出谈开窗函数(一)

深入浅出谈开窗函数(一)

大家好,又见面了,我是全栈君,祝每个程序员都可以多学几门语言。

        在开窗函数出现之前存在着非常多用 SQL 语句非常难解决的问题,非常多都要通过复杂的相关子查询或者存储过程来完毕。为了解决这些问题,在2003年ISO  SQL标准添�了开窗函数,开窗函数的使用使得这些经典的难题能够被轻松的解决。眼下在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持,只是非常遗憾的是 MYSQL 临时还未对开窗函数给予支持。 

为了更加清楚地理解,我们来建表并进行相关的查询(截图为MSSQLServer中的结果)

        MYSQL,MSSQLServer,DB2: 
        

CREATE TABLE T_Person 
(
	FName VARCHAR(20),
	FCity VARCHAR(20), 
	FAge INT,
	FSalary INT
) 

        Oracle: 

        CREATE TABLE T_Person (FName VARCHAR2(20),FCity VARCHAR2(20), FAge INT,FSalary INT) 

注:下面结果仅仅在MSSQLServer中演示:

T_Person 表保存了人员信息,FName 字段为人员姓名,FCity 字段为人员所在的城市名,

FAge  字段为人员年龄,FSalary 字段为人员工资。然后运行以下的SQL语句向 T_Person

表中插入一些演示数据: 

       

INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Tom','BeiJing',20,3000); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Tim','ChengDu',21,4000); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Jim','BeiJing',22,3500); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Lily','London',21,2000); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('John','NewYork',22,1000); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('YaoMing','BeiJing',20,3000); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Swing','London',22,2000); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Guo','NewYork',20,2800); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('YuQian','BeiJing',24,8000); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Ketty','London',25,8500); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Kitty','ChengDu',25,3000); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Merry','BeiJing',23,3500); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Smith','ChengDu',30,3000); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary)
VALUES('Bill','BeiJing',25,2000); 
INSERT INTO T_Person(FName,FCity,FAge,FSalary) 
VALUES('Jerry','NewYork',24,3300); 

查看表中的内容:

select * from T_Person

深入浅出谈开窗函数(一)

开窗函数简单介绍 
 
合函数一样,开窗函数也是对行集组进行聚合计算,可是它不像普通聚合函数那样

每组仅仅返回一个值,开窗函数能够为每组返回多个值,由于开窗函数所运行聚合计算的行

集组是窗体。在ISO SQL规定了这种函数为开窗函数,在 Oracle中则被称为分析函数,

而在DB2中则被称为OLAP函数。  

要计算全部人员的总数,我们能够运行以下的 SQL语句: 
SELECT COUNT(*) FROM T_Person 
 
       除了这样的较简单的使用方式, 有时须要从不在聚合函数中的行中訪问这些聚合计

算的值。比方我们想查询每一个工资小于 5000元的员工信息(城市以及年龄) ,而且在

每行中都显示全部工资小于5000元的员工个数,尝试编写以下的 SQL语句: 
SELECT FCITY , FAGE , COUNT(*)  
FROM T_Person 
HERE FSALARY<5000 
  运行上面的SQL以后我们会得到以下的错误信息: 
选择列表中的列  ‘T_Person.FCity’ 无效,由于该列没有包括在聚合函数或 

 GROUP BY 子句中。 
  这是由于全部不包括在聚合函数中的列必须声明在GROUP BY 子句中,

能够进行例如以下改动: 
SELECT FCITY, FAGE, COUNT(*)  
FROM T_Person 
WHERE FSALARY<5000 
GROUP BY FCITY , FAGE 
  运行完成我们就能在输出结果中看到以下的运行结果: 
               

   深入浅出谈开窗函数(一)

  这个运行结果与我们想像的是全然不同的,这是由于GROUP  BY子句对结果集

进行了分组,所以聚合函数进行计算的对象不再是全部的结果集,而是每个分组。

 能够通过子查询来解决问题,SQL例如以下: 
SELECT FCITY , FAGE ,  

  SELECT COUNT(* ) FROM T_Person 
  WHERE FSALARY<5000 

FROM T_Person 
WHERE FSALARY<5000 
  运行完成我们就能在输出结果中看到以下的运行结果:

深入浅出谈开窗函数(一)

  尽管使用子查询可以解决问题,可是子查询的使用很麻烦,使用开窗函数

则能够大大简化实现,以下的SQL语句展示了假设使用开窗函数来实现相同的效果: 

SELECT FCITY , FAGE , COUNT(*) OVER() 
FROM T_Person 
WHERE FSALARY<5000 
  运行完成我们就能在输出结果中看到以下的运行结果: 

深入浅出谈开窗函数(一)

能够看到与聚合函数不同的是,开窗函数在聚合函数后添加�了一个OVER keyword。 
开窗函数的调用格式为: 
函数名(列) OVER(选项) 
    OVER   keyword表示把函数当成开窗函数而不是聚合函数。SQL  标准同意将全部聚 
合函数用做开窗函数,使用OVER keyword来区分这两种使用方法。 
    在上边的样例中,开窗函数COUNT(*) OVER()对于查询结果的每一行都返回全部 
符合条件的行的条数。OVERkeyword后的括号里还常常加入�选项用以改变进行聚合运算的窗 
口范围。假设OVERkeyword后的括号里的选项为空,则开窗函数会对结果集中的全部行进行 
聚合运算。  
  

总结:上述讲述的是开窗函数的基本使用方法,希望对大家有所帮助!

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/118456.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 【C语言】求最小公倍数和最大公约数(辗转相除法)

    【C语言】求最小公倍数和最大公约数(辗转相除法)用到的名词:最小公倍数,最大公约数,辗转相除法一、名词解释:1).最小公倍数:最小公倍数(LeastCommonMultiple,LCM),如果有一个自然数a能被自然数b整除,则称a为b的倍数,b为a的约数,对于两个整数来说,指该两数共有倍数中最小的一个。计算最小公倍数时,通常会借助最大公约数来辅助计算。 最小公倍数=两数的乘积/最大公约(因)数,解题时要避免和最大公约(因)…

  • 8年软件测试工程师感悟——写给还在迷茫中的朋友

    8年软件测试工程师感悟——写给还在迷茫中的朋友这两天和朋友谈到软件测试的发展,其实软件测试已经在不知不觉中发生了非常大的改变,前几年的软件测试行业还是一个风口,随着不断地转行人员以及毕业的大学生疯狂地涌入软件测试行业,目前软件测试行业“缺口”已经基本饱和。当然,我说的是最基础的功能测试的岗位需求已经很少了,而自动化、性能、安全乃至于以后可能出现的大数据测试、AI测试仍存在着非常多的机会。“长江后浪推前浪,前浪死在沙滩上”,曾经一句让人…

  • flask 的 jsonify 自动排序问题

    flask 的 jsonify 自动排序问题背景·Python在写接口的时候有时需要返回json格式的数据给客户端·最简单的方式就是用flask的jsonify,能直接将字典格式化为json的形式进行传输例如fromflaskimportjsonify………defreturn_success(data):”””返回成功信息”””returnjsonify(data)通过以上jsonify的方式大部分需求是能够搞定的问题引发但是有时候我们要传递的json格式可能

  • CCNA学习指南 第七章 下载

    CCNA学习指南 第七章 下载

  • java输出结果保留两位小数,经典好文

    java输出结果保留两位小数,经典好文前言面试技巧另外开篇再说,先上面试干货吧。面试的题目并不一定有严格的顺序关系,有的是从前一个问题延伸而来,(探究的是一个知识的深度),有的是考察面试者的知识广度、有的纯粹是我想到哪里写到哪里的啦。。不要太在意哈,最近工作有点忙。基础知识RabbitMQ是一个开源的消息代理和队列服务器,用来通过普通协议在完全不同的应用之间共享数据,它是使用Erlang语言来编写的,并且是基于AMQP协议的;RabbitMQ高性能的原因Erlang语言在交换机的交互方面性能优秀的(Erlang语言最初在于交换机领域

  • RXJava原理_JavaScript的执行原理

    RXJava原理_JavaScript的执行原理RXJava简单理解首先,rxjava是什么?其实对于刚接触rxjava的宝宝而言,只需要掌握两点:观察者模式异步处理观察上图,清楚生动刻画出了rxjava的观察者模式:开关(被观察者)作为的是事件的产生方(产生“on”和“off”这两个Event),有它发起这起开关的事件。台灯(观察者)作为事件的处理方(处理的是“on”和“off”这两个事件),被动的执行on和off。

    2022年10月22日

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号