开窗函数有浅入深详解（一）
在开窗函数出现之前存在着很多用 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 关键字。
开窗函数的调用格式为：
函数名(列) OVER(选项)
OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用OVER 关键字来区分这两种用法。
在上边的例子中，开窗函数COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。
如果OVER关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。
总结：上述讲述的是开窗函数的基本用法，希望对大家有所帮助！
相关推荐
-
SQLserver 实现分组统计查询（按月、小时分组）
设置AccessCount字段可以根据需求在特定的时间范围内如果是相同IP访问就在AccessCount上累加. 复制代码 代码如下: Create table Counter ( CounterID int identity(1,1) not null, IP varchar(20), AccessDateTime datetime, AccessCount int ) 该表在这儿只是演示使用,所以只提供了最基本的字段 现在往表中插入几条记录 insert into Counter selec
-
SQL2005 学习笔记 窗口函数(OVER)
1.简介: SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合,通过它可以非常方便地累计总数.移动平均值.以及执行其它计算. 窗口函数功能非常强大,使用起来也十分容易.可以使用这个技巧立即得到大量统计值. 窗口是用户指定的一组行. 开窗函数计算从窗口派生的结果集中各行的值. 2.适用范围: 排名开窗函数和聚合开窗函数. 也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用 OVER子句前面必须是排名函数或者是聚合函数 3.例题: 复制代码 代码如下: --建立订单表 c
-
SQL2005利用ROW_NUMBER() OVER实现分页功能
1.首先介绍ROW_NUMBER() OVER的基本用法 2.看一下实例数据 初始化数据 create table employee (empid int ,deptid int ,salary decimal(10,2)) insert into employee values(1,10,5500.00) insert into employee values(2,10,4500.00) insert into employee values(3,20,1900.00) insert into
-
Sql Server 分组统计并合计总数及WITH ROLLUP应用
WITH ROLLUP 在生成包含小计和合计的报表时,ROLLUP 运算符很有用.ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集. 复制代码 代码如下: SELECT [Source], COUNT(*) AS OrderTotal FROM [ExternalOrder] Where OrderStatus=1 AND (CheckPayment=1 ) and TicketDate >= '2012-11-1' AND TicketDate < '2012-12-1
-
Sql Server：多行合并成一行，并做分组统计的两个方法
复制代码 代码如下: --创建 test 表 ,插入数据 CREATE TABLE test(code varchar(50), [values] varchar(10),[count] int)INSERT test SELECT '001', 'aa',1UNION ALL SELECT '001', 'bb',2UNION ALL SELECT '002', 'aaa',4UNION ALL SELECT '002', 'bbb',5UNION ALL SELECT '002', 'ccc
-
sql server如何利用开窗函数over()进行分组统计
这是一道常见的面试题,在实际项目中经常会用到. 需求:求出以产品类别为分组,各个分组里价格最高的产品信息. 实现过程如下: declare @t table( ProductID int, ProductName varchar(20), ProductType varchar(20), Price int) --测试数据 insert @t select 1,'name1','P1',3 union all select 2,'name2','P1',5 union all select 3,
-
SQL进行排序、分组、统计的10个新技巧分享
1.使用排序使数据有序通常,你的所有数据真正需要的仅仅是按某种顺序排列.SQL的ORDER BY语句可以以字母或数字顺序组织数据.因此,相似的值按组排序在一起.然而,这个分组时排序的结果,并不是真的分组.ORDER BY显示每条记录而分组可能代表很多记录.2.进行分组除去重复值 排序和分组之间的最大区别是:排序的数据显示所有记录(在限定标准范围之内),而分组数据不是显示所有记录.GROUP BY语句对于同样的值只显示一条记录.例如,下面的语句中的GROUP BY语句对数据源中重复出现的数据只返回
-
sqlserver2005使用row_number() over分页的实现方法
语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) <BR> 例子: 复制代码 代码如下: select * from ( select *, ROW_NUMBER() OVER(Order by a.CreateTime DESC ) AS RowNumber from table_name as a ) as b where RowNumber BETWEEN 1 and 5 将会返回table表 其中有一列名字为 RowNu
-
开窗函数有浅入深详解（一）
在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成.为了解决这些问题,在2003年ISO SQL标准加入了开窗函数,开窗函数的使用使得这些经典的难题可以被轻松的解决. 目前在 MSSQLServer.Oracle.DB2 等主流数据库中都提供了对开窗函数的支持,不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持. 为了更加清楚地理解,我们来建表并进行相关的查询(截图为MSSQLServer中的结果) MYSQL,MSSQLServe
-
Sql Server 开窗函数Over()的使用实例详解
利用over(),将统计信息计算出来,然后直接筛选结果集 declare @t table( ProductID int, ProductName varchar(20), ProductType varchar(20), Price int) insert @t select 1,'name1','P1',3 union all select 2,'name2','P1',5 union all select 3,'name3','P2',4 union all select 4,'name4
-
Java对象深复制与浅复制实例详解
Java对象深复制与浅复制实例详解 我们在遇到一些业务场景的时候经常需要对对象进行复制,对于对象的复制一般有两种方式,深复制和浅复制 浅复制:对象的复制仅是对象本身,对象引用的其它对方并不会复制. 深复制:对象的复制包含对象引用的对象. Java所有对象的基类提供了clone方法,但是这个方法是protected native修饰,因此只暴露给之类去重写,外部是无法直接调用的. 我们现在来测试两种复制,首选是浅复制,浅复制要实现Cloneable接口. // 课程对象 class Class
-
Python带你从浅入深探究Tuple(基础篇)
元组 Python中的元组容器序列(tuple)与列表容器序列(list)具有极大的相似之处,因此也常被称为不可变的列表. 但是两者之间也有很多的差距,元组侧重于数据的展示,而列表侧重于数据的存储与操作. 它们非常相似,虽然都可以存储任意类型的数据,但是一个元组定义好之后就不能够再进行修改. 元组特性 元组的特点: 元组属于容器序列 元组属于不可变类型 元组底层由顺序存储组成,而顺序存储是线性结构的一种 基本声明 以下是使用类实例化的形式进行对象声明: tup = tuple((1, 2, 3,
-
JavaScript学习小结之被嫌弃的eval函数和with语句实例详解
前面的话 eval和with经常被嫌弃,好像它们的存在就是错误.在CSS中,表格被嫌弃,在网页中只是用表格来展示数据,而不是做布局,都可能被斥为不规范,矫枉过正.那关于eval和with到底是什么情况呢?本文将详细介绍eval()函数和with语句 eval 定义 eval()是一个全局函数,javascript通过eval()来解释运行由javascript源代码组成的字符串 var result = eval('3+2'); console.log(result,typeof result)
-
Linux中stat函数和stat命令使用详解
stat函数和stat命令 linux文件里的[inode = index node]解释:要理解inode必须了解磁盘和[目录项],inode实际是连接[目录项]和磁盘的中间物质. 图里的大圈代表硬件的磁盘,里面的小圈代表某个文件存储在磁盘上了. [inode = index node]的node(承载node信息的结构体是:stat,stat的定义在后面 )里面有: 文件大小 文件的最后修改时间 文件的所属用户 文件的权限 硬链接计数(ls -l 显示出来的数字) 块位置:指定文件存储在磁盘
-
Go语言学习之函数的定义与使用详解
目录 1.函数定义 2.多值返回 3.引用传递 4.函数作为实参使用 5.匿名函数 1.函数定义 函数的定义和java一样,使用{}进行包裹,并且要明确入参类型以及返回类型. 样例代码如下: func min(num1, num2 int) int { if num1 <= num2 { return num1 } else { return num2 } } func main() { fmt.Printf("max = %d\n", min(10, 12)) } 执行结果 m
-
React.memo函数中的参数示例详解
目录 React.memo?这是个啥? React.memo的第一个参数 父组件 子组件 React.memo优化 React.memo的第二个参数 父组件 子组件 React.memo优化 父组件 子组件 小结 React.memo?这是个啥? 按照官方文档的解释: 如果你的函数组件在给定相同 props 的情况下渲染相同的结果,那么你可以通过将其包装在 React.memo 中调用,以此通过记忆组件渲染结果的方式来提高组件的性能表现.这意味着在这种情况下,React 将跳过渲染组件的操作并直
-
PHP中filter函数校验数据的方法详解
介绍PHP中filter函数校验数据的方法详解,PHP过滤器包含两种类型:Validation用来验证验证项是否合法 .Sanitization用来格式化被验证的项目,因此它可能会修改验证项的值,将不合法的字符删除. input_filters_list() 用来列出当前系统所支持的所有过滤器. 复制代码 代码如下: <?php foreach(filter_list() as $id => $filter) { echo $filter.' '.filter_id($filter).
-
JavaScript中匿名函数的用法及优缺点详解
匿名函数可以有效的保证在页面上写入Javascript,而不会造成全局变量的污染. 这在给一个不是很熟悉的页面增加Javascript时非常有效,也很优美. 一.什么是匿名函数? 在Javascript定义一个函数一般有如下三种方式: 函数关键字(function)语句: function fnMethodName(x){alert(x);} 函数字面量(Function Literals): var fnMethodName = function(x){alert(x);} Function(