分析函数的rows和range的差异
本帖最后由 Iris1988 于 2014-4-3 07:51 编辑rows是物理行，就是按行的位置，根据位置计算窗口范围
range是逻辑行，是按单元格值和偏移量计算窗口范围
示例如下：
准备环境：
SYS@WYZ>drop table emp purge;
表已删除。
SYS@WYZ>CREATE TABLE emp
2(
3 emp_id NUMBER(6),
4 enameVARCHAR2(45),
5 dept_id NUMBER(4),
6 hire_date DATE,
7 sal NUMBER(8,2)
8);
表已创建。
SYS@WYZ>
SYS@WYZ>--创建emp数据
SYS@WYZ>INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (101, 'Tom', 20,TO_DATE('21-09-1989', 'DD-MM-YYYY'), 2000);
已创建 1 行。
SYS@WYZ>INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (102, 'Mike', 20,TO_DATE('13-01-1993', 'DD-MM-YYYY'), 8000);
已创建 1 行。
SYS@WYZ>INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (120, 'John', 50,TO_DATE('18-07-1996', 'DD-MM-YYYY'), 1000);
已创建 1 行。
SYS@WYZ>INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (121, 'Joy', 50,TO_DATE('10-04-1997', 'DD-MM-YYYY'), 4000);
已创建 1 行。
SYS@WYZ>INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (122, 'Rich', 50,TO_DATE('01-05-1995', 'DD-MM-YYYY'), 3000);
已创建 1 行。
SYS@WYZ>INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (123, 'Kate', 50,TO_DATE('10-10-1997', 'DD-MM-YYYY'), 5000);
已创建 1 行。
SYS@WYZ>INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (124, 'Jess', 50,TO_DATE('16-11-1999', 'DD-MM-YYYY'), 6000);
已创建 1 行。
SYS@WYZ>INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (100, 'Stev', 10,TO_DATE('01-01-1990', 'DD-MM-YYYY'), 7000);
已创建 1 行。
SYS@WYZ>COMMIT;
提交完成。
SYS@WYZ>
SYS@WYZ>set linesize 2000
SYS@WYZ>set pagesize 2000
SYS@WYZ>col emp_id format 999
SYS@WYZ>col dept_id format 99
SYS@WYZ>col sal format 9999
SYS@WYZ>col ename format a5
SYS@WYZ>col hire_date FORMAT DATE
Rows使用：
SYS@WYZ>SELECT
2 emp_id,ename,dept_id,hire_date,sal,
3 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum_1_to_last,
4 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_1_to_cur,
5 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1/*value_expr*/ PRECEDING) sum_1_to_curbef1,
6 SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) sum_1_to_curaft1
7FROM emp order by dept_id,hire_date;
EMP_ID ENAME DEPT_ID HIRE_DATE SAL SUM_1_TO_LAST SUM_1_TO_CUR SUM_1_TO_CURBEF1 SUM_1_TO_CURAFT1
------ ----- ------- -------------- ----- ------------- ------------ ---------------- ----------------
100 Stev 10 01-1月 -90 7000 7000 7000 7000
101 Tom 20 21-9月 -89 2000 10000 2000 10000
102 Mike 20 13-1月 -93 8000 10000 10000 2000 10000
122 Rich 50 01-5月 -95 3000 19000 3000 4000
120 John 50 18-7月 -96 1000 19000 4000 3000 8000
121 Joy 50 10-4月 -97 4000 19000 8000 4000 13000
123 Kate 50 10-10月-97 5000 19000 13000 8000 19000
124 Jess 50 16-11月-99 6000 19000 19000 13000 19000
已选择8行。
Range使用：
SYS@WYZ>SELECT
2 emp_id,ename,dept_id,hire_date,sal,
3 SUM(sal) OVER (PARTITION BY dept_id ORDER BY salRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum_1_to_last,
4 SUM(sal) OVER (PARTITION BY dept_id ORDER BY salRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_1_to_cur,
5 SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND 2500/*value_expr*/ PRECEDING) sum1,
6 SUM(sal) OVER (PARTITION BY dept_id ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND 2500/*value_expr*/ FOLLOWING) sum2
7FROM emp;
EMP_ID ENAME DEPT_ID HIRE_DATE SAL SUM_1_TO_LAST SUM_1_TO_CUR SUM1 SUM2
------ ----- ------- -------------- ----- ------------- ------------ ---------- ----------
100 Stev 10 01-1月 -90 7000 7000 7000 7000
101 Tom 20 21-9月 -89 2000 10000 2000 2000
102 Mike 20 13-1月 -93 8000 10000 10000 2000 10000
120 John 50 18-7月 -96 1000 19000 1000 4000
122 Rich 50 01-5月 -95 3000 19000 4000 13000
121 Joy 50 10-4月 -97 4000 19000 8000 1000 19000
123 Kate 50 10-10月-97 5000 19000 13000 1000 19000
124 Jess 50 16-11月-99 6000 19000 19000 4000 19000
已选择8行。
页:
[1]