分析函数及窗口函数(一)

分析函数格式（三部分组成）

   函数名(参数) over(partition by 子句 order by 子句 rows/range子句)
   三部分组成
   1: 函数名  
        比如：sum,avg,max,min等聚合函数或lead,lag行比较函数
   2: over 关键字，说明是分析函数
   3: 分析子句 
        over括号内的内容，包括三部分
       (1): partition by  分组子句
       (2): order by   排序子句 组内排序
       (3): rows/range 窗口子句  必须和order by 子句同时使用，操作行集合   
   
一：order by 
   (1): 没有order by 
            结果集的一行到最后一行
   (2): 有order by
            默认从第一行到当前分组最后一行
     select  e.*,sum(sal) over() sum_sal,
  sum(sal) over(order by deptno) sum_ord_sal
      from  emp e;


     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO    SUM_SAL SUM_ORD_SAL
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -----------
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10      30025        8750
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10      30025        8750
      7839 KING       PRESIDENT            17-11月-81           5000                    10      30025        8750
      7566 JONES      MANAGER         7839 02-4月 -81           2975                    20      30025       19625
      7369 SMITH      CLERK           7902 17-12月-80            800                    20      30025       19625
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20      30025       19625
      7902 FORD       ANALYST         7566 03-12月-81           3000                    20      30025       19625
      7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20      30025       19625
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30      30025       29025
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30      30025       29025
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30      30025       29025
      7900 JAMES      CLERK           7698 03-12月-81            950                    30      30025       29025
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30      30025       29025
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30      30025       29025
         1 aaa        sales           7902 03-3月 -81           1000                            30025       30025


二：排列
   rank：        有多个并列名次时，下一名次会跳跃：如两个2，下一个是4
   dense_rank   有多个并列名次时，正常排序。如两个2，下一个是3
   row_number    当有两个并列时,名次不一样(有先后)
   
三: 窗口函数
    1: row窗口
       特点：可以order by多列，可以是任何类型
       (1): rows 3 preceding 
        包括当前行和当前行的前3行记录在内，如果有partition by 就是在当前分组内
        
    2: range 窗口
       特点：只能是number，date类型，只支持order by一列
       (1): range unbounded preceding
           当前行和之前所有行包含进来
           select deptno,ename,sal,
    　　    sum(sal) over(order by deptno range unbounded preceding) sumsal
      from emp;


   DEPTNO ENAME             SAL     SUMSAL
---------- ---------- ---------- ----------
       10 CLARK            2450       8750
       10 MILLER           1300       8750
       10 KING             5000       8750
       20 JONES            2975      19625
       20 SMITH             800      19625
       20 SCOTT            3000      19625
       20 FORD             3000      19625
       20 ADAMS            1100      19625
       30 WARD             1250      29025
       30 TURNER           1500      29025
       30 ALLEN            1600      29025
       30 JAMES             950      29025
       30 MARTIN           1250      29025
       30 BLAKE            2850      29025
          aaa              1000      30025
            
            #order by 默认是range between unbounded preceding and current row
 select deptno,ename,sal,
　     sum(sal) over (order by deptno) sumsal
       from emp;
   
   DEPTNO ENAME             SAL     SUMSAL
---------- ---------- ---------- ----------
       10 CLARK            2450       8750
       10 MILLER           1300       8750
       10 KING             5000       8750
       20 JONES            2975      19625
       20 SMITH             800      19625
       20 SCOTT            3000      19625
       20 FORD             3000      19625
       20 ADAMS            1100      19625
       30 WARD             1250      29025
       30 TURNER           1500      29025
       30 ALLEN            1600      29025
       30 JAMES             950      29025
       30 MARTIN           1250      29025
       30 BLAKE            2850      29025
          aaa              1000      30025

(3): range 100 preceding
         假如over()里asc排列，意思是[number-100,number]这样一个闭区间是它的窗口。
　　  假如over()里desc排列，意思是[number,number+100]这样一个闭区间是它的窗口
    
(4): 窗口总结

　　 1、unbounded preceding:从当前分区的第一行开始，到当前行结束。

　　  2、current row:从当前行开始，也结束于当前行。

　　  3、[numeric eXPression] preceding:
对于rows来说从当前行之前的第[numeric expression]行开始，到当前行结束。
对range来说从小于数值表达式的值开始，到当前行结束。

           4、[numeric expression] following:与[numeric expression] preceding相反。