Expert 101 Oracle 分析函式

一、 一個查詢例子：[php]

SQL> break on deptno skip 1

SQL> select ename,deptno,sal,

2 sum(sal) over (order by deptno,ename) running_total,

3 sum(sal) over (partition by deptno order by ename) department_total,

4 row_number() over (partition by deptno order by ename) seq

5 from emp;[/php]

[@more@]

[php]ENAME DEPTNO SAL RUNNING_TOTAL DEPARTMENT_TOTAL SEQ

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

CLARK 10 2450 2450 2450 1

KING 5000 7450 7450 2

MILLER 1300 8750 8750 3

FORD 20 3000 11750 3000 1

JONES 2975 14725 5975 2

SMITH 800 15525 6775 3

ALLEN 30 1600 17125 1600 1

BLAKE 2850 19975 4450 2

JAMES 950 20925 5400 3

MARTIN 1250 22175 6650 4

TURNER 1500 23675 8150 5

WARD 1250 24925 9400 6

二、測試分析函式與傳統查詢的效率比較：

SQL> create table t

2 as

3 select object_name ename,

4 mod(object_id,50) deptno,

5 object_id sal

6 from all_objects

7 where rownum<=1000

8 /

Table created.

SQL> create index idx_t on t(deptno,ename);

Index created.

SQL> select ename,deptno,sal,

2 sum(sal) over (order by deptno,ename) running_total,

3 sum(sal) over (partition by deptno order by ename) department_total,

4 row_number() over (partition by deptno order by ename) seq

5 from t emp

6 order by deptno,ename

7 /

1000 rows selected.

Elapsed: 00:00:00.03

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 WINDOW (BUFFER)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T'

3 2 INDEX (FULL SCAN) OF 'IDX_T' (NON-UNIQUE)

Statistics

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

0 recursive calls

0 db block gets

231 consistent gets

5 physical reads

0 redo size

49242 bytes sent via SQL*Net to client

1229 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

1000 rows processed

SQL> select ename, deptno, sal,

2 (select sum(sal)

3 from t e2

4 where e2.deptno < emp.deptno

5 or (e2.deptno = emp.deptno and e2.ename <= emp.ename ))

6 running_total,

7 (select sum(sal)

8 from t e3

9 where e3.deptno = emp.deptno

10 and e3.ename <= emp.ename)

11 department_total,

12 (select count(ename)

13 from t e3

14 where e3.deptno = emp.deptno

15 and e3.ename <= emp.ename)

16 seq

17 from t emp

18 order by deptno, ename

19 /

1000 rows selected.

Elapsed: 00:00:01.02

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'

2 1 INDEX (FULL SCAN) OF 'IDX_T' (NON-UNIQUE)

Statistics

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

0 recursive calls

0 db block gets

119344 consistent gets

0 physical reads

0 redo size

49242 bytes sent via SQL*Net to client

1229 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1000 rows processed

可以看到，用分析函式全部執行了0.03秒，發生了231個邏輯IO。沒有利用分析函式的則執行了將近1.02秒，發生高達119244個邏輯IO。有沒利用分析函式的效率是不言而喻的。

三、分析函式語法：

FUNCTION_NAME(,,…)

OVER

( )

order by 區別：

SQL> select ename,sal,avg(sal) over(order by ename)

2 from emp

3 /

ENAME SAL AVG(SAL)OVER(ORDERBYENAME)

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

ALLEN 1600 1600

BLAKE 2850 2225

CLARK 2450 2300

FORD 3000 2475

JAMES 950 2170

JONES 2975 2304.16667

KING 5000 2689.28571

MARTIN 1250 2509.375

MILLER 1300 2375

SMITH 800 2217.5

TURNER 1500 2152.27273

WARD 1250 2077.08333

12 rows selected.

有order by的利用當前行及其之前的行進行統計。

Elapsed: 00:00:00.00

SQL> select ename,sal,avg(sal) over()

2 from emp

3 /

ENAME SAL AVG(SAL)OVER()

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

SMITH 800 2077.08333

