Oracle 分析函数 OVER()
OVER()
是 Oracle 从 8.1.6 版本开始提供的一个强大分析函数,用于解决复杂报表统计需求。它允许在数据分组后,基于每个组内的某种统计值对每一行返回一个结果值。与传统的聚合函数不同,OVER()
函数能让每行数据返回一个计算结果,而不需要整组数据进行聚合计算。
1. OVER()
分析函数语法
OVER()
函数的基本语法结构如下:
FUNCTION_NAME(<参数>, ...)
OVER (
<PARTITION BY 表达式, ...>
<ORDER BY 表达式 ASC/DESC NULLS FIRST/LAST>
<WINDOWING 子句>
)
语法解析:
-
FUNCTION_NAME(<参数>, ...)
: 用于指定要与OVER()
函数结合使用的分析函数。常见的分析函数包括RANK()
、DENSE_RANK()
、ROW_NUMBER()
、NTILE()
、LAG()
、LEAD()
以及聚合函数SUM()
、AVG()
、MIN()
、MAX()
、COUNT()
等。 -
PARTITION BY 表达式
: 类似于GROUP BY
,用于对数据进行分区。每个分区将独立进行函数计算。 -
ORDER BY 表达式 ASC/DESC NULLS FIRST/LAST
: 指定在每个分区内的数据排序方式。ASC
表示升序,DESC
表示降序。NULLS FIRST
将NULL
值放在前面,NULLS LAST
将NULL
值放在最后。 -
WINDOWING 子句
: 用于定义数据窗口的范围。常见的写法有ROWS BETWEEN
和RANGE BETWEEN
,分别定义窗口的起止范围。
2. 窗口子句 WINDOWING
示例
WINDOWING
子句用于指定每行对应的窗口数据范围,即当前行在哪些数据范围内进行计算。例如:
ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING
: 当前行的上1行和下2行,共计4行。ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
: 当前行和其后的2行,共计3行。RANGE BETWEEN CURRENT ROW AND 50 FOLLOWING
: 当前行开始,后面50个范围内的行。RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING
: 当前行开始,前50和后150个范围内的行。
3. OVER()
函数常见搭配使用
RANK()
、DENSE_RANK()
、ROW_NUMBER()
:用于为分区中的每行计算排名。NTILE()
:将数据分为指定的组数,并返回每行所属的组号。LAG()
和LEAD()
:分别用于访问当前行之前或之后的行数据。- 聚合函数:如
SUM()
、AVG()
、MIN()
、MAX()
和COUNT()
,它们也可以与OVER()
搭配使用,但ORDER BY
子句可以省略。
示例说明
有一个员工表 emp
,使用 OVER()
分析函数计算每个部门的员工工资总和和排名:
SELECT
empno,
deptno,
sal,
SUM(sal) OVER (PARTITION BY deptno) AS dept_sal_sum,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS sal_rank
FROM emp;
SUM(sal) OVER (PARTITION BY deptno)
:计算每个部门的工资总和。RANK() OVER (PARTITION BY deptno ORDER BY sal DESC)
:按工资从高到低对每个部门的员工排名。
非常感谢您读到这里!如果您觉得这篇文章对您有帮助,可以关注一下博主。关注后,您将第一时间获得最新的AI、云计算、运维(Linux、数据库,容器等)技术,以及更多实用的技能干货。
提供丰富的学习资源和实践经验,让你快速掌握AI技能;提供最新的行业动态和应用案例,帮助你在AI领域脱颖而出。