分析函数一：应用领域

● 计算运行的总数         --- 逐行地显示一个部门的累计工资。每行包括前面各行工资的总和。
● 查找一组内的百分数     --- 显示在某些部门中付给个人的总工资的百分数。将他们的工资与该部门的工资总和相除。
● 前N个查询            --- 按地区查找前N个工资最高的人或前N份最高的销售款
● 计算正在流动的平均值     --- 将当前行的值与前N行的值加在一起求平均值
● 执行带等级的查询        --- 显示一个部门内一个人工资的相关等级

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;

已选择10000行。

已用时间:  00: 00: 35.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=10000 Bytes=290000)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=5 Card=510 Bytes=14790)
   3    0   SORT (AGGREGATE)
   4    3     TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=10 Bytes=290)
   5    4       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=2)
   6    0   SORT (AGGREGATE)
   7    6     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=10 Bytes=250)
   8    0   SORT (ORDER BY) (Cost=70 Card=10000 Bytes=290000)
   9    8     TABLE ACCESS (FULL) OF 'T' (Cost=5 Card=10000 Bytes=290000)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     812993  consistent gets
          0  physical reads
          0  redo size
     512934  bytes sent via SQL*Net to client
       4884  bytes received via SQL*Net from client
        401  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

SQL> SELECT ename, deptno, sal,
  2        SUM(sal) over
  3          (ORDER BY deptno, ename) running_total,
  4        SUM(sal) over
  5          (PARTITION BY deptno
  6           ORDER BY ename) department_total,
  7        row_number() over
  8          (PARTITION BY deptno
  9           ORDER BY ename) seq
 10      FROM t emp
 11   ORDER BY deptno, ename;

已选择10000行。

已用时间:  00: 00: 01.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=10000 Bytes=290000)
   1    0   WINDOW (SORT) (Cost=5 Card=10000 Bytes=290000)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=5 Card=10000 Bytes=290000)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         53  consistent gets
          0  physical reads
          0  redo size
     512934  bytes sent via SQL*Net to client
       4884  bytes received via SQL*Net from client
        401  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed