Oracle之分析函数 之 order by子句

/*


有ORDER BY的存在将添加一个默认的开窗子句！意味着从第一行到当前行；
没有ORDER BY时,默认的窗口是全部的分区 ；
在Order by 子句后可以添加nulls last,如:order by comm desc nulls last   表示排序时忽略comm列为空的行.   

 不写between AND  ,在有order BY 的情况下，就是分组第一行到当前行                        BETWEEN unbounded preceding  and current row
 不写between AND  ,在没有order BY 的情况下，就是分组第一行到分组最后一行；              BETWEEN unbounded preceding and unbounded following 


 此外记住，在RANGE的开窗中，ORDER BY中只能有一列；ROWS的开窗的ORDER BY 可以有多列。  
  
*/

drop table emp purge;


CREATE TABLE emp
(
  emp_id    NUMBER(6),
  ename  VARCHAR2(45),
  dept_id   NUMBER(4),
  hire_date DATE,
  sal    NUMBER(8,2)
);


--创建emp数据
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (101, 'Tom',    20,  TO_DATE('21-09-1989', 'DD-MM-YYYY'), 2000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (102, 'Mike',   20,  TO_DATE('13-01-1993', 'DD-MM-YYYY'), 8000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (120, 'John',   50,  TO_DATE('18-07-1996', 'DD-MM-YYYY'), 1000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (121, 'Joy',    50,  TO_DATE('10-04-1997', 'DD-MM-YYYY'), 4000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (122, 'Rich',   50,  NULL, 3000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (123, 'Kate',   50,  TO_DATE('10-10-1997', 'DD-MM-YYYY'), 5000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (124, 'Jess',   50,  TO_DATE('16-11-1999', 'DD-MM-YYYY'), 6000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (100, 'Stev',   10,  TO_DATE('01-01-1990', 'DD-MM-YYYY'), 7000);
COMMIT;


set linesize 2000
set pagesize 2000
col emp_id format 999
col dept_id format 99
col ename format a5


SELECT
  emp_id,ename,dept_id,hire_date,sal,
  SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date) sum_sal1,
  SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date DESC) sum_sal2,  
  SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date DESC nulls LAST) sum_sal3,  
  SUM(sal) OVER (PARTITION BY dept_id ) sum_sal4,
  SUM(sal) OVER ( ) sum_sal5
  FROM emp;



EMP_ID ENAME DEPT_ID HIRE_DATE             SAL   SUM_SAL1   SUM_SAL2   SUM_SAL3   SUM_SAL4   SUM_SAL5
------ ----- ------- -------------- ---------- ---------- ---------- ---------- ---------- ----------
   100 Stev       10 01-1月 -90           7000       7000       7000       7000       7000      36000
   101 Tom        20 21-9月 -89           2000       2000      10000      10000      10000      36000
   102 Mike       20 13-1月 -93           8000      10000       8000       8000      10000      36000
   120 John       50 18-7月 -96           1000       1000      19000      16000      19000      36000
   121 Joy        50 10-4月 -97           4000       5000      18000      15000      19000      36000
   123 Kate       50 10-10月-97           5000      10000      14000      11000      19000      36000
   124 Jess       50 16-11月-99           6000      16000       9000       6000      19000      36000
   122 Rich       50                      3000      19000       3000      19000      19000      36000