一、 一个查询例子：[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]

[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