教为学:Oracle SQL学习之路(三):分析函数之统计
前言
与统计相关的分析函数有哪些?
SUM
AVG
MAX/MIN
FIRST_VALUE/LAST_VALUE
等等。
我们从最最最基础的SUM、AVG开始吧。惯例,先上例子。
再上结果。
SUM(SAL)OVER()
--------------
29025
29025
29025
29025
29025
29025
29025
29025
29025
29025
29025
29025
29025
29025
和我们平时的sum聚合函数有什么区别?
和前几个分析函数又有什么区别?
平时sum只有一个结果,分析函数的sum有N个结果。
平时的分析函数over()里面有条件,这里没有任何东西。
Syntax
SUM函数:
AVG函数:
不出意外,嗯,又是一样的。
语法详解
Partition by就不详细叙述了。还是分类,order by呢?还是排序?
除了排序还有什么?再来一个例子就很明显了。
结果如下:
SAL
SUM(SAL)OVER(ORDERBYSAL)
800
800
950
1750
1100
2850
1250
5350
1250
5350
1300
6650
1500
8150
1600
9750
2450
12200
2850
15050
2975
18025
3000
24025
3000
24025
5000
29025
Order by除了有排序的作用,更重要的是有累加的作用。
看看avg:
结果:
SAL
AVG(SAL)OVER(ORDERBYSAL)
800
800
950
875
1100
950
1250
1070
1250
1070
1300
1108.333333333333333333333333333333333333
1500
1164.285714285714285714285714285714285714
1600
1218.75
2450
1355.555555555555555555555555555555555556
2850
1505
2975
1638.636363636363636363636363636363636364
3000
1848.076923076923076923076923076923076923
3000
1848.076923076923076923076923076923076923
5000
2073.214285714285714285714285714285714286
同样,这叫什么来着呢?累计平均。
这都比较简单,我们接下来要弄一个以前没有看见过的条件。那是什么?
同样看例子。
结果:
SAL
AVG
800
875
950
950
1100
1100
1250
1200
1250
1266.666666666666666666666666666666666667
1300
1350
1500
1466.666666666666666666666666666666666667
1600
1850
2450
2300
2850
2758.333333333333333333333333333333333333
2975
2941.666666666666666666666666666666666667
3000
2991.666666666666666666666666666666666667
3000
3666.666666666666666666666666666666666667
5000
4000
表示上行加本身加上下一行的平均值。
这样的函数很多,我们现在不一一道来。
谈谈效率
相同效果sql的不同执行计划:
select
empno, ename, sal, sum(sal)over(order by empno) addsum
from
emp;
执行计划:
Execution Plan
----------------------------------------------------------
Plan hash value: 2726561287
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 210 | 2 (0)| 00:00:01 |
| 1 | WINDOW BUFFER | | 15 | 210 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 15 | 210 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_EMP | 15 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
937 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
非分析函数:
with
t as (select rownum px, a.* from emp a order by empno)
select
a.empno, a.ename, a.sal, sum(b.sal) addsum from t a, t b
where
a.px >=b.px group by a.empno,a.ename, a.sal
order
by a.empno;
执行计划:
Execution Plan
----------------------------------------------------------
Plan hash value: 4141007082
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 583 | 9 (34)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D662A_17B75F | | | | |
| 3 | COUNT | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 15 | 555 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_EMP | 15 | | 1 (0)| 00:00:01 |
| 6 | SORT GROUP BY | | 11 | 583 | 7 (43)| 00:00:01 |
| 7 | MERGE JOIN | | 11 | 583 | 6 (34)| 00:00:01 |
| 8 | SORT JOIN | | 15 | 405 | 3 (34)| 00:00:01 |
| 9 | VIEW | | 15 | 405 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662A_17B75F | 15 | 555 | 2 (0)| 00:00:01 |
|* 11 | SORT JOIN | | 15 | 390 | 3 (34)| 00:00:01 |
| 12 | VIEW | | 15 | 390 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662A_17B75F | 15 | 555 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access(INTERNAL_FUNCTION("A"."PX")>=INTERNAL_FUNCTION("B"."PX"))
filter(INTERNAL_FUNCTION("A"."PX")>=INTERNAL_FUNCTION("B"."PX"))
Statistics
----------------------------------------------------------
274 recursive calls
8 db block gets
56 consistent gets
1 physical reads
864 redo size
937 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
14 rows processed
Cost和读写,差距不是一点点!
分析函数一定效率高?
不,强制排序是它性能的短板。谁都知道排序是件力量活。