SQL分析利器：OVER() 函数如何为每行数据定制计算

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领域脱颖而出。

本文由mdnice多平台发布