ALLEN 1600 2077.08333

WARD 1250 2077.08333

JONES 2975 2077.08333

MARTIN 1250 2077.08333

BLAKE 2850 2077.08333

CLARK 2450 2077.08333

KING 5000 2077.08333

TURNER 1500 2077.08333

JAMES 950 2077.08333

FORD 3000 2077.08333

MILLER 1300 2077.08333

12 rows selected.

無order by子句的利用全部值進行統計。

1 select ename, deptno,sal,

2 sum(sal) over (order by ename, deptno) sum_ename_deptno,

3 sum(sal) over (order by deptno, ename) sum_deptno_ename

4 from emp

5* order by ename, deptno

SQL> /

ENAME DEPTNO SAL SUM_ENAME_DEPTNO SUM_DEPTNO_ENAME

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

ALLEN 30 1600 1600 17125

BLAKE 30 2850 4450 19975

CLARK 10 2450 6900 2450

FORD 20 3000 9900 11750

JAMES 30 950 10850 20925

JONES 20 2975 13825 14725

KING 10 5000 18825 7450

MARTIN 30 1250 20075 22175

MILLER 10 1300 21375 8750

SMITH 20 800 22175 15525

TURNER 30 1500 23675 23675

WARD 30 1250 24925 24925

第一行ename為ALLEN，他的工具為1600，故總計工資也為1600。而他的deptno為30，根據DEPTNO排序計算的話，必須包括從10至當前行的值，故為17125。

1 select ename, deptno,sal,

2 sum(sal) over (order by ename, deptno) sum_ename_deptno,

3 sum(sal) over (order by deptno, ename) sum_deptno_ename

4 from emp

5* order by deptno,ename

SQL> /

ENAME DEPTNO SAL SUM_ENAME_DEPTNO SUM_DEPTNO_ENAME

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

CLARK 10 2450 6900 2450

KING 10 5000 18825 7450

MILLER 10 1300 21375 8750

FORD 20 3000 9900 11750

JONES 20 2975 13825 14725

SMITH 20 800 22175 15525

ALLEN 30 1600 1600 17125

BLAKE 30 2850 4450 19975

JAMES 30 950 10850 20925

MARTIN 30 1250 20075 22175

TURNER 30 1500 23675 23675

WARD 30 1250 24925 24925

5、Windowing子句：給出一個定義變化或固定的資料視窗方法，分析函式將對這些資料進行操作。必須使用order by子句。

（1、）ROWS視窗：包括在視窗中的行的物理數。

部門前兩行僱員與當前行工資總和：

1 select deptno,ename,sal ,

2 sum(sal) over (partition by deptno order by ename rows 2 preceding)

3 sliending_total

4 from emp

5* order by deptno,ename

SQL> /

DEPTNO ENAME SAL SLIENDING_TOTAL

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

10 CLARK 2450 2450

KING 5000 7450

MILLER 1300 8750

20 FORD 3000 3000

JONES 2975 5975

SMITH 800 6775

30 ALLEN 1600 1600

BLAKE 2850 4450

JAMES 950 5400

MARTIN 1250 5050

TURNER 1500 3700

WARD 1250 4000

按hiredate升序排序，提取包括當前行在內及其前5行的第一個員工資訊：

SQL> select ename,hiredate,sal,

2 first_value(ename) over

3 (order by hiredate rows 5 preceding) ename_prec,

4 first_value(hiredate) over

5 (order by hiredate rows 5 preceding) hiredate_prec

6 from emp

7 order by hiredate

8 /

ENAME HIREDATE SAL ENAME_PREC HIREDATE_

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

SMITH 17-DEC-80 800.00 SMITH 17-DEC-80

ALLEN 20-FEB-81 1600.00 SMITH 17-DEC-80

WARD 22-FEB-81 1250.00 SMITH 17-DEC-80

JONES 02-APR-81 2975.00 SMITH 17-DEC-80

BLAKE 01-MAY-81 2850.00 SMITH 17-DEC-80

CLARK 09-JUN-81 2450.00 SMITH 17-DEC-80

