本文详细介绍了 Oracle 数据库中的 OLAP 函数及其使用方法，包括窗口函数的语法组成、orderby 子句对窗口的影响、用于排列的函数、用于合计的函数及不同类型的窗口设置。通过多个 SQL 示例演示了如何应用这些函数来处理复杂的数据分析任务。
发表于: 2004.11.09 01:12
分类: 读书手记
出处: http://sweetmemory.itpub.net/post/543/4375
---------------------------------------------------------------
语法: FUNCTION_NAME(,,...)
OVER
(
)
OLAP函数语法四个部分:
1、function本身 用于对窗口中的数据进行操作；
2、partitioning clause 用于将结果集分区；
3、order by clause 用于对分区中的数据进行排序；
4、windowing clause 用于定义function在其上操作的行的集合，即function所影响的范围。
一、order by对窗口的影响
不含order by的：
SQL> select deptno,sal,sum(sal) over()
2 from emp;
DEPTNO SAL SUM(SAL)OVER()
--------- ----- -------------------
20 800 24028
30 1600 24028
30 1250 24028
20 2975 24028
30 1250 24028
30 2850 24028
10 2450 24028
20 3000 24028
30 1500 24028
20 1100 24028
30 950 24028
20 3000 24028
10 1300 24028
0 24028
0 1 24028
0 2 24028
已选择16行。
已用时间: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=13 Bytes=91)
1 0 WINDOW (BUFFER) (Cost=2 Card=13 Bytes=91)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=91)
不含order by时，默认的窗口是从结果集的第一行直到末尾。
因此我们看到的结果是，为所有的sal计算了一个合计值。
含order by的：
SQL> select deptno,sal,
2 sum(sal) over(order by deptno) as sumsal
3 from emp;
DEPTNO SAL SUMSAL
----------- ------ ----------
0 3
0 1 3
0 2 3
10 2450 3753
10 1300 3753——3753=1+2+2450+1300
20 800 14628
20 3000 14628
20 3000 14628
20 1100 14628
20 2975 14628
30 1600 24028
30 950 24028
30 1500 24028
30 2850 24028
30 1250 24028
30 1250 24028
已选择16行。
已用时间: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=13 Bytes=91)
1 0 WINDOW (SORT) (Cost=4 Card=13 Bytes=91)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=91)
我们看到执行计划变了，窗口受到了order by的影响。
当含有order by时，默认的窗口是从第一行直到当前分组的最后一行。
sum会计算当前组所有值和前面所有行的合计值。
二、用于排列的函数
SQL> select empno, deptno, sal,
2 rank() over
3 (partition by deptno order by sal desc nulls last) as rank,
4 dense_rank() over
5 (partition by deptno order by sal desc nulls last) as dense_rank,
6 row_number() over
7 (partition by deptno order by sal desc nulls last) as row_number
8 from emp;
EMPNO DEPTNO SAL RANK DENSE_RANK ROW_NUMBER
--------- ---------- ------- ------- ---------------- -----------------
9999 0 2 1 1 1
9999 0 1 2 2 2
9999 0 3 3 3
7839 10 5000 1 1 1
7782 10 2450 2 2 2
7934 10 1300 3 3 3
7788 20 3000 1 1 1
7902 20 3000 1 1 2
7566 20 2975 3 2 3
7876 20 1100 4 3 4
7369 20 800 5 4 5
7698 30 2850 1 1 1
7499 30 1600 2 2 2
7844 30 1500 3 3 3
7521 30 1250 4 4 4
7654 30 1250 4 4 5
7900 30 950 6 5 6
已选择17行。
nulls last:强制NULL值放在最后，否则在降序排列时NULL会被排在最前面。
三、用于合计的函数
SQL> select deptno,sal,
2 sum(sal) over (partition by deptno) as sumsal,
3 avg(sal) over (partition by deptno) as avgsal,
4 count(*) over (partition by deptno) as count,
5 max(sal) over (partition by deptno) as maxsal
6 from emp;
DEPTNO SAL SUMSAL AVGSAL COUNT MAXSAL
----------- ------- ---------- ---------- --------- ----------
0 3 3 2
0 1 3 3 2
0 2 3 3 2
10 2450 8750 2916.6 3 5000
10 5000 8750 2916.6 3 5000
10 1300 8750 2916.6 3 5000
20 800 10875 5 3000
20 1100 10875 5 3000
20 3000 10875 5 3000
20 3000 10875 5 3000
20 2975 10875 5 3000
30 1600 9400 1566.6 6 2850
30 2850 9400 1566.6 6 2850
30 1250 9400 1566.6 6 2850
30 950 9400 1566.6 6 2850
30 1500 9400 1566.6 6 2850
30 1250 9400 1566.6 6 2850
已选择17行。
四、开窗语句
1、rows窗口: "rows 5 preceding"
适用于任何类型而且可以order by多列。
SQL> select deptno,ename,sal,
2 sum(sal) over (order by deptno rows 2 preceding) sumsal
3 from emp;
DEPTNO ENAME SAL SUMSAL
---------- ---------- ------ ----------
0 aaa
0 aaa 1 1
0 aaa 2 3
10 Clark 2450 2453 ——2453=1+2+2450
10 Miller 1300 3752
20 Smith 800 4550
20 Scott 3000 5100
20 Ford 3000 6800
20 Adams 1100 7100
20 Jones 2975 7075
30 Allen 1600 5675
30 James 950 5525
30 Turner 1500 4050
30 Blake 2850 5300
30 Martin 1250 5600
30 Ward 1250 5350
已选择16行。
rows 2 preceding:将当前行和它前面的两行划为一个窗口，因此sum函数就作用在这三行上面。
SQL> select deptno,ename,sal,
2 sum(sal) over
3 (partition by deptno order by ename rows 2 preceding) sumsal
4 from emp
5 order by deptno,ename;
DEPTNO ENAME SAL SUMSAL
---------- --------- ------ -----------
0 aaa
0 aaa 1 1
0 aaa 2 3
10 Clark 2450 2450
10 Miller 1300 3750
20 Adams 1100 1100 ——仍然为1100
20 Ford 3000 4100
20 Jones 2975 7075 ——7075=1100+3000+2975
20 Scott 3000 8975
20 Smith 800 6775
30 Allen 1600 1600
30 Blake 2850 4450
30 James 950 5400
30 Martin 1250 5050
30 Turner 1500 3700
30 Ward 1250 4000
已选择16行。
加了partiton by分区后之后，rows 2 preceding(窗口)只在当前分区内生效，不会影响分区之外的行。
SQL> select ename,sal,hiredate,
2 first_value(ename)over
3 (order by hiredate asc rows 5 preceding) first_ename,
4 first_value(hiredate) over
5 (order by hiredate asc rows 5 preceding) first_hiredate
6 from emp
7 order by hiredate asc;
ENAME SAL HIREDATE FIRST_ENAME FIRST_HIRE
---------- ------ ------------- ----------------- --------------
SMITH 800 17-12月-80 SMITH 17-12月-80
ALLEN 1600 20-2月 -81 SMITH 17-12月-80
WARD 1250 22-2月 -81 SMITH 17-12月-80
JONES 2975 02-4月 -81 SMITH 17-12月-80
BLAKE 2850 01-5月 -81 SMITH 17-12月-80
CLARK 2450 09-6月 -81 SMITH 17-12月-80
TURNER 1500 08-9月 -81 ALLEN 20-2月 -81
MARTIN 1250 28-9月 -81 WARD 22-2月 -81
KING 5000 17-11月-81 JONES 02-4月 -81
JAMES 950 03-12月-81 BLAKE 01-5月 -81
FORD 3000 03-12月-81 CLARK 09-6月 -81
MILLER 1300 23-1月 -82 TURNER 08-9月 -81
SCOTT 3000 19-4月 -87 MARTIN 28-9月 -81
ADAMS 1100 23-5月 -87 KING 17-11月-81
aaa 3 12-8月 -04 JAMES 03-12月-81
aaa 08-11月-04 FORD 03-12月-81
aaa 1 20-11月-04 MILLER 23-1月 -82
aaa 2 29-11月-04 SCOTT 19-4月 -87
已选择18行。
order by hiredate asc rows 5 preceding: order by之后，取当前行的前5行+当前行作为窗口(共6行)。
2、"range unbounded preceding"
range unbounded preceding会把当前行之前的所有行都包含进来，但当partition by时:
SQL> select deptno,ename,sal,
2 sum(sal) over
3 (partition by deptno order by deptno range unbounded preceding) sumsal
4 from emp;
DEPTNO ENAME SAL SUMSAL
---------- -------- ------ ----------
0 aaa 3
0 aaa 1 3
0 aaa 2 3
10 Clark 2450 3750
10 Miller 1300 3750
20 Smith 800 10875
20 Scott 3000 10875
20 Ford 3000 10875
20 Adams 1100 10875
20 Jones 2975 10875——10875=800+3000+3000+1100+2975
30 Allen 1600 9400
30 James 95 9400
30 Turner 1500 9400
30 Blake 2850 9400
30 Martin 1250 9400
30 Ward 1250 9400
已选择16行。
SQL> select deptno,ename,sal,
2 sum(sal) over
3 (order by deptno range unbounded preceding) sumsal
4 from emp;
DEPTNO ENAME SAL SUMSAL
---------- -------- ----- ---------
0 aaa 3
0 aaa 1 3
0 aaa 2 3
10 Clark 2450 3753
10 Miller 1300 3753
20 Smith 800 14628
20 Scott 3000 14628
20 Ford 3000 14628
20 Adams 1100 14628
20 Jones 2975 14628
30 Allen 1600 24028
30 James 950 24028
30 Turner 1500 24028
30 Blake 2850 24028
30 Martin 1250 24028
30 Ward 1250 24028
已选择16行。
这SQL句子和下面这SQL是等价的:
select deptno,ename,sal,
sum(sal) over (order by deptno) sumsal
from emp;
因为order by的默认窗口总是从结果集的第一行开始到它分组的最后一行。
而partiton by的默认窗口总是从分区的第一行开始。
3、range窗口: "range 100 preceding"
这个子句只适用于number和date，而且只能order by一列。
如果over()里asc排列，意思是[number-100,number]这样一个闭区间是它的窗口。
如果over()里desc排列，意思是[number,number+100]这样一个闭区间是它的窗口。
下面看一个date的:
SQL> select ename,sal,hiredate,hiredate-100 windowtop,
2 first_value(ename) over
3 (order by hiredate asc range 100 preceding) first_ename,
4 first_value(hiredate) over
5 (order by hiredate asc range 100 preceding) first_hiredate
6 from emp
7 order by hiredate asc;
ENAME SAL HIREDATE WINDOWTOP FIRST_ENAME FIRST_HIRE
------- ----- ------------ ---------------- ----------------- ---------------
SMITH 800 17-12月-80 08-9月 -80 SMITH 17-12月-80
ALLEN 1600 20-2月 -81 12-11月-80 SMITH 17-12月-80
WARD 1250 22-2月 -81 14-11月-80 SMITH 17-12月-80
JONES 2975 02-4月 -81 23-12月-80 ALLEN 20-2月 -81
BLAKE 2850 01-5月 -81 21-1月 -81 ALLEN 20-2月 -81
CLARK 2450 09-6月 -81 01-3月 -81 JONES 02-4月 -81
TURNER 1500 08-9月 -81 31-5月 -81 CLARK 09-6月 -81
MARTIN 1250 28-9月 -81 20-6月 -81 TURNER 08-9月 -81
KING 5000 17-11月-81 09-8月 -81 TURNER 08-9月 -81
FORD 3000 03-12月-81 25-8月 -81 TURNER 08-9月 -81
JAMES 950 03-12月-81 25-8月 -81 TURNER 08-9月 -81
MILLER 1300 23-1月 -82 15-10月-81 KING 17-11月-81
SCOTT 3000 19-4月 -87 09-1月 -87 SCOTT 19-4月 -87
ADAMS 1100 23-5月 -87 12-2月 -87 SCOTT 19-4月 -87
aaa 3 12-8月 -04 04-5月 -04 aaa 12-8月 -04
aaa 08-11月-04 31-7月 -04 aaa 12-8月 -04
aaa 1 20-11月-04 12-8月 -04 aaa 12-8月 -04
aaa 2 29-11月-04 21-8月 -04 aaa 08-11月-04
已选择18行。
first_value返回[hiredate-100,hiredate]这个区间内的离hiredate-100最近的一个值。
下面这句可以看出WINDOW里包含的记录个数:
SQL> select ename,sal,hiredate,hiredate-100 windowtop,
2 count(hiredate) over
3 (order by hiredate asc range 100 preceding) count_window
4 from emp;
ENAME SAL HIREDATE WINDOWTOP COUNT_WINDOW
-------- ------- ------------ --------------- ------------------
SMITH 800 17-12月-80 08-9月 -80 1
ALLEN 1600 20-2月 -81 12-11月-80 2
WARD 1250 22-2月 -81 14-11月-80 3
JONES 2975 02-4月 -81 23-12月-80 3
BLAKE 2850 01-5月 -81 21-1月 -81 4
CLARK 2450 09-6月 -81 01-3月 -81 3
TURNER 1500 08-9月 -81 31-5月 -81 2
MARTIN 1250 28-9月 -81 20-6月 -81 2
KING 5000 17-11月-81 09-8月 -81 3
JAMES 950 03-12月-81 25-8月 -81 5
FORD 3000 03-12月-81 25-8月 -81 5
MILLER 1300 23-1月 -82 15-10月-81 4
SCOTT 3000 19-4月 -87 09-1月 -87 1
ADAMS 1100 23-5月 -87 12-2月 -87 2
aaa 3 12-8月 -04 04-5月 -04 1
aaa 08-11月-04 31-7月 -04 2
aaa 1 20-11月-04 12-8月 -04 3
aaa 2 29-11月-04 21-8月 -04 3
已选择18行。
下面看一个desc的例子:
SQL> select ename,sal,hiredate,hiredate+100 windowtop,
2 first_value(ename) over
3 (order by hiredate desc range 100 preceding) first_ename,
4 first_value(hiredate) over
5 (order by hiredate desc range 100 preceding) first_hiredate
6 from emp
7 order by hiredate desc;
ENAME SAL HIREDATE WINDOWTOP FIRST_ENAME FIRST_HIRE
-------- ----- ------------- ---------------- ---------------- ---------------
aaa 2 29-11月-04 09-3月 -05 aaa 29-11月-04
aaa 1 20-11月-04 28-2月 -05 aaa 29-11月-04
aaa 08-11月-04 16-2月 -05 aaa 29-11月-04
aaa 3 12-8月 -04 20-11月-04 aaa 20-11月-04
ADAMS 1100 23-5月 -87 31-8月 -87 ADAMS 23-5月 -87
SCOTT 3000 19-4月 -87 28-7月 -87 ADAMS 23-5月 -87
MILLER 1300 23-1月 -82 03-5月 -82 MILLER 23-1月 -82
FORD 3000 03-12月-81 13-3月 -82 MILLER 23-1月 -82
JAMES 950 03-12月-81 13-3月 -82 MILLER 23-1月 -82
KING 5000 17-11月-81 25-2月 -82 MILLER 23-1月 -82
MARTIN 1250 28-9月 -81 06-1月 -82 FORD 03-12月-81
TURNER 1500 08-9月 -81 17-12月-81 FORD 03-12月-81
CLARK 2450 09-6月 -81 17-9月 -81 TURNER 08-9月 -81
BLAKE 2850 01-5月 -81 09-8月 -81 CLARK 09-6月 -81
JONES 2975 02-4月 -81 11-7月 -81 CLARK 09-6月 -81
WARD 1250 22-2月 -81 02-6月 -81 BLAKE 01-5月 -81
ALLEN 1600 20-2月 -81 31-5月 -81 BLAKE 01-5月 -81
SMITH 800 17-12月-80 27-3月 -81 WARD 22-2月 -81
已选择18行。
4、窗口总结
1、unbounded preceding:从当前分区的第一行开始，到当前行结束。
2、current row:从当前行开始，也结束于当前行。
3、[numeric expression] preceding:对于rows来说从当前行之前的第[numeric expression]行开始，到当前行结束。对range来说从小于数值
表达式的值开始，到当前行结束。
4、[numeric expression] following:与[numeric expression] preceding相反。
一个preceding和following混合的例子:
SQL> select ename,hiredate,
2 first_value(ename) over
3 (order by hiredate asc range between 100 preceding and 100 following) as first_ename,
4 last_value(ename) over
5 (order by hiredate asc range between 100 preceding and 100 following) as last_ename
6 from emp
7 order by hiredate asc;
ENAME HIREDATE FIRST_ENAME LAST_ENAME
-------------------- ------------ -------------------- --------------------
SMITH 17-12月-80 SMITH WARD
ALLEN 20-2月 -81 SMITH BLAKE
WARD 22-2月 -81 SMITH BLAKE
JONES 02-4月 -81 ALLEN CLARK
BLAKE 01-5月 -81 ALLEN CLARK
CLARK 09-6月 -81 JONES TURNER
TURNER 08-9月 -81 CLARK JAMES
MARTIN 28-9月 -81 TURNER JAMES
KING 17-11月-81 TURNER MILLER
FORD 03-12月-81 TURNER MILLER
JAMES 03-12月-81 TURNER MILLER
MILLER 23-1月 -82 KING MILLER
SCOTT 19-4月 -87 SCOTT ADAMS
ADAMS 23-5月 -87 SCOTT ADAMS
aaa 12-8月 -04 aaa aaa
aaa 08-11月-04 aaa aaa
aaa 20-11月-04 aaa aaa
aaa 29-11月-04 aaa aaa
已选择18行。
扫一扫
3085
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
