Oracle 分析函数

浏览数：30 / 时间：2015年06月12日

对于分析函数，大家要注意：
1. 分析函数是在整个SQL查询结束后(SQL语句中的ORDER BY的执行比较特殊)再进行的操作, 也就是说
SQL语句中的ORDER BY也会影响分析函数的执行结果,请看:
SQL> select deptno,
  2         empno,
  3         ename,
  4         sal,
  5         hiredate,
  6         last_value(sal) over(partition by deptno) last_value
  7    from emp
  8   where deptno = 30;

DEPTNO EMPNO ENAME            SAL HIREDATE    last_value
------ ----- ---------- --------- ----------- -----------
    30  7499 ALLEN        1600.00 1981-02-20          950
    30  7521 WARD         1250.00 1981-02-22          950
    30  7654 MARTIN       1250.00 1981-09-28          950
    30  7698 BLAKE        2850.00 1981-05-01          950
    30  7844 TURNER       1500.00 1981-09-08          950
    30  7900 JAMES         950.00 1981-12-03          950

6 rows selected

SQL> select deptno,
  2         empno,
  3         ename,
  4         sal,
  5         hiredate,
  6         last_value(sal) over(partition by deptno) last_value
  7    from emp
  8   where deptno = 30
  9   order by deptno,mgr;

DEPTNO EMPNO ENAME            SAL HIREDATE    last_value
------ ----- ---------- --------- ----------- -----------
    30  7499 ALLEN        1600.00 1981-02-20         2850
    30  7521 WARD         1250.00 1981-02-22         2850
    30  7654 MARTIN       1250.00 1981-09-28         2850
    30  7900 JAMES         950.00 1981-12-03         2850
    30  7844 TURNER       1500.00 1981-09-08         2850
    30  7698 BLAKE        2850.00 1981-05-01         2850

6 rows selected

SQL> select deptno,
  2         empno,
  3         ename,
  4         sal,
  5         hiredate,
  6         last_value(sal) over(partition by deptno) last_value
  7    from emp
  8   where deptno = 30
  9   order by deptno,mgr desc;

DEPTNO EMPNO ENAME            SAL HIREDATE    last_value
------ ----- ---------- --------- ----------- -----------
    30  7698 BLAKE        2850.00 1981-05-01         1250
    30  7499 ALLEN        1600.00 1981-02-20         1250
    30  7521 WARD         1250.00 1981-02-22         1250
    30  7900 JAMES         950.00 1981-12-03         1250
    30  7844 TURNER       1500.00 1981-09-08         1250
    30  7654 MARTIN       1250.00 1981-09-28         1250

6 rows selected

从上面的结果我们分析得出: 
a) 如果SQL语句中的Order By满足分析函数分析时要求的排序,那么SQL语句中的排序将先执行,分析
函数分析时就不必再排序
b) 如果SQL语句中的Order By不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分
析函数分析结束后执行排序


2. 分析函数中包含三个分析子句:分组(Partition By), 排序(Order By), 窗口(Window)
窗口就是分析函数分析时要处理的数据范围,就拿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)
以上示例指定窗口为整个分组.

当省略窗口子句时:
a) 如果存在Order By则默认的窗口是unbounded preceding and current row
b) 如果同时省略Order By则默认的窗口是unbounded preceding and unbounded following

如果省略分组,则把全部记录当成一个组:
a) 如果存在Order By则默认窗口是unbounded preceding and current row
b) 如果这时省略Order By则窗口默认为unbounded preceding and unbounded following

希望以上信息对大家有所帮助!