首页 > 代码库 > oracle 分析函数
oracle 分析函数
认识分析函数
分析函数是什么？
分析函数是oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。
分析函数和聚合函数的不同之处是什么？
普通的聚合函数用group by 分组，每个分组返回一个统计值，而分析函数采用partition by 分组，并且每组每行都可以返回一个统计值。
分析函数的形式：
分析函数带有一个开窗函数OVER(),包含三个分析子句:分组(partition by),排序(order by),窗口(Rows),他们的使用形式如下：
Over(Partition By XXX Order by yyy rows between zzz)
注：窗口子句包括：rows方式的窗口,range方式和滑动窗口。
分析函数的例子：
显示各部门员工的工资，并附带显示该部分的最高工资。
select deptno,empno,ename,sal,
last_value(sal) over(partition by deptno order by sal rows between unbounded preceding and unbounded following) max_sal
from emp;
DEPTNO EMPNO ENAME SAL MAX_SAL
10 7934 MILLER 1300.00 5000
10 7782 CLARK 2450.00 5000
10 7839 KING 5000.00 5000
20 7369 SMITH 800.00 3000
20 7876 ADAMS 1100.00 3000
20 7566 JONES 2975.00 3000
20 7788 SCOTT 3000.00 3000
20 7902 FORD 3000.00 3000
30 7900 JAMES 950.00 2850
30 7654 MARTIN 1250.00 2850
30 7521 WARD 1250.00 2850
30 7844 TURNER 1500.00 2850
30 7499 ALLEN 1600.00 2850
30 7698 BLAKE 2850.00 2850
2.理解OVER()函数
2.1两个Order by 的执行时机
分析函数是在整个SQL查询结束后(SQL语句中的ORDER BY的执行比较特殊)再进行的操作，也就是说SQL语句中的ORDER BY 也会影响分析函数的执行结果：
a)两者一致：如果SQL语句中的Order by 满足分析函数分析时要求的排序，那么SQL语句中的排序将先执行，分析函数在分析时就不必再排序。
b)两者不一致：如果SQL语句中的Order by 不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分析函数分析结束后执行排序。
2.2 分析函数中的分组/排序/窗口
分析函数 包含三个分析子句：分组(Partition by ),排序(Order by),窗口(Rows)
窗口就是分析函数分析时要处理的数据范围，就拿SUM来说，它是SUM窗口中的记录而不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗口指定到该分组中的第一行数据到当前行，如果你指定该窗口从该分组中的第一行到最后一行，那么该组中的每一个SUM值都会一样，即整个组的总和。
窗口子句在这里我只说rows方式的窗口，range方式和滑动窗口也不提
窗口子句中我们经常用到指定第一行，当前行，最后一行这样的三个属性.
第一行是unbounded preceding,
当前行是 current row,
最后一行是 unbounded following .
窗口子句不能单独出现，必须有Order By子句时才能出现,如：
last_value(sal) over(partition by deptno order by sal rows between unbounded preceding and unbounded following)
以上示例指定窗口为整个分组.
而出现Order By子句的时候，不一定要有窗口子句，但效果会很不一样，此时的窗口默认是当前组的第一行到当前行！
当省略窗口子句时：
a) 如果存在Order By则默认的窗口是unbounded preceding and current now
b) 如果同时省略Order By则默认的窗口是unbounded preceding and unbounded following
如果省略分组，则把全部记录当成一个组：
a) 如果存在Order By则默认窗口是unbounded preceding and current row
b) 如果这时省略Order By则窗口默认认为unbounded preceding and unbounded following
2.3帮助理解OVER()的实例
例1:SQL无排序,over()排序子句省略
select deptno ,empno,ename , sal ,
last_value(sal) over(partition by deptno )
from emp
where deptno=20
DEPTNO EMPNO ENAME SAL LAST_VALUE(SAL)OVER(PARTITIONB
20 7369 SMITH 800.00 3000
20 7566 JONES 2975.00 3000
20 7788 SCOTT 3000.00 3000
20 7876 ADAMS 1100.00 3000
20 7902 FORD 3000.00 3000
例2：SQL无排序,OVER()排序子句有，窗口省略
Select deptno ,empno,ename , sal ,
last_value(sal) over(partition by deptno order by sal)
from emp where deptno=’30’
DEPTNO EMPNO ENAME SAL LAST_VALUE(SAL)OVER(PARTITIONB
30 7900 JAMES 950.00 950
30 7654 MARTIN 1250.00 1250
30 7521 WARD 1250.00 1250
30 7844 TURNER 1500.00 1500
30 7499 ALLEN 1600.00 1600
30 7698 BLAKE 2850.00 2850
例3:SQL无排序，OVER()排序子句有，窗口也有，窗口特意强调全组数据
select deptno ,empno,ename , sal ,
last_value(sal) over(partition by deptno order by sal
rows between unbounded preceding and unbounded following)
from emp where deptno=30
DEPTNO EMPNO ENAME SAL LAST_VALUE(SAL)OVER(PARTITIONB
30 7900 JAMES 950.00 2850
30 7521 WARD 1250.00 2850
30 7654 MARTIN 1250.00 2850
30 7844 TURNER 1500.00 2850
30 7499 ALLEN 1600.00 2850
30 7698 BLAKE 2850.00 2850
例4：SQL有排序(正序),Over()排序子句无，先做SQL排序再进行分析函数运算。
select deptno ,empno,ename , sal ,
last_value(sal) over(partition by deptno )
from emp
where deptno=30
order by deptno,sal
DEPTNO EMPNO ENAME SAL LAST_VALUE(SAL)OVER(PARTITIONB
30 7900 JAMES 950.00 2850
30 7521 WARD 1250.00 2850
30 7654 MARTIN 1250.00 2850
30 7844 TURNER 1500.00 2850
30 7499 ALLEN 1600.00 2850
30 7698 BLAKE 2850.00 2850
2.4 lag()与lead():求之前与之后的第N行
Lag(arg1,arg2,arg3):
Arg1是从其他行返回的表达式
Arg2 是希望检索的当前行分区的偏移量。是一个正的偏移量，是一个往回检索以前的行的数目。
Arg3是在arg2表示的数目超出了分组的范围时返回的值。
而lead()与lag()相反
2.5. ratio_to_report():求百分比
select empno,ename,sal,deptno,
ratio_to_report(sal) over() as "每个员工的薪水占总工资比",
ratio_to_report(sal) over(partition by deptno) as "每个员工的薪水占部门总薪水的比"
from emp
order by deptno,empno;
EMPNO ENAME SAL DEPTNO 每个员工的薪水占总工资比 每个员工的薪水占部门总薪水的比
7782 CLARK 2450.00 10 0.0844099913867356 0.28
7839 KING 5000.00 10 0.172265288544358 0.571428571428571
7934 MILLER 1300.00 10 0.0447889750215332 0.148571428571429
7369 SMITH 800.00 20 0.0275624461670973 0.0735632183908046
7566 JONES 2975.00 20 0.102497846683893 0.273563218390805
7788 SCOTT 3000.00 20 0.103359173126615 0.275862068965517
7876 ADAMS 1100.00 20 0.0378983634797588 0.101149425287356
7902 FORD 3000.00 20 0.103359173126615 0.275862068965517
7499 ALLEN 1600.00 30 0.0551248923341947 0.170212765957447
7521 WARD 1250.00 30 0.0430663221360896 0.132978723404255
7654 MARTIN 1250.00 30 0.0430663221360896 0.132978723404255
7698 BLAKE 2850.00 30 0.0981912144702842 0.303191489361702
7844 TURNER 1500.00 30 0.0516795865633075 0.159574468085106
7900 JAMES 950.00 30 0.0327304048234281 0.101063829787234
总结：ratio_to_report() 括号中就是分子，
Over()括号中就是分母
有了ratio_to_report(分析函数，我们避免了还需要写分析函数，自己相除的写法。
注意：分母缺省就是占整个比。
oracle 分析函数