【开发篇sql】分析函数（一）评级相关的函数

最新推荐文章于 2022-04-23 23:02:52 发布

cixlv48826

最新推荐文章于 2022-04-23 23:02:52 发布

阅读量222

点赞数

文章标签：大数据数据库

三，分析函数

分析函数计算基于一组行的聚合值，与聚合函数不同的是它为每个分组返回多行值。行的分组被叫做一个窗口，在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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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/

cixlv48826

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【开发篇sql】分析函数（一）评级相关的函数

三，分析函数分析函数计算基于一组行的聚合值，与聚合函数不同的是它为每个分组返回多行值。行的分组被叫做一个窗口，在analytic_clause子句中定义。具体的句法如下：Analytic_function (argu...
复制链接

扫一扫