ORACLE中分析函数的应用

大家应该对排序分组这种操作很熟悉,我们来看看下面的几个典型例子：

①查找上一年度各个销售区域排名前10的员工

②按区域查找上一年度订单总额占区域订单总额20%以上的客户

③查找上一年度销售最差的部门所在的区域

④查找上一年度销售最好和最差的产品

可以感觉到这几个查询和我们日常遇到的查询有些不同，具体有：

①需要对同样的数据进行不同级别的聚合操作

②需要在表内将多条数据和同一条数据进行多次的比较

③需要在排序完的结果集上进行额外的过滤操作

处理这些问题就需要用到分析函数了

select ename, sal, sum(sal) over(partition by deptno order by sal) from emp;

①sum就是函数名 (sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式。例如:sum(sal+comm) over 是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数

②partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区

③order by ename 是可选的order by 子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数

ORACLE提供了26个分析函数,按功能分5类 ：

1.等级(ranking)函数:用于寻找前N种查询

2.窗口(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MMINAX等,作用于数据的一个窗口上。例: sum(t.sal) over (partition by t.deptno order by t.ename) department_total

3.制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列。例: sum(t.sal) over (partition by t.deptno ) department_total2 制表函数与开窗函数的关键不同之处在于OVER语句上缺少一个ORDER BY子句!

4.LAG,LEAD函数:这类函数允许在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常用用的.

5.VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值

Loading...