Oracle分析函数sum over介绍

分析函数sum over() 介绍

报送单位：审核人：

类型：业务应用

关键字：分析函数

1、引言

运维中，常常需要通过SQL语句对单行数据进行查询，同时又需要对结果集进行汇总，通常的方法是通过两个SQL语句分别进行查询汇总，这样效率低下。

2、现象描述

本节介绍一种ORACLE提供的全新的函数sum over()，该类函数称为分析函数，这类函数功能强大，可以通过一个SQL语句对数据进行遍历的同时又进行汇总，而且一张表只进行一次扫描，极大地提高SQL的执行效率。

3、处理过程

语法：

FUNCTION_NAME(,,...)

OVER()

NAME：可以是SUM,AVG,MAX,MIN,COUNT等其它，这些函数单独使用称为聚集函数，与OVER子句一起使用使称为分析函数。在当分析函数使用时，SQL语句中不需要使用GROUP BY子句。

执行计划：

下图说明分析函数只对表进行一次扫描

4、举例说明

下面分别举例来说明分析函数的使用，原始数据如下表

示例1：查询单行数据同时对所有数据工资进行汇总求和

示例2：查询所有数据，同时对第各部门工资进行汇总，汇总范围取值为第一行所在部门至当前部门所有数据。

示例3：查询所有数据，并且每一行汇总值按ENAME排序后取第一行至当前行ENAME所在行。

示例4：查询所有数据，并且每一行汇总值按EMPNO排序后取第一行至当前行EMPNO所在行

示例5：查询所有数据，同时每行对各部门分别进行按EMPNO排序后从各组第一行至当前行汇总求和。

示例6：查询所有数据，同时每行对从当前上两行数据范围的工资进行汇总求和。

示例7：查询所有数据，同时每行对从当前向前一行数据向后两行数据范围的工资进行汇总求和。

示例8：查询所有数据，同时每行对从当前行雇用日期向前推三天的范围的工资进行汇总求和。 HIREDATE-3>=HIREDATE>=

HIREDATE+3

示例9: 查询所有数据，同时每行对从当前行工资向前加500向后减500的工资涉及的行进行汇总求和。 SAL－500>=SAL>= SAL+500

5、经验总结

适当地使用分析函数可以提高SQL语句的效率。

减少应用书写逻辑，同时减少系统资源占用

其它类似分析函数AVG,MAX,MIN，COUNT使用与SUM类似。