开窗函数 函数() OVER()

时间：2024-01-05 09:07:02
-- 初始化

CREATE TABLE T_Person

(FName VARCHAR2(20), FCity VARCHAR2(20), FAge INT, FSalary INT);

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 COUNT(*) FROM T_Person;

-- 查询每个工资小于5000元的员工信息，并且在每行中都显示所有工资小于5000元的员工个数,报错：列名无效，不包含在聚合函数或者GROUP BY子句中

SELECT FCity, FAge, COUNT(*) FROM T_Person WHERE FSalary <5000;

-- 修改后得到的COUNT(*)不是我想要的

SELECT FCity, FAge,COUNT(*) FROM T_Person WHERE FSalary <5000 GROUP BY FCity,FAge;

-- 通过子查询来解决这个问题

SELECT FCity, FAge, (SELECT COUNT(*) FROM T_Person WHERE FSalary < 5000) FROM T_Person WHERE FSalary <5000;

-- 使用开窗函数

SELECT FCity, FAge, COUNT(*) OVER() FROM T_Person WHERE FSalary <5000;

与聚合函数不同的是，开窗函数在聚合函数后增加了一个OVER关键字。开窗函数的调用格式为：函数名(列) OVER(选项)，over关键字把函数当成开窗函数而不是

聚合函数，SQL标准允许将所有聚合函数用做开窗函数，使用OVER关键字来区分这两种用法。OVER()对于查询结果的每一行都返回所有符合条件的行的条数，OVER

关键字后的括号中还经常添加选项，用以改变对聚合运算的窗口范围

开窗函数的OVER关键字后的括号中，可以使用PARTITION BY子句来定义行的分区，从而进行聚合计算。与GROUP BY不同，PARTITION BY子句创建的分区是独立于结

果集的，创建的分区只是供进行聚合运算的，而且不同的开窗函数所创建的分区也互不影响

-- COUNT(*) OVER (PARTITION BY FCity) 表示对结果集按照FCity进行分区，并且计算当期行所属的组的聚合计算结果，多个开窗函数不会互相干扰

SELECT FName, FCity, FAge, FSalary, COUNT(*) OVER (PARTITION BY FCity) FROM T_Person;

SELECT FName, FCity, FAge, FSalary, COUNT(*) OVER (PARTITION BY FCity), COUNT(*) OVER(PARTITION BY FAge) FROM T_Person;

开窗函数可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，而且有的开窗函数还要求必须指定排序规则。使用 ORDER BY 子句可以对结果集按照

指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。 ORDER BY 子句的语法为： ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规

则2

RANGE 按照值得范围进行范围的定义，而ROWS表示按照行的范围进行范围的定义,边界规则得取值表：

	CURRENT ROW : 当前行

	N PRECEDING ：前N行

	UNBOUNDED PRECEDING : 一直到第一条记录

	N FOLLOWING ：后N行

	UNBOUNDED FOLLOWING : 一直到最后一条记录

RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2 用来定位聚合计算范围

-- 表示按照FSalary进行排序，然后计算从第一行到当前行的和，这样的额计算结果就是按照工资进行排序的工资值得累积和

SELECT FName, FSalary, SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW） FROM T_Person

-- 表示按照FSalary进行排序，然后计算从第一行到当前行工资范围内的和

SELECT FName, FSalary, SUM(FSalary) OVER(ORDER BY FSalary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T_Person

-- 表示按照FSalary进行排序，然后计算从当前行前两行到当前行后2行的工资和

SELECT FName, FSalary, SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) FROM T_Person

-- 表示按照FSalary进行排序，然后计算从当前行后1行到当前行后3行的工资和

SELECT FName, FSalary, SUM(FSalary) OVER(ORDER BY FSalary ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) FROM T_Person

-- 使用行号为范围，第一行到当前行是默认的定位框架，可以省略

SELECT FName, FSalary, FAge, MAX(FSalary) OVER(PARTITION BY FAge ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T_Pers

on

SELECT FName, FSalary, FAge, MAX(FSalary) OVER(PARTITION BY FAge ORDER BY FSalary) FROM T_Person

除了在聚合函数使用开窗函数之外，一些高级函数也可以。如：RANK()、 DENSE_RANK()、 ROW_NUMBER()等

　　

相关文章