ORACLE开窗函数
基本介绍:
Analytic_clause的语法如下：
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]
这里：
query_partition_clause是查询分组子句；
order_by_clause是分组排序子句；
windowing_clause是窗口范围子句。
分析函数在查询结果集确定之后才开始进行计算，Analytic_clause就是用来定义函数怎样对查询结果集进行分组计算的。
根据Oracle对查询和分析函数的处理方法可知，在select和order by子句中都可以使用分析函数。
query_partition_by、order_by_clause和windowing_clause三个子句是可选的，将三个子句分别简记为p，o，w。
合法的组合方式有如下6种：
1). Pow
(query_partition_clause order_by_clause windowing_clause)
分组，排序，定义窗口范围
2). Po
(query_partition_clause order_by_clause)
分组，排序，窗口默认为range between unbounded preceding and current row
3). P
(query_partition_clause)
分组，不排序，没有窗口
4). Ow
(order_by_clause windowing_clause)
分组为整个查询结果集，排序，定义窗口范围
5). O
(order_by_clause)
分组为整个查询结果集，排序，窗口默认为range between unbounded preceding and current row
6). Null
()
分组为整个查询结果集，不排序，没有窗口
因为只有存在order_by_clause，才能有windowing_clause，故不存在如下两种形式的组合：
pw(query_partition_clause windowing_clause)
w(windowing_clause)
总结：
1). 对于是否存在order_by_clause，分析函数可以分为两类，含有order_by_clause的一般称为windowing function，不含的称为reporting function。
2). Windowing function，对查询结果集进行分组，排序，根据窗口范围计算分组中每一行的函数结果。
3). Reporting function，对查询结果集进行分组，不排序，窗口范围为整个分组，在每一个分组内，计算整个分组的函数值，再将函数值分别赋给分组内的每一行。
一、开窗函数
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1、over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (ORDER BY SALARY) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY SALARY
功能：按salary升序排序，统计小于等于当前salary的salary总和。
返回结果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
132 2100 121 50 2100
128 2200 120 50 6500
136 2200 122 50 6500
127 2400 120 50 11300
135 2400 122 50 11300
119 2500 114 30 26300
140 2500 123 50 26300
144 2500 124 50 26300
191 2500 122 50 26300
182 2500 120 50 26300
注意 SALARY为2200、2400和2500行的DD值
2、over（partition by DEPARTMENT_ID）按照部门分区。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY DEPARTMENT_ID
功能：按DEPARTMENT_ID分区，汇总各个部门的SALARY总和。
返回结果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
200 4400 101 10 4400
201 13000 100 20 19000
202 6000 201 20 19000
114 11000 100 30 24900
115 3100 114 30 24900
116 2900 114 30 24900
119 2500 114 30 24900
118 2600 114 30 24900
117 2800 114 30 24900
注意 DEPARTMENT_ID为20，30的DD值
3、over（partition by DEPARTMENT_ID order by SALARY）按照部门分区。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY DEPARTMENT_ID
功能：按DEPARTMENT_ID分区，按SALARY升序排序，统计各个部门内部小于当前SALARY的和。
返回结果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
200 4400 101 10 4400
201 13000 100 20 6000
202 6000 201 20 19000
114 11000 100 30 2500
115 3100 114 30 5100
116 2900 114 30 7900
119 2500 114 30 10800
118 2600 114 30 13900
117 2800 114 30 24900
注意 DEPARTMENT_ID为20、30的DD值和2中的区别
4、over（order by salary range between 50 preceding and 150 following）
SQL> select
empno,sal,mgr,deptno,
sum(sal) over (partition by deptno order by sal
RANGE BETWEEN 0 PRECEDING AND 100 FOLLOWING) dd
from emp;
功能：按DEPARTMENT_ID分区，按SALARY升序排序，汇总当前SALARY到比当前SALARY大100之间的SALARY总和。
返回结果：
EMPNO SAL MGR DEPTNO DD
----- ------ ------ ------- --------
7934 1300 7782 10 1300
7782 2450 7839 10 2450
7839 5000 10 5000
7369 800 7902 20 800
7566 2975 7839 20 5975 3000在2975和（2975+100）之间，故求2975与3000的和
7902 3000 7566 20 3000
7900 950 7698 30 950
7521 1250 7698 30 2500
7654 1250 7698 30 2500
7844 1500 7698 30 3100
7499 1600 7698 30 1600
7698 2850 7839 30 2850
已选择12行。
解释：返回前置行和当前行SALARY相等，后续行比他大100的记录，在SALARY列上求和。
上下边界没有限制：OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
SQL> select empno,sal,mgr,deptno,
sum(sal) over (partition by deptno order by sal
RANGE BETWEEN unbounded PRECEDING AND unbounded FOLLOWING) dd
from emp;
EMPNO SAL MGR DEPTNO DD
---------- ---------- ---------- ---------- ----------
7934 1300 7782 10 8750
7782 2450 7839 10 8750
7839 5000 10 8750
7369 800 7902 20 6775
7566 2975 7839 20 6775
7902 3000 7566 20 6775
7900 950 7698 30 9400
7521 1250 7698 30 9400
7654 1250 7698 30 9400
7844 1500 7698 30 9400
7499 1600 7698 30 9400
7698 2850 7839 30 9400
已选择12行。
5、over（order by salary rows between 1 preceding and 2 following）-- 每行对应的数据窗口是之前行幅度值不超过1，之后行幅度值不超过2
SQL> select empno,sal,mgr,deptno,
sum(sal) over (partition by deptno order by sal
rows BETWEEN 1 PRECEDING AND 2 FOLLOWING) dd
from emp;
返回结果
EMPNO SAL MGR DEPTNO DD
---------- ---------- ---------- ---------- ----------
7934 1300 7782 10 8750
7782 2450 7839 10 8750
7839 5000 10 7450
7369 800 7902 20 6775
7566 2975 7839 20 6775
7902 3000 7566 20 5975
7900 950 7698 30 3450
7521 1250 7698 30 4950
7654 1250 7698 30 5600
7844 1500 7698 30 7200
7499 1600 7698 30 5950
7698 2850 7839 30 4450
已选择12行。
喝水不忘挖井人：http://blog.sina.com.cn/s/blog_70cea94b0100xi46.html
基本介绍:
Analytic_clause的语法如下：
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]
这里：
query_partition_clause是查询分组子句；
order_by_clause是分组排序子句；
windowing_clause是窗口范围子句。
分析函数在查询结果集确定之后才开始进行计算，Analytic_clause就是用来定义函数怎样对查询结果集进行分组计算的。
根据Oracle对查询和分析函数的处理方法可知，在select和order by子句中都可以使用分析函数。
query_partition_by、order_by_clause和windowing_clause三个子句是可选的，将三个子句分别简记为p，o，w。
合法的组合方式有如下6种：
1). Pow
(query_partition_clause order_by_clause windowing_clause)
分组，排序，定义窗口范围
2). Po
(query_partition_clause order_by_clause)
分组，排序，窗口默认为range between unbounded preceding and current row
3). P
(query_partition_clause)
分组，不排序，没有窗口
4). Ow
(order_by_clause windowing_clause)
分组为整个查询结果集，排序，定义窗口范围
5). O
(order_by_clause)
分组为整个查询结果集，排序，窗口默认为range between unbounded preceding and current row
6). Null
()
分组为整个查询结果集，不排序，没有窗口
因为只有存在order_by_clause，才能有windowing_clause，故不存在如下两种形式的组合：
pw(query_partition_clause windowing_clause)
w(windowing_clause)
总结：
1). 对于是否存在order_by_clause，分析函数可以分为两类，含有order_by_clause的一般称为windowing function，不含的称为reporting function。
2). Windowing function，对查询结果集进行分组，排序，根据窗口范围计算分组中每一行的函数结果。
3). Reporting function，对查询结果集进行分组，不排序，窗口范围为整个分组，在每一个分组内，计算整个分组的函数值，再将函数值分别赋给分组内的每一行。
一、开窗函数
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1、over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (ORDER BY SALARY) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY SALARY
功能：按salary升序排序，统计小于等于当前salary的salary总和。
返回结果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
132 2100 121 50 2100
128 2200 120 50 6500
136 2200 122 50 6500
127 2400 120 50 11300
135 2400 122 50 11300
119 2500 114 30 26300
140 2500 123 50 26300
144 2500 124 50 26300
191 2500 122 50 26300
182 2500 120 50 26300
注意 SALARY为2200、2400和2500行的DD值
2、over（partition by DEPARTMENT_ID）按照部门分区。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY DEPARTMENT_ID
功能：按DEPARTMENT_ID分区，汇总各个部门的SALARY总和。
返回结果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
200 4400 101 10 4400
201 13000 100 20 19000
202 6000 201 20 19000
114 11000 100 30 24900
115 3100 114 30 24900
116 2900 114 30 24900
119 2500 114 30 24900
118 2600 114 30 24900
117 2800 114 30 24900
注意 DEPARTMENT_ID为20，30的DD值
3、over（partition by DEPARTMENT_ID order by SALARY）按照部门分区。
SELECT
EMPLOYEE_ID, SALARY, MANAGER_ID, DEPARTMENT_ID,
SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) DD
FROM INFA_TEST.EMPLOYEES EMP
ORDER BY DEPARTMENT_ID
功能：按DEPARTMENT_ID分区，按SALARY升序排序，统计各个部门内部小于当前SALARY的和。
返回结果：
EMPLOYEE_ID SALARY MANAGER_ID DEPARTMENT_ID DD
200 4400 101 10 4400
201 13000 100 20 6000
202 6000 201 20 19000
114 11000 100 30 2500
115 3100 114 30 5100
116 2900 114 30 7900
119 2500 114 30 10800
118 2600 114 30 13900
117 2800 114 30 24900
注意 DEPARTMENT_ID为20、30的DD值和2中的区别
4、over（order by salary range between 50 preceding and 150 following）
SQL> select
empno,sal,mgr,deptno,
sum(sal) over (partition by deptno order by sal
RANGE BETWEEN 0 PRECEDING AND 100 FOLLOWING) dd
from emp;
功能：按DEPARTMENT_ID分区，按SALARY升序排序，汇总当前SALARY到比当前SALARY大100之间的SALARY总和。
返回结果：
EMPNO SAL MGR DEPTNO DD
----- ------ ------ ------- --------
7934 1300 7782 10 1300
7782 2450 7839 10 2450
7839 5000 10 5000
7369 800 7902 20 800
7566 2975 7839 20 5975 3000在2975和（2975+100）之间，故求2975与3000的和
7902 3000 7566 20 3000
7900 950 7698 30 950
7521 1250 7698 30 2500
7654 1250 7698 30 2500
7844 1500 7698 30 3100
7499 1600 7698 30 1600
7698 2850 7839 30 2850
已选择12行。
解释：返回前置行和当前行SALARY相等，后续行比他大100的记录，在SALARY列上求和。
上下边界没有限制：OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
SQL> select empno,sal,mgr,deptno,
sum(sal) over (partition by deptno order by sal
RANGE BETWEEN unbounded PRECEDING AND unbounded FOLLOWING) dd
from emp;
EMPNO SAL MGR DEPTNO DD
---------- ---------- ---------- ---------- ----------
7934 1300 7782 10 8750
7782 2450 7839 10 8750
7839 5000 10 8750
7369 800 7902 20 6775
7566 2975 7839 20 6775
7902 3000 7566 20 6775
7900 950 7698 30 9400
7521 1250 7698 30 9400
7654 1250 7698 30 9400
7844 1500 7698 30 9400
7499 1600 7698 30 9400
7698 2850 7839 30 9400
已选择12行。
5、over（order by salary rows between 1 preceding and 2 following）-- 每行对应的数据窗口是之前行幅度值不超过1，之后行幅度值不超过2
SQL> select empno,sal,mgr,deptno,
sum(sal) over (partition by deptno order by sal
rows BETWEEN 1 PRECEDING AND 2 FOLLOWING) dd
from emp;
返回结果
EMPNO SAL MGR DEPTNO DD
---------- ---------- ---------- ---------- ----------
7934 1300 7782 10 8750
7782 2450 7839 10 8750
7839 5000 10 7450
7369 800 7902 20 6775
7566 2975 7839 20 6775
7902 3000 7566 20 5975
7900 950 7698 30 3450
7521 1250 7698 30 4950
7654 1250 7698 30 5600
7844 1500 7698 30 7200
7499 1600 7698 30 5950
7698 2850 7839 30 4450
已选择12行。
喝水不忘挖井人：http://blog.sina.com.cn/s/blog_70cea94b0100xi46.html
分享到：
发表评论
相关推荐
### Oracle开窗函数学习技巧总结 #### 一、概述 在Oracle数据库中，开窗函数是一种非常强大的功能，它能够帮助我们对数据进行更复杂的分析和处理。开窗函数允许我们在查询结果集的一个窗口内执行聚合操作，而无需对...
### Oracle分析函数及开窗函数详解 #### 一、Oracle分析函数概述 Oracle自8.1.6版本开始引入了分析函数，这类函数主要用于计算基于组的聚合值，并且与传统的聚合函数不同的是，分析函数可以针对每个组返回多行结果...
### Oracle 语法之 OVER (PARTITION BY ..) 及开窗函数详解 #### 一、OVER (PARTITION BY ..) 概述 在Oracle数据库中，`OVER (PARTITION BY ...)` 是一种非常强大的功能，它允许用户在数据集上进行窗口操作。这在...
### Oracle分析函数OVER及开窗函数详解 #### 一、概述 在Oracle数据库中，分析函数（Analytic Functions）是一种非常强大的工具，用于处理复杂的查询需求。这些函数可以在一组相关的行上执行计算，并且每行返回一个...
### Oracle的分析函数OVER及开窗函数 #### 一、分析函数OVER ##### 1. 概念介绍 从Oracle 8.1.6版本开始，Oracle引入了分析函数，这些函数可以对分组的数据执行复杂的操作，如计算累积总和、排名等。与聚合函数...
Oracle数据库调优是一个复杂而重要的任务，涉及到多个角色和阶段。在进行Oracle调优时，首先需要理解调优的目标和责任分工。系统调整的主要参与者包括应用设计人员、应用开发人员、数据库管理员（DBA）以及硬件/软件...
开窗函数是分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。开窗函数可以指定数据窗口的大小和范围，例如按照 salary 排序进行累计、按照部门分区等。 下面是 Oracle 中一些常用的分析函数...
Oracle、SQL Server、DB2等主流数据库系统都实现了开窗函数，但MySQL暂时还不支持。 开窗函数与聚合函数相似，都是对一组行进行计算，但不同之处在于，开窗函数可以在每组内返回多个结果，而不是像聚合函数那样只...
MySql只支持Union(并集)集合运算；但是对于交集Intersect、差集Except，就没有实现了。一般的解决方案用in和not in来解决，小量数据还可以，但数据...伪列：实现类似Oracle，MySqlServer的Over() partition by 开窗函数
目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函数给予支持。 开窗函数简介:与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像...
通过上述介绍，我们不仅了解了分析函数的基本用法，还探讨了如何利用开窗函数来定义不同类型的计算窗口，从而实现更高级的数据分析需求。掌握了这些技巧后，您可以更加高效地进行数据探索和洞察发现。
3. **制表(Reporting)函数**：与开窗函数同名，但作用于整个分区或结果集中的所有行，不依赖于特定的顺序。常见的制表函数有`SUM()`, `COUNT()`, `AVG()`, `MIN()`, `MAX()`等。 ```sql -- 示例：计算所有员工的...
1. **`OVER (ORDER BY salary)`**：此函数按薪资排序进行累计计算，其中`ORDER BY`可以被视为一种默认的开窗函数。 2. **`OVER (PARTITION BY deptno)`**：根据部门进行分组（分区），即在每个部门内部执行相应的...
开窗函数定义了分析函数作用的数据范围，通常包括以下几种类型： 1. **ORDER BY**：根据指定列的值对数据进行排序。 2. **PARTITION BY**：将数据划分成不同的分区，每个分区独立进行计算。 3. **RANGE BETWEEN** ...
开窗函数在MSSQL Server、Oracle、DB2等主流数据库系统中得到广泛支持，但遗憾的是，MySQL当前版本还不支持。 首先，我们需要理解什么是窗口。在SQL中，"窗口"是指在执行开窗函数时定义的一组行，这组行是相对于...
- **制表(reporting)函数**：与开窗函数同名，但作用于一个分区或一组上的所有列，不依赖于排序。 - **LAG、LEAD函数**：这类函数允许在结果集中向前或向后检索值，避免数据的自连接。 - **VAR_POP、VAR_SAMP、...
### 开窗函数及其应用场景 #### 开窗函数定义 开窗函数是分析函数中用于定义数据窗口范围的关键组成部分。它允许用户指定一个动态或静态的窗口范围，从而可以对窗口内的数据进行计算。开窗函数主要包括 `OVER` 子句...
开窗函数允许在一组行上执行计算，这组行被称为“窗口”，窗口可以基于特定的排序或分区条件。例如： - `OVER (ORDER BY salary)` 按薪资排序进行累计。 - `OVER (PARTITION BY deptno)` 按部门分区。 - `ROW_...