开窗函数 函数() OVER()

-- 初始化
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()等

  

上一篇:angular懒加载机制 刷新后无法回退解决方案


下一篇:vue-router路由懒加载(解决vue项目首次加载慢)