分析函数学习(一) -->>语法学习

分析函数在OLAP系统应用非常普遍,而且高效. 最近好好系统学习一下。

 

一： 分析函数语法

           analytic_function([ arguments ]) OVER (analytic_clause)

   其中：analytic_function   分析函数的名称(如sum, avg, rank等)

               arguments             分析函数的参数

               over                         标识是一个分析函数

              analytic_clause      用来确认分析函数的操作规则 包括query_partition_clause、order_by_clause和windowing_clau

     例如: sum(sal) over (partition by deptno order by sal rows between unbounded preceding and current row)

  A. analytic_clause 之 order_by_clause 语法

     1) 默认是当前行所属的partition第一行到当前行

     2)默认是range窗口,对应逻辑窗口.  与其对应的是物理窗口 rows

    3)order by 如果有多个排序键, 则必须要求对应的窗口是当前partition 所有行,第一行到当前行,当前行到当前partition最后一行.

B.  analytic_clause 之 windowing_clau

  1)有显式window子句, 必须有order by . 有的分析函数不能有显式window, 有row_number,rank, dense_rank等

  2)window 可以指定是逻辑窗口还是物理窗口,逻辑用range 物理用rows

 3) window 窗口的方向必须是从上而下的.

 

二. 具体的案例分析

 A.  SUM  函数(基于表EMP)  需求：每个部门按工资从小到大累加

      select  t.ename , t.deptno,t.sal,
                   sum(t.sal) over(partition by t.deptno order by t.sal) cul_sal
          from emp t

   结果：

  

    

 而select  t.ename , t.deptno,t.sal,
                   sum(t.sal) over
                   (partition by t.deptno order by t.sal rows between unbounded preceding and current row ) cul_sal
          from emp t

  结果：

    

注意：order by 后面没写window 默认是 range between unbounded preceding and current row 指的是逻辑

                                                                          rows between unbounded preceding and current row  指的是物理

          看上两个图红色部门。表达不行 自己意会 。呵呵~~~~~~~~