TURNER 08-SEP-81 1500.00 ALLEN 20-FEB-81

MARTIN 28-SEP-81 1250.00 WARD 22-FEB-81

KING 17-NOV-81 5000.00 JONES 02-APR-81

JAMES 03-DEC-81 950.00 BLAKE 01-MAY-81

FORD 03-DEC-81 3000.00 CLARK 09-JUN-81

MILLER 23-JAN-82 1300.00 TURNER 08-SEP-81

按hiredatet降序排序，包括當前行及其前5行求值：

SQL> select ename,hiredate,sal,

2 first_value(ename) over

3 (order by hiredate desc rows 5 preceding) ename_prec,

4 first_value(hiredate) over

5 (order by hiredate desc rows 5 preceding) hiredate_prec

6 from emp

7 order by hiredate desc

8 /

ENAME HIREDATE SAL ENAME_PREC HIREDATE_

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

MILLER 23-JAN-82 1300.00 MILLER 23-JAN-82

JAMES 03-DEC-81 950.00 MILLER 23-JAN-82

FORD 03-DEC-81 3000.00 MILLER 23-JAN-82

KING 17-NOV-81 5000.00 MILLER 23-JAN-82

MARTIN 28-SEP-81 1250.00 MILLER 23-JAN-82

TURNER 08-SEP-81 1500.00 MILLER 23-JAN-82

CLARK 09-JUN-81 2450.00 JAMES 03-DEC-81

BLAKE 01-MAY-81 2850.00 FORD 03-DEC-81

JONES 02-APR-81 2975.00 KING 17-NOV-81

WARD 22-FEB-81 1250.00 MARTIN 28-SEP-81

ALLEN 20-FEB-81 1600.00 TURNER 08-SEP-81

SMITH 17-DEC-80 800.00 CLARK 09-JUN-81

Hiredate升序或降序排序後，當前行之前5行求平均工資及其統計行數：

SQL> select ename,hiredate,sal,

2 avg(sal) over

3 (order by hiredate asc rows 5 preceding) before_avg,

4 count(*) over

5 (order by hiredate asc rows 5 preceding) before_obs,

6 avg(sal) over

7 (order by hiredate desc rows 5 preceding) after_avg,

8 count(*) over

9 (order by hiredate desc rows 5 preceding) after_obs

10 from emp

11 order by hiredate

12 /

ENAME HIREDATE SAL BEFORE_AVG BEFORE_OBS AFTER_AVG AFTER_OBS

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

SMITH 17-DEC-80 800.00 800.00 1.00 1987.50 6.00

ALLEN 20-FEB-81 1600.00 1200.00 2.00 2104.17 6.00

WARD 22-FEB-81 1250.00 1216.67 3.00 2045.83 6.00

JONES 02-APR-81 2975.00 1656.25 4.00 2670.83 6.00

BLAKE 01-MAY-81 2850.00 1895.00 5.00 2675.00 6.00

CLARK 09-JUN-81 2450.00 1987.50 6.00 2358.33 6.00

TURNER 08-SEP-81 1500.00 2104.17 6.00 2166.67 6.00

MARTIN 28-SEP-81 1250.00 2045.83 6.00 2300.00 5.00

KING 17-NOV-81 5000.00 2670.83 6.00 2562.50 4.00

JAMES 03-DEC-81 950.00 2333.33 6.00 1125.00 2.00

FORD 03-DEC-81 3000.00 2358.33 6.00 1750.00 3.00

MILLER 23-JAN-82 1300.00 2166.67 6.00 1300.00 1.00

（2、）Range視窗：根據where條件將行集中在一起。如range N preceding，將在組內擁有當前行以前N行的組合。只能用於數字和日期的資料型別比較。

例：按hiredate升序排序，求每一行前100天之內的第一個員工及對應入職日期（升序時，由於是求當前行之前範圍的值，因此必須是日期減去100）：

SQL> select ename,sal,hiredate,hiredate-100,

2 first_value(ename)

3 over

[/php]

4