三,分析函数
分析函数计算基于一组行的聚合值,与聚合函数不同的是它为每个分组返回多行值。行的分组被叫做一个窗口,在analytic_clause子句中定义。
具体的句法如下:
Analytic_function (arguments) over (analytic_clause)
其中:
Analytic_function函数名,9i/10g中有26个分析函数,接下来会逐一分析。
analytic_clause:{Query_partition_clause } {order by clause} {windowing_clause}
使用over告知查询分析器函数为分析函数而不是聚合函数,且在随后的子句指出要在那些字段上做分析计算。
Query_partition_clause: partition by {expr}/{(expr)}
使用partition表示对数据进行分组,如果没有指定,则将全部的结果作为一个分组来对待。
Order by clause:order by expr {asc/desc} {null/{first/last}}
Order by添加一个默认的开窗子句,告知当前分组内的计算顺序。后面添加nulls last或者nulls first是正对null值的处理。
Window_clause:{rows/range}
定义分组内用于计算或操作的具体行的集合。
Range:
产生一个滑动窗口,在组中拥有指定的range的行,使用range时对order by限制为一列,使得其滑动窗口的范围为一维。
下面分几组来讲解分析函数的具体使用:
1, 评级函数row_number,rank dense_rank,percent_rank,cume_dist,ntile
先来看看row_number,现在要对scott用户下面的工人表emp按照薪水从小到大排序,看看哪些是困难户:
SQL> select a.empno, a.ename, a.sal, row_number() over(order by a.sal) num
2 from scott.emp a;
EMPNO ENAME SAL NUM
---------- ---------- ---------- ----------
7369 SMITH 800 1
7900 JAMES 950 2
7876 ADAMS 1100 3
7521 WARD 1250 4
7654 MARTIN 1250 5
7934 update 1300 6
7844 TURNER 1500 7
7499 ALLEN 1600 8
7566 JONES 2000 9
7782 CLARK 2450 10
7698 BLAKE 2850 11
EMPNO ENAME SAL NUM
---------- ---------- ---------- ----------
7788 SCOTT 3000 12
7902 FORD 3000 13
7839 KING 5000 14
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3145491563
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 196 | 4 (25)| 00:00:01 |
| 1 | WINDOW SORT | | 14 | 196 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 196 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
使用row_number()很容易为一个分组里面的行排序并加上序号(注意上面执行计划id为2的window sort。)
上述排序中12和13的工钱一样多,这样排序只是按照行的rowid来排的,默认是从小到大,可以具体看看:
SQL> select row_number() over(order by a.deptno) num, rowid, a.empno,a.deptno
2 from scott.emp a;
NUM ROWID EMPNO DEPTNO
---------- ------------------ ---------- ----------
1 AAANMFAAEAAAABEAAG 7782 10
2 AAANMFAAEAAAABEAAI 7839 10
3 AAANMFAAEAAAABEAAN 7934 10
4 AAANMFAAEAAAABEAAD 7566 20
5 AAANMFAAEAAAABEAAM 7902 20
6 AAANMFAAEAAAABEAAK 7876 20
7 AAANMFAAEAAAABEAAA 7369 20
8 AAANMFAAEAAAABEAAH 7788 20
9 AAANMFAAEAAAABEAAC 7521 30
10 AAANMFAAEAAAABEAAJ 7844 30
11 AAANMFAAEAAAABEAAB 7499 30
NUM ROWID EMPNO DEPTNO
---------- ------------------ ---------- ----------
12 AAANMFAAEAAAABEAAL 7900 30
13 AAANMFAAEAAAABEAAF 7698 30
14 AAANMFAAEAAAABEAAE 7654 30
14 rows selected.
通过看相同deptno的rowid就可以看出来这个规律。
现在需要对分不同部门来看部门内的工钱排名,且从大到小排列:
SQL> select a.empno,
2 a.ename,
3 a.deptno,
4 a.sal,
5 row_number() over(partition by a.deptno order by a.sal desc) num
6 from scott.emp a;
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7839 KING 10 5000 1
7782 CLARK 10 2450 2
7934 update 10 1300 3
7788 SCOTT 20 3000 1
7902 FORD 20 3000 2
7566 JONES 20 2000 3
7876 ADAMS 20 1100 4
7369 SMITH 20 800 5
7698 BLAKE 30 2850 1
7499 ALLEN 30 1600 2
7844 TURNER 30 1500 3
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7654 MARTIN 30 1250 4
7521 WARD 30 1250 5
7900 JAMES 30 950 6
14 rows selected.
如果每个部门只要取前三名的话,则可以稍稍修改查询:
SQL> select * from (
2 select a.empno,
3 a.ename,
4 a.deptno,
5 a.sal,
6 row_number() over(partition by a.deptno order by a.sal desc) num
7 from scott.emp a) where num <=3;
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7839 KING 10 5000 1
7782 CLARK 10 2450 2
7934 update 10 1300 3
7788 SCOTT 20 3000 1
7902 FORD 20 3000 2
7566 JONES 20 2000 3
7698 BLAKE 30 2850 1
7499 ALLEN 30 1600 2
7844 TURNER 30 1500 3
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 14 | 826 | 4 (25)| 00:00:01
|
|* 1 | VIEW | | 14 | 826 | 4 (25)| 00:00:01
|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 238 | 4 (25)| 00:00:01
|
| 3 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01
|
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM"<=3)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "A"."DEPTNO" ORDER BY
INTERNAL_FUNCTION("A"."SAL") DESC )<=3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
845 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
很容易想到用嵌套查询,在外层限定序号num的值不超过3,注意看看执行计划id为2的行,使用的是WINDOW SORT PUSHED RANK
下面就来看看rank是如何实现上面的这个求前三甲的需求的:
SQL> select * from (
2 select a.empno,
3 a.ename,
4 a.deptno,
5 a.sal,
6 rank() over(partition by a.deptno order by a.sal desc) num
7 from scott.emp a) where num <=3;
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7839 KING 10 5000 1
7782 CLARK 10 2450 2
7934 update 10 1300 3
7788 SCOTT 20 3000 1
7902 FORD 20 3000 1
7566 JONES 20 2000 3
7698 BLAKE 30 2850 1
7499 ALLEN 30 1600 2
7844 TURNER 30 1500 3
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 14 | 826 | 4 (25)| 00:00:01
|
|* 1 | VIEW | | 14 | 826 | 4 (25)| 00:00:01
|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 238 | 4 (25)| 00:00:01
|
| 3 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01
|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM"<=3)
2 - filter(RANK() OVER ( PARTITION BY "A"."DEPTNO" ORDER BY
INTERNAL_FUNCTION("A"."SAL") DESC )<=3)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
842 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
好像写法是一样的,但是结果稍稍有所不同,rank会把相同的sal当作并列处理。但看执行机会和统计信息则发现基本一样(这里猜测oracle内部实现row_number和rank的主要算法是一样的)
Dense_rank和rank稍有不同,不过是序号不会像rank一样跳跃。再看一个例子:
SQL> select a.deptno,
2 a.job,
3 sum(a.sal),
4 dense_rank() over(partition by a.deptno order by sum(a.sal) desc) num
5 from scott.emp a
6 group by grouping sets((a.deptno),(a.job),(a.deptno, a.job),());
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
10 8750 1
10 PRESIDENT 5000 2
10 MANAGER 2450 3
10 CLERK 1300 4
20 9900 1
20 ANALYST 6000 2
20 MANAGER 2000 3
20 CLERK 1900 4
30 9400 1
30 SALESMAN 5600 2
30 MANAGER 2850 3
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
30 CLERK 950 4
28050 1
MANAGER 7300 2
ANALYST 6000 3
SALESMAN 5600 4
PRESIDENT 5000 5
CLERK 4150 6
18 rows selected.
以上是分部门和工种来求工钱总和,并且使用了grouping sets根据需要获得分组的排名。
Percent_rank也很容易理解:
当前行占分组内行的百分比,比如要知道当前的员工的工钱水平占整个部门内的什么水平,可以这样来看:
SQL> select a.empno,
2 a.ename,
3 a.deptno,
4 a.sal,
5 percent_rank() over(partition by a.deptno order by a.sal desc) num
6 from scott.emp a
7 ;
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7839 KING 10 5000 0
7782 CLARK 10 2450 .5
7934 update 10 1300 1
7788 SCOTT 20 3000 0
7902 FORD 20 3000 0
7566 JONES 20 2000 .5
7876 ADAMS 20 1100 .75
7369 SMITH 20 800 1
7698 BLAKE 30 2850 0
7499 ALLEN 30 1600 .2
7844 TURNER 30 1500 .4
EMPNO ENAME DEPTNO SAL NUM
---------- ---------- ---------- ---------- ----------
7654 MARTIN 30 1250 .6
7521 WARD 30 1250 .6
7900 JAMES 30 950 1
14 rows selected.
显然KING和CLARK都是部门内50%的富人,但是他们相差却非常之大,贫富差距往往是数据看不出来的,前面还没有用avg来求平均值,求了则更是粉饰了真正的现实。想到最近10年或者20年(从记事开始算起)以来的变迁,我们的“涉会注意郭家”是相当优越的,对内喜欢讲人均,对外喜欢将总和,就是转移一下贫富差距的视线,现在的情况是大寡头占有了涉会绝大多数财富资源,很少数的人抢占了大多数人的幸福生活。不要觉得意外,其基本原理本来就是这样:
让一部分人先富起来,带动全民富裕。
这句看着很搞笑了。
下面继续来看看cume_dist,和percent_rank差不多,看一个例子就可以知道了:
SQL> select a.deptno,
2 a.job,
3 sum(a.sal),
4 cume_dist() over(partition by a.deptno order by sum(a.sal) desc) num
5 from scott.emp a
6 group by rollup (a.deptno, a.job);
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
10 8750 .25
10 PRESIDENT 5000 .5
10 MANAGER 2450 .75
10 CLERK 1300 1
20 9900 .25
20 ANALYST 6000 .5
20 MANAGER 2000 .75
20 CLERK 1900 1
30 9400 .25
30 SALESMAN 5600 .5
30 MANAGER 2850 .75
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
30 CLERK 950 1
28050 1
13 rows selected.
而ntile是将行再分组,修改上述查询也很容易理解,ntile(2)将行分为2组,下面例子中4行的即为2,2,如果是3行,则2,1,其他雷同:
SQL> select a.deptno,
2 a.job,
3 sum(a.sal),
4 ntile(2) over(partition by a.deptno order by sum(a.sal) desc) num
5 from scott.emp a
6 group by rollup (a.deptno, a.job);
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
10 8750 1
10 PRESIDENT 5000 1
10 MANAGER 2450 2
10 CLERK 1300 2
20 9900 1
20 ANALYST 6000 1
20 MANAGER 2000 2
20 CLERK 1900 2
30 9400 1
30 SALESMAN 5600 1
30 MANAGER 2850 2
DEPTNO JOB SUM(A.SAL) NUM
---------- --------- ---------- ----------
30 CLERK 950 2
28050 1
13 rows selected.
有关评级函数,再说一个表结构设计时候的问题,现在有一个log表,记录某些操作的具体信息(操作事件,操作人,操作信息,开始时间,结束时间,操作对象id,操作状态等等),现在要对操作人考核,看看这个人总共操作了多少次,在操作中花了多少时间,每个状态值停了多少时间,从第一次开始操作,到最终完成操作又话了多少时间。好像没有什么是sql完成不了的,rank,row_number,或者lag,lead可能都可以用的上,不过个人觉得,像经常性查询的一些关键字段,应该在基表内有一定的冗余字段,关联或者计算在大数据量和高响应要求时给系统带来了负面影响,严格来讲分析函数应该尽可能多用于数据仓库或者分析报表库,而不是oltp的生产系统,但是往往情况不是这样。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16179598/viewspace-663141/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-663141/