众所周知，Oracle的分析函数在BI类的分析数据库中起到了非常大的作用，不仅仅是因为它能做的事情非常的丰富，同时，分析函数作为Oracle的内部实现，较之其他的实现形式（例如使用group by实现）效率也是出奇的高。
通过这个例子展示一下分析函数的一点点魅力。
1.样本数据如下
sec@ora10g> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- --------- ---- ------------------- ----- ---- ------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
2.实验目标
从上面的员工信息表emp中得到以下六列信息：
1）部门号
2）员工名字
3）工资
4）部门工资总数
5）员工工资占所在部门总工资数的百分比
6）员工工资占所总工资数的百分比
3.使用Oracle的分析函数实现如下
sec@ora10g> select deptno, ename, sal,
2 sum(sal)
3 over(partition by deptno order by sal, ename ) cum_sal,
4 round(100*ratio_to_report(sal)
5 over(partition by deptno),1) pct_dept,
6 round(100*ratio_to_report(sal)
7 over(),1) pct_overall
8 from emp
9 order by deptno,sal
10 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
10 CLARK 2450 3750 28 8.4
10 KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
20 ADAMS 1100 1900 10.1 3.8
20 JONES 2975 4875 27.4 10.2
20 FORD 3000 7875 27.6 10.3
20 SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
30 MARTIN 1250 2200 13.3 4.3
30 WARD 1250 3450 13.3 4.3
30 TURNER 1500 4950 16 5.2
30 ALLEN 1600 6550 17 5.5
30 BLAKE 2850 9400 30.3 9.8
14 rows selected.
如果上面的显示效果不是很漂亮，可以使用break on在SQL*Plus中美化一下
sec@ora10g> break on deptno skip 1
sec@ora10g> select deptno, ename, sal,
2 sum(sal)
3 over(partition by deptno order by sal, ename ) cum_sal,
4 round(100*ratio_to_report(sal)
5 over(partition by deptno),1) pct_dept,
6 round(100*ratio_to_report(sal)
7 over(),1) pct_overall
8 from emp
9 order by deptno,sal
10 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
CLARK 2450 3750 28 8.4
KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
ADAMS 1100 1900 10.1 3.8
JONES 2975 4875 27.4 10.2
FORD 3000 7875 27.6 10.3
SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
MARTIN 1250 2200 13.3 4.3
WARD 1250 3450 13.3 4.3
TURNER 1500 4950 16 5.2
ALLEN 1600 6550 17 5.5
BLAKE 2850 9400 30.3 9.8
14 rows selected.
OK，这时的显示效果是不是很直观，类似于报表统计之后的效果，这就是SQL的魅力。
4.解释一下上面用到的分析函数
1）DEPTNO，ENAME和SAL三列就不用多说了，仅仅是字段的罗列而已。
2）CUM_SAL列使用的分析函数是：sum(sal) over(partition by deptno order by sal, ename )
功能：按照部门号将每一位员工的工资进行累加，最终得到这个部门的部门工资总数。
3）PCT_DEPT列使用到的分析函数是：round(100*ratio_to_report(sal) over (partition by deptno),1)
功能：按照部门号deptno对工资sal计算得到员工工资占所在部门总工资数的百分比
4）PCT_OVERALL列使用的分析函数是：round(100*ratio_to_report(sal) over(),1) pct_overall
功能：over参数为空，表示计算单位是所有的数据，最终得到员工工资占所总工资数的百分比
5.如果不用分析函数如何改写上面的SQL语句呢？可以做如下的改写
sec@ora10g> break on deptno skip 1
sec@ora10g> select emp.deptno, emp.ename, emp.sal,
2 sum(emp4.sal) cum_sal,
3 round(100*emp.sal/emp2.sal_by_dept,1) pct_dept,
4 round(100*emp.sal/emp3.sal_overall,1) pct_overall
5 from emp,
6 (select deptno, sum(sal) sal_by_dept
7 from emp
8 group by deptno ) emp2,
9 (select sum(sal) sal_overall
10 from emp ) emp3,
11 emp emp4
12 where emp.deptno = emp2.deptno
13 and emp.deptno = emp4.deptno
14 and (emp.sal > emp4.sal or
15 (emp.sal = emp4.sal and emp.ename >= emp4.ename))
16 group by emp.deptno, emp.ename, emp.sal,
17 round(100*emp.sal/emp2.sal_by_dept,1),
18 round(100*emp.sal/emp3.sal_overall,1)
19 order by deptno, sal
20 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
CLARK 2450 3750 28 8.4
KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
ADAMS 1100 1900 10.1 3.8
JONES 2975 4875 27.4 10.2
FORD 3000 7875 27.6 10.3
SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
MARTIN 1250 2200 13.3 4.3
WARD 1250 3450 13.3 4.3
TURNER 1500 4950 16 5.2
ALLEN 1600 6550 17 5.5
BLAKE 2850 9400 30.3 9.8
14 rows selected.
6.小结
经测试表明，使用分析函数在数据量增加的情况下，执行效率是呈线性降低的，但是使用group by的实现方式，执行效率是呈指数下降！（您可以参考一下【附】中的测试结果）
因此，分析函数的实现方式可以成百上千的提高SQL的效率。
虽然对分析函数的学习和接触有一个过程，不过在对海量数据进行分析时，漫长的执行等待是谁都无法容忍的，这也是技术的魅力所在。
【附】分析函数与普通查询方法的性能测试结果
第一次测试：测试记录数为14条
分析函数耗时：Elapsed: 00:00:00.02
普通查询耗时：Elapsed: 00:00:00.09
第二次测试：测试记录数为1792条
分析函数耗时：Elapsed: 00:00:00.06
普通查询耗时：Elapsed: 00:00:01.37
第三次测试：测试记录数为14336条
分析函数耗时：Elapsed: 00:00:00.24
普通查询耗时：Elapsed: 00:01:17.43
第四次试验：测试记录数为1835008条
分析函数耗时：Elapsed: 00:00:26.63
普通查询耗时：当emp表的数据量达到近2百万的数量级的时候，普通查询方法已经无法返回结果，时间超过50031秒，而且时间还在累计，我无法等到这个结果的返回，手工cancel了。
-- The End --
通过这个例子展示一下分析函数的一点点魅力。
1.样本数据如下
sec@ora10g> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ------- --------- ---- ------------------- ----- ---- ------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1982-12-09 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1983-01-12 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
2.实验目标
从上面的员工信息表emp中得到以下六列信息：
1）部门号
2）员工名字
3）工资
4）部门工资总数
5）员工工资占所在部门总工资数的百分比
6）员工工资占所总工资数的百分比
3.使用Oracle的分析函数实现如下
sec@ora10g> select deptno, ename, sal,
2 sum(sal)
3 over(partition by deptno order by sal, ename ) cum_sal,
4 round(100*ratio_to_report(sal)
5 over(partition by deptno),1) pct_dept,
6 round(100*ratio_to_report(sal)
7 over(),1) pct_overall
8 from emp
9 order by deptno,sal
10 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
10 CLARK 2450 3750 28 8.4
10 KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
20 ADAMS 1100 1900 10.1 3.8
20 JONES 2975 4875 27.4 10.2
20 FORD 3000 7875 27.6 10.3
20 SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
30 MARTIN 1250 2200 13.3 4.3
30 WARD 1250 3450 13.3 4.3
30 TURNER 1500 4950 16 5.2
30 ALLEN 1600 6550 17 5.5
30 BLAKE 2850 9400 30.3 9.8
14 rows selected.
如果上面的显示效果不是很漂亮，可以使用break on在SQL*Plus中美化一下
sec@ora10g> break on deptno skip 1
sec@ora10g> select deptno, ename, sal,
2 sum(sal)
3 over(partition by deptno order by sal, ename ) cum_sal,
4 round(100*ratio_to_report(sal)
5 over(partition by deptno),1) pct_dept,
6 round(100*ratio_to_report(sal)
7 over(),1) pct_overall
8 from emp
9 order by deptno,sal
10 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
CLARK 2450 3750 28 8.4
KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
ADAMS 1100 1900 10.1 3.8
JONES 2975 4875 27.4 10.2
FORD 3000 7875 27.6 10.3
SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
MARTIN 1250 2200 13.3 4.3
WARD 1250 3450 13.3 4.3
TURNER 1500 4950 16 5.2
ALLEN 1600 6550 17 5.5
BLAKE 2850 9400 30.3 9.8
14 rows selected.
OK，这时的显示效果是不是很直观，类似于报表统计之后的效果，这就是SQL的魅力。
4.解释一下上面用到的分析函数
1）DEPTNO，ENAME和SAL三列就不用多说了，仅仅是字段的罗列而已。
2）CUM_SAL列使用的分析函数是：sum(sal) over(partition by deptno order by sal, ename )
功能：按照部门号将每一位员工的工资进行累加，最终得到这个部门的部门工资总数。
3）PCT_DEPT列使用到的分析函数是：round(100*ratio_to_report(sal) over (partition by deptno),1)
功能：按照部门号deptno对工资sal计算得到员工工资占所在部门总工资数的百分比
4）PCT_OVERALL列使用的分析函数是：round(100*ratio_to_report(sal) over(),1) pct_overall
功能：over参数为空，表示计算单位是所有的数据，最终得到员工工资占所总工资数的百分比
5.如果不用分析函数如何改写上面的SQL语句呢？可以做如下的改写
sec@ora10g> break on deptno skip 1
sec@ora10g> select emp.deptno, emp.ename, emp.sal,
2 sum(emp4.sal) cum_sal,
3 round(100*emp.sal/emp2.sal_by_dept,1) pct_dept,
4 round(100*emp.sal/emp3.sal_overall,1) pct_overall
5 from emp,
6 (select deptno, sum(sal) sal_by_dept
7 from emp
8 group by deptno ) emp2,
9 (select sum(sal) sal_overall
10 from emp ) emp3,
11 emp emp4
12 where emp.deptno = emp2.deptno
13 and emp.deptno = emp4.deptno
14 and (emp.sal > emp4.sal or
15 (emp.sal = emp4.sal and emp.ename >= emp4.ename))
16 group by emp.deptno, emp.ename, emp.sal,
17 round(100*emp.sal/emp2.sal_by_dept,1),
18 round(100*emp.sal/emp3.sal_overall,1)
19 order by deptno, sal
20 /
DEPTNO ENAME SAL CUM_SAL PCT_DEPT PCT_OVERALL
---------- ---------- ---------- ---------- ---------- -----------
10 MILLER 1300 1300 14.9 4.5
CLARK 2450 3750 28 8.4
KING 5000 8750 57.1 17.2
20 SMITH 800 800 7.4 2.8
ADAMS 1100 1900 10.1 3.8
JONES 2975 4875 27.4 10.2
FORD 3000 7875 27.6 10.3
SCOTT 3000 10875 27.6 10.3
30 JAMES 950 950 10.1 3.3
MARTIN 1250 2200 13.3 4.3
WARD 1250 3450 13.3 4.3
TURNER 1500 4950 16 5.2
ALLEN 1600 6550 17 5.5
BLAKE 2850 9400 30.3 9.8
14 rows selected.
6.小结
经测试表明，使用分析函数在数据量增加的情况下，执行效率是呈线性降低的，但是使用group by的实现方式，执行效率是呈指数下降！（您可以参考一下【附】中的测试结果）
因此，分析函数的实现方式可以成百上千的提高SQL的效率。
虽然对分析函数的学习和接触有一个过程，不过在对海量数据进行分析时，漫长的执行等待是谁都无法容忍的，这也是技术的魅力所在。
【附】分析函数与普通查询方法的性能测试结果
第一次测试：测试记录数为14条
分析函数耗时：Elapsed: 00:00:00.02
普通查询耗时：Elapsed: 00:00:00.09
第二次测试：测试记录数为1792条
分析函数耗时：Elapsed: 00:00:00.06
普通查询耗时：Elapsed: 00:00:01.37
第三次测试：测试记录数为14336条
分析函数耗时：Elapsed: 00:00:00.24
普通查询耗时：Elapsed: 00:01:17.43
第四次试验：测试记录数为1835008条
分析函数耗时：Elapsed: 00:00:26.63
普通查询耗时：当emp表的数据量达到近2百万的数量级的时候，普通查询方法已经无法返回结果，时间超过50031秒，而且时间还在累计，我无法等到这个结果的返回，手工cancel了。
-- The End --
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70034820/viewspace-2993996/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 中国OCM联盟隆重落户ITPUB社区，百位OCM已入驻，欢迎更多的OCM加入我们哦！
- OCMU联盟-一个有温度的联盟
- 丢掉盔甲，我们依然是骄傲的骑士——Secooler“三问四看”话认证培训
- 【数据安全】一次惊心动魄的ASM磁盘头损坏故障处理过程带来的深思
- 【参数】恢复db_recovery_file_dest_size参数为默认值“0”方法
- 【如何做快乐的DBA】ITPUB社区第一期博客专家专题“如何做快乐的DBA”
- 【Bitmap Index】B-Tree索引与Bitmap位图索引的锁代价比较研究
- 【RMAN】使用RMAN的Duplicate功能创建物理DataGuard报错（ORA-17627、ORA-17629）处理
- 【CUBE】Oracle分组函数之CUBE魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
