oracle分析函数之windowing_clause--rows(一)

oracle分析函数之windowing_clause--rows

Some analytic functions allow the windowing_clause. In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*)。--有些分析函数允许windowing_clause，在附录中，带*号的分析函数可以用windowing_clause，可以看到很多是聚合函数。

rows代表物理行，range代表逻辑偏移，我们来做下试验rows：

  --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
  --unbounded：不受控制的，无限的
  --preceding：在...之前
  --following：在...之后

   SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL  
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;

    DEPTNO      EMPNO ENAME             SAL    MAX_SAL
---------- ---------- ---------- ---------- ----------
        10       7934 MILLER           1300       5000
        10       7782 CLARK            2450       5000
        10       7839 KING             5000       5000
        20       7369 SMITH             800       3000
        20       7876 ADAMS            1100       3000
        20       7566 JONES            2975       3000
        20       7788 SCOTT            3000       3000
        20       7902 FORD             3000       3000
        30       7900 JAMES             950       2850
        30       7654 MARTIN           1250       2850
        30       7521 WARD             1250       2850
        30       7844 TURNER           1500       2850
        30       7499 ALLEN            1600       2850
        30       7698 BLAKE            2850       2850

--ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总  
SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
  FROM EMP;
     EMPNO ENAME          DEPTNO        SAL    MAX_SAL
---------- ---------- ---------- ---------- ----------
      7782 CLARK              10       2450       2450
      7839 KING               10       5000       7450
      7934 MILLER             10       1300       8750
      7876 ADAMS              20       1100       1100
      7902 FORD               20       3000       4100
      7566 JONES              20       2975       7075
      7788 SCOTT              20       3000      10075
      7369 SMITH              20        800      10875
      7499 ALLEN              30       1600       1600
      7698 BLAKE              30       2850       4450
      7900 JAMES              30        950       5400
      7654 MARTIN             30       1250    





   6650
      7844 TURNER             30       1500       8150
      7521 WARD               30       1250       9400

 --ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
 SELECT EMPNO,
        ENAME,
        DEPTNO,
        SAL,
        SUM(SAL) OVER(PARTITION BY DEPTNO
                      ORDER BY ENAME
                      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
   FROM EMP;

    EMPNO ENAME          DEPTNO        SAL    MAX_SAL
     ------ ---------- ---------- ---------- ----------
      7782 CLARK              10       2450       8750
      7839 KING               10       5000       6300
      7934 MILLER             10       1300       1300
      7876 ADAMS              20       1100      10875
      7902 FORD               20       3000       9775
      7566 JONES              20       2975       6775
      7788 SCOTT              20       3000       3800
      7369 SMITH              20        800        800
      7499 ALLEN              30       1600       9400
      7698 BLAKE              30       2850       7800
      7900 JAMES              30        950       4950
      7654 MARTIN             30       1250       4000
      7844 TURNER             30       1500       2750
      7521 WARD               30       1250       1250


--注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总
 SELECT EMPNO,
        ENAME,
        DEPTNO,
        SAL,
        SUM(SAL) OVER(PARTITION BY DEPTNO
                      ORDER BY ENAME ROWS
                      BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
   FROM EMP;

      EMPNO ENAME          DEPTNO        SAL    MAX_SAL
     ------ ---------- ---------- ---------- ----------
      7782 CLARK              10       2450       2450
      7839 KING               10       5000       7450
      7934 MILLER             10       1300       6300
      7876 ADAMS              20       1100       1100
      7902 FORD               20       3000       4100
      7566 JONES              20       2975       5975
      7788 SCOTT              20       3000       5975
      7369 SMITH              20        800       3800
      7499 ALLEN              30       1600       1600
      7698 BLAKE              30       2850       4450
      7900 JAMES              30        950       3800
      7654 MARTIN             30       1250       2200
      7844 TURNER             30       1500       2750
      7521 WARD               30       1250       2750

 --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下两行(rownum+2)的汇总
 SELECT EMPNO,
        ENAME,
        DEPTNO,
        SAL,
        SUM(SAL) OVER(PARTITION BY DEPTNO
                      ORDER BY ENAME
                      ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
   FROM EMP;

      EMPNO ENAME          DEPTNO        SAL    MAX_SAL
     ---