总结笔记之【开发篇sql】

yellowlee · 发表于 2010-5-29 16:22

呵呵，谢谢！！
sql写了一部分，现在陆续贴上来，希望大家可以帮忙找找问题，或者提出可以扩展和深入的地方，将会在后续的sql优化中补充进去，

目前正在写备份方面的，下一个写plsql，希望大家支持，届时再借开发版跟大家分享一下，一起学习。

yellowlee · 发表于 2010-5-29 16:28

三，分析函数
分析函数计算基于一组行的聚合值，与聚合函数不同的是它为每个分组返回多行值。行的分组被叫做一个窗口，在analytic_clause子句中定义。
具体的句法如下：
Analytic_function (arguments) over (analytic_clause)
其中：
Analytic_function函数名，9i/10g中有26个分析函数，接下来会逐一分析。
analytic_clause:{Query_partition_clause } {order by clause} {windowing_clause}
使用over告知查询分析器函数为分析函数而不是聚合函数，且在随后的子句指出要在那些字段上做分析计算。
Query_partition_clause: partition by {expr}/{(expr)}
使用partition表示对数据进行分组，如果没有指定，则将全部的结果作为一个分组来对待。
Order by clause:order by expr {asc/desc} {null/{first/last}}
Order by添加一个默认的开窗子句，告知当前分组内的计算顺序。后面添加nulls last或者nulls first是正对null值的处理。
Window_clause:{rows/range}
定义分组内用于计算或操作的具体行的集合。
Range:
产生一个滑动窗口，在组中拥有指定的range的行，使用range时对order by限制为一列，使得其滑动窗口的范围为一维。
下面分几组来讲解分析函数的具体使用：
1, 评级函数row_number,rank dense_rank,percent_rank，cume_dist,ntile
先来看看row_number，现在要对scott用户下面的工人表emp按照薪水从小到大排序，看看哪些是困难户：
SQL> select a.empno, a.ename, a.sal, row_number() over(order by a.sal) num
  2 from scott.emp a;

   EMPNO ENAME          SAL       NUM
---------- ---------- ---------- ----------
   7369 SMITH          800       1
   7900 JAMES          950       2
   7876 ADAMS          1100       3
   7521 WARD          1250       4
   7654 MARTIN          1250       5
   7934 update          1300       6
   7844 TURNER          1500       7
   7499 ALLEN          1600       8
   7566 JONES          2000       9
   7782 CLARK          2450       10
   7698 BLAKE          2850       11

   EMPNO ENAME          SAL       NUM
---------- ---------- ---------- ----------
   7788 SCOTT          3000       12
   7902 FORD          3000       13
   7839 KING          5000       14

14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3145491563

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    | 14 | 196 |    4  (25)| 00:00:01 |
| 1 |  WINDOW SORT    |    | 14 | 196 |    4  (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP  | 14 | 196 |    3 (0)| 00:00:01 |
---------------------------------------------------------------------------
使用row_number()很容易为一个分组里面的行排序并加上序号（注意上面执行计划id为2的window sort。）
上述排序中12和13的工钱一样多，这样排序只是按照行的rowid来排的，默认是从小到大，可以具体看看：
SQL> select row_number() over(order by a.deptno) num, rowid, a.empno,a.deptno
  2 from scott.emp a;

   NUM ROWID                EMPNO    DEPTNO
---------- ------------------ ---------- ----------
      1 AAANMFAAEAAAABEAAG    7782       10
      2 AAANMFAAEAAAABEAAI    7839       10
      3 AAANMFAAEAAAABEAAN    7934       10
      4 AAANMFAAEAAAABEAAD    7566       20
      5 AAANMFAAEAAAABEAAM    7902       20
      6 AAANMFAAEAAAABEAAK    7876       20
      7 AAANMFAAEAAAABEAAA    7369       20
      8 AAANMFAAEAAAABEAAH    7788       20
      9 AAANMFAAEAAAABEAAC    7521       30
      10 AAANMFAAEAAAABEAAJ    7844       30
      11 AAANMFAAEAAAABEAAB    7499       30

   NUM ROWID                EMPNO    DEPTNO
---------- ------------------ ---------- ----------
      12 AAANMFAAEAAAABEAAL    7900       30
      13 AAANMFAAEAAAABEAAF    7698       30
      14 AAANMFAAEAAAABEAAE    7654       30

14 rows selected.
通过看相同deptno的rowid就可以看出来这个规律。
现在需要对分不同部门来看部门内的工钱排名，且从大到小排列：
SQL> select a.empno,
  2       a.ename,
  3       a.deptno,
  4       a.sal,
  5       row_number() over(partition by a.deptno order by a.sal desc) num
  6 from scott.emp a;

   EMPNO ENAME       DEPTNO       SAL       NUM
---------- ---------- ---------- ---------- ----------
   7839 KING             10    5000       1
   7782 CLARK             10    2450       2
   7934 update          10    1300       3
   7788 SCOTT             20    3000       1
   7902 FORD             20    3000       2
   7566 JONES             20    2000       3
   7876 ADAMS             20    1100       4
   7369 SMITH             20       800       5
   7698 BLAKE             30    2850       1
   7499 ALLEN             30    1600       2
   7844 TURNER          30    1500       3

   EMPNO ENAME       DEPTNO       SAL       NUM
---------- ---------- ---------- ---------- ----------
   7654 MARTIN          30    1250       4
   7521 WARD             30    1250       5
   7900 JAMES             30       950       6

14 rows selected.
如果每个部门只要取前三名的话，则可以稍稍修改查询：
SQL> select * from (
  2  select a.empno,
  3       a.ename,
  4       a.deptno,
  5       a.sal,
  6       row_number() over(partition by a.deptno order by a.sal desc) num
  7 from scott.emp a) where num <=3;

   EMPNO ENAME       DEPTNO       SAL       NUM
---------- ---------- ---------- ---------- ----------
   7839 KING             10    5000       1
   7782 CLARK             10    2450       2
   7934 update          10    1300       3
   7788 SCOTT             20    3000       1
   7902 FORD             20    3000       2
   7566 JONES             20    2000       3
   7698 BLAKE             30    2850       1
   7499 ALLEN             30    1600       2
   7844 TURNER          30    1500       3

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077

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

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time
|

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

| 0 | SELECT STATEMENT       |    | 14 | 826 |    4  (25)| 00:00:01
|

|*  1 |  VIEW                   |    | 14 | 826 |    4  (25)| 00:00:01
|

|*  2 | WINDOW SORT PUSHED RANK|    | 14 | 238 |    4  (25)| 00:00:01
|

| 3 | TABLE ACCESS FULL    | EMP  | 14 | 238 |    3 (0)| 00:00:01
|

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NUM"<=3)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "A"."DEPTNO" ORDER BY
            INTERNAL_FUNCTION("A"."SAL") DESC )<=3)

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      3  consistent gets
      0  physical reads
      0  redo size
      845  bytes sent via SQL*Net to client
      400  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      9  rows processed
很容易想到用嵌套查询，在外层限定序号num的值不超过3，注意看看执行计划id为2的行，使用的是WINDOW SORT PUSHED RANK
下面就来看看rank是如何实现上面的这个求前三甲的需求的：
SQL> select * from (
  2  select a.empno,
  3       a.ename,
  4       a.deptno,
  5       a.sal,
  6       rank() over(partition by a.deptno order by a.sal desc) num
  7 from scott.emp a) where num <=3;

   EMPNO ENAME       DEPTNO       SAL       NUM
---------- ---------- ---------- ---------- ----------
   7839 KING             10    5000       1
   7782 CLARK             10    2450       2
   7934 update          10    1300       3
   7788 SCOTT             20    3000       1
   7902 FORD             20    3000       1
   7566 JONES             20    2000       3
   7698 BLAKE             30    2850       1
   7499 ALLEN             30    1600       2
   7844 TURNER          30    1500       3

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3291446077

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

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time
|

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

| 0 | SELECT STATEMENT       |    | 14 | 826 |    4  (25)| 00:00:01
|

|*  1 |  VIEW                   |    | 14 | 826 |    4  (25)| 00:00:01
|

|*  2 | WINDOW SORT PUSHED RANK|    | 14 | 238 |    4  (25)| 00:00:01
|

| 3 | TABLE ACCESS FULL    | EMP  | 14 | 238 |    3 (0)| 00:00:01
|

--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NUM"<=3)
2 - filter(RANK() OVER ( PARTITION BY "A"."DEPTNO" ORDER BY
            INTERNAL_FUNCTION("A"."SAL") DESC )<=3)

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      3  consistent gets
      0  physical reads
      0  redo size
      842  bytes sent via SQL*Net to client
      400  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      9  rows processed
好像写法是一样的，但是结果稍稍有所不同，rank会把相同的sal当作并列处理。但看执行机会和统计信息则发现基本一样(这里猜测oracle内部实现row_number和rank的主要算法是一样的)
Dense_rank和rank稍有不同，不过是序号不会像rank一样跳跃。再看一个例子：
SQL> select a.deptno,
  2       a.job,
  3       sum(a.sal),
  4       dense_rank() over(partition by a.deptno order by sum(a.sal) desc) num
  5 from scott.emp a
  6 group by grouping sets((a.deptno),(a.job),(a.deptno, a.job),());

DEPTNO JOB    SUM(A.SAL)       NUM
---------- --------- ---------- ----------
      10                8750       1
      10 PRESIDENT    5000       2
      10 MANAGER       2450       3
      10 CLERK          1300       4
      20                9900       1
      20 ANALYST       6000       2
      20 MANAGER       2000       3
      20 CLERK          1900       4
      30                9400       1
      30 SALESMAN       5600       2
      30 MANAGER       2850       3

DEPTNO JOB    SUM(A.SAL)       NUM
---------- --------- ---------- ----------
      30 CLERK          950       4
                        28050       1
         MANAGER       7300       2
         ANALYST       6000       3
         SALESMAN       5600       4
         PRESIDENT    5000       5
         CLERK          4150       6

18 rows selected.
以上是分部门和工种来求工钱总和，并且使用了grouping sets根据需要获得分组的排名。
Percent_rank也很容易理解：
当前行占分组内行的百分比，比如要知道当前的员工的工钱水平占整个部门内的什么水平，可以这样来看：
SQL> select a.empno,
  2       a.ename,
  3       a.deptno,
  4       a.sal,
  5       percent_rank() over(partition by a.deptno order by a.sal desc) num
  6 from scott.emp a
  7  ;

   EMPNO ENAME       DEPTNO       SAL       NUM
---------- ---------- ---------- ---------- ----------
   7839 KING             10    5000       0
   7782 CLARK             10    2450       .5
   7934 update          10    1300       1
   7788 SCOTT             20    3000       0
   7902 FORD             20    3000       0
   7566 JONES             20    2000       .5
   7876 ADAMS             20    1100       .75
   7369 SMITH             20       800       1
   7698 BLAKE             30    2850       0
   7499 ALLEN             30    1600       .2
   7844 TURNER          30    1500       .4

   EMPNO ENAME       DEPTNO       SAL       NUM
---------- ---------- ---------- ---------- ----------
   7654 MARTIN          30    1250       .6
   7521 WARD             30    1250       .6
   7900 JAMES             30       950       1

14 rows selected.
显然KING和CLARK都是部门内50%的富人，但是他们相差却非常之大，贫富差距往往是数据看不出来的，前面还没有用avg来求平均值，求了则更是粉饰了真正的现实。想到最近10年或者20年（从记事开始算起）以来的变迁，我们的“涉会注意郭家”是相当优越的，对内喜欢讲人均，对外喜欢将总和，就是转移一下贫富差距的视线，现在的情况是大寡头占有了涉会绝大多数财富资源，很少数的人抢占了大多数人的幸福生活。不要觉得意外，其基本原理本来就是这样：
让一部分人先富起来，带动全民富裕。
这句看着很搞笑了。
下面继续来看看cume_dist,和percent_rank差不多，看一个例子就可以知道了：
SQL> select a.deptno,
  2       a.job,
  3       sum(a.sal),
  4       cume_dist() over(partition by a.deptno order by sum(a.sal) desc) num
  5 from scott.emp a
  6 group by rollup (a.deptno, a.job);

DEPTNO JOB    SUM(A.SAL)       NUM
---------- --------- ---------- ----------
      10                8750       .25
      10 PRESIDENT    5000       .5
      10 MANAGER       2450       .75
      10 CLERK          1300       1
      20                9900       .25
      20 ANALYST       6000       .5
      20 MANAGER       2000       .75
      20 CLERK          1900       1
      30                9400       .25
      30 SALESMAN       5600       .5
      30 MANAGER       2850       .75

DEPTNO JOB    SUM(A.SAL)       NUM
---------- --------- ---------- ----------
      30 CLERK          950       1
                        28050       1

13 rows selected.
而ntile是将行再分组，修改上述查询也很容易理解，ntile(2)将行分为2组，下面例子中4行的即为2，2，如果是3行，则2，1，其他雷同：
SQL> select a.deptno,
  2       a.job,
  3       sum(a.sal),
  4       ntile(2) over(partition by a.deptno order by sum(a.sal) desc) num
  5 from scott.emp a
  6 group by rollup (a.deptno, a.job);

DEPTNO JOB    SUM(A.SAL)       NUM
---------- --------- ---------- ----------
      10                8750       1
      10 PRESIDENT    5000       1
      10 MANAGER       2450       2
      10 CLERK          1300       2
      20                9900       1
      20 ANALYST       6000       1
      20 MANAGER       2000       2
      20 CLERK          1900       2
      30                9400       1
      30 SALESMAN       5600       1
      30 MANAGER       2850       2

DEPTNO JOB    SUM(A.SAL)       NUM
---------- --------- ---------- ----------
      30 CLERK          950       2
                        28050       1

13 rows selected.
有关评级函数，再说一个表结构设计时候的问题，现在有一个log表，记录某些操作的具体信息（操作事件，操作人，操作信息，开始时间，结束时间，操作对象id，操作状态等等），现在要对操作人考核，看看这个人总共操作了多少次，在操作中花了多少时间，每个状态值停了多少时间，从第一次开始操作，到最终完成操作又话了多少时间。好像没有什么是sql完成不了的，rank,row_number,或者lag,lead可能都可以用的上，不过个人觉得，像经常性查询的一些关键字段，应该在基表内有一定的冗余字段，关联或者计算在大数据量和高响应要求时给系统带来了负面影响，严格来讲分析函数应该尽可能多用于数据仓库或者分析报表库，而不是oltp的生产系统，但是往往情况不是这样。

yellowlee · 发表于 2010-5-29 16:29

2,行筛选相关的函数keep,first,last,first_value,last_value,lag,lead
先来看两个查询，一个实现的是部门内最早雇佣的员工中的工资最多与最小的值，一个是实现工资最高和最低的员工中入职时间最早或最晚的值：
查询1：
SQL> select a.deptno,a.hiredate,a.sal,
  2       min(a.sal) keep(dense_rank first order by hiredate desc) over(partition by a.deptno) first,
  3       max(a.sal) keep(dense_rank last order by hiredate desc) over(partition by a.deptno) last
  4 from scott.emp a;

DEPTNO HIREDATE       SAL    FIRST    LAST
---------- --------- ---------- ---------- ----------
      10 09-JUN-81    2450    1300    2450
      10 17-NOV-81    5000    1300    2450
      10 23-JAN-82    1300    1300    2450
      20 02-APR-81    2000    1100       800
      20 03-DEC-81    3000    1100       800
      20 23-MAY-87    1100    1100       800
      20 17-DEC-80       800    1100       800
      20 19-APR-87    3000    1100       800
      30 22-FEB-81    1250       950    1600
      30 08-SEP-81    1500       950    1600
      30 20-FEB-81    1600       950    1600

DEPTNO HIREDATE       SAL    FIRST    LAST
---------- --------- ---------- ---------- ----------
      30 03-DEC-81       950       950    1600
      30 01-MAY-81    2850       950    1600
      30 28-SEP-81    1250       950    1600

14 rows selected.

查询2：
SQL> select a.deptno,a.sal,a.hiredate,
  2       min(a.hiredate) keep(dense_rank first order by a.sal desc) over(partition by a.deptno) first,
  3       max(a.hiredate) keep(dense_rank last order by a.sal desc) over(partition by a.deptno) last
  4 from scott.emp a;

DEPTNO       SAL HIREDATE  FIRST    LAST
---------- ---------- --------- --------- ---------
      10    2450 09-JUN-81 17-NOV-81 23-JAN-82
      10    5000 17-NOV-81 17-NOV-81 23-JAN-82
      10    1300 23-JAN-82 17-NOV-81 23-JAN-82
      20    2000 02-APR-81 03-DEC-81 17-DEC-80
      20    3000 03-DEC-81 03-DEC-81 17-DEC-80
      20    1100 23-MAY-87 03-DEC-81 17-DEC-80
      20       800 17-DEC-80 03-DEC-81 17-DEC-80
      20    3000 19-APR-87 03-DEC-81 17-DEC-80
      30    1250 22-FEB-81 01-MAY-81 03-DEC-81
      30    1500 08-SEP-81 01-MAY-81 03-DEC-81
      30    1600 20-FEB-81 01-MAY-81 03-DEC-81

DEPTNO       SAL HIREDATE  FIRST    LAST
---------- ---------- --------- --------- ---------
      30       950 03-DEC-81 01-MAY-81 03-DEC-81
      30    2850 01-MAY-81 01-MAY-81 03-DEC-81
      30    1250 28-SEP-81 01-MAY-81 03-DEC-81

14 rows selected.

上面的例子中时间没有完全相同的，那么简单修改一下，则可以更好的理解keep中的first和last与min和max的意义：
正对上面的查询1来修改得到
查询3：
SQL> select a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
  2       min(a.sal) keep(dense_rank first order by to_char(hiredate,'yyyy') desc) over(partition by a.deptno) first,
  3       max(a.sal) keep(dense_rank last order by to_char(hiredate,'yyyy') desc) over(partition by a.deptno) last
  4 from scott.emp a;

DEPTNO HIRE       SAL    FIRST    LAST
---------- ---- ---------- ---------- ----------
      10 1981    2450    1300    5000
      10 1981    5000    1300    5000
      10 1982    1300    1300    5000
      20 1981    2000    1100       800
      20 1981    3000    1100       800
      20 1987    1100    1100       800
      20 1980       800    1100       800
      20 1987    3000    1100       800
      30 1981    1250       950    2850
      30 1981    1500       950    2850
      30 1981    1600       950    2850

DEPTNO HIRE       SAL    FIRST    LAST
---------- ---- ---------- ---------- ----------
      30 1981       950       950    2850
      30 1981    2850       950    2850
      30 1981    1250       950    2850

14 rows selected.
first和last很容易理解。Dense_rank的特点上面的keep中的dense_rank first或者dense_rank last都可能会取到多行。
在hiredate修改为to_char(hiredate,'yyyy')以后，有了在同一年份入职的员工，那么first其实会把第一组年份的员工行都取了，外层使用min(a.sal)或者max(a.sal)就是再对组中的员工求最大最小工钱值。
接下来再看看first_value和last_value,有四个查询例子：
这个查询按deptno开窗找第一个和最后一个sal：
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
  2       first_value(a.sal)  over(partition by a.deptno ) first,
  3       last_value(a.sal) over(partition by a.deptno ) last
  4 from scott.emp a;

   EMPNO    DEPTNO HIRE       SAL    FIRST    LAST
---------- ---------- ---- ---------- ---------- ----------
   7782       10 1981    2450    2450    1300
   7839       10 1981    5000    2450    1300
   7934       10 1982    1300    2450    1300
   7566       20 1981    2000    2000    3000
   7902       20 1981    3000    2000    3000
   7876       20 1987    1100    2000    3000
   7369       20 1980       800    2000    3000
   7788       20 1987    3000    2000    3000
   7521       30 1981    1250    1250    1250
   7844       30 1981    1500    1250    1250
   7499       30 1981    1600    1250    1250

   EMPNO    DEPTNO HIRE       SAL    FIRST    LAST
---------- ---------- ---- ---------- ---------- ----------
   7900       30 1981       950    1250    1250
   7698       30 1981    2850    1250    1250
   7654       30 1981    1250    1250    1250

14 rows selected.
下面的查询在上面的基础上增加了order by deptno语句：
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
  2       first_value(a.sal)  over(partition by a.deptno order by deptno) first,
  3       last_value(a.sal) over(partition by a.deptno order by deptno) last
  4 from scott.emp a;

   EMPNO    DEPTNO HIRE       SAL    FIRST    LAST
---------- ---------- ---- ---------- ---------- ----------
   7934       10 1982    1300    1300    5000
   7782       10 1981    2450    1300    5000
   7839       10 1981    5000    1300    5000
   7369       20 1980       800       800    3000
   7876       20 1987    1100       800    3000
   7566       20 1981    2000       800    3000
   7788       20 1987    3000       800    3000
   7902       20 1981    3000       800    3000
   7900       30 1981       950       950    2850
   7654       30 1981    1250       950    2850
   7521       30 1981    1250       950    2850

   EMPNO    DEPTNO HIRE       SAL    FIRST    LAST
---------- ---------- ---- ---------- ---------- ----------
   7844       30 1981    1500       950    2850
   7499       30 1981    1600       950    2850
   7698       30 1981    2850       950    2850

14 rows selected.
下面的查询是按照sal升序排序后的，这样每个部门下面按照sal排序了，每增加一条数据，都会取当前的first和last，因此可以看到每行的last都在变化。
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
  2       first_value(a.sal)  over(partition by a.deptno order by sal) first,
  3       last_value(a.sal) over(partition by a.deptno order by sal) last
  4 from scott.emp a;

   EMPNO    DEPTNO HIRE       SAL    FIRST    LAST
---------- ---------- ---- ---------- ---------- ----------
   7934       10 1982    1300    1300    1300
   7782       10 1981    2450    1300    2450
   7839       10 1981    5000    1300    5000
   7369       20 1980       800       800       800
   7876       20 1987    1100       800    1100
   7566       20 1981    2000       800    2000
   7788       20 1987    3000       800    3000
   7902       20 1981    3000       800    3000
   7900       30 1981       950       950       950
   7654       30 1981    1250       950    1250
   7521       30 1981    1250       950    1250

   EMPNO    DEPTNO HIRE       SAL    FIRST    LAST
---------- ---------- ---- ---------- ---------- ----------
   7844       30 1981    1500       950    1500
   7499       30 1981    1600       950    1600
   7698       30 1981    2850       950    2850

14 rows selected.
再修改一下，order by rowid，效果差不多：
SQL> select a.empno,a.deptno,to_char(hiredate,'yyyy') hiredate,a.sal,
  2       first_value(a.sal)  over(partition by a.deptno order by rowid) first,
  3       last_value(a.sal) over(partition by a.deptno order by rowid) last
  4 from scott.emp a;

   EMPNO    DEPTNO HIRE       SAL    FIRST    LAST
---------- ---------- ---- ---------- ---------- ----------
   7782       10 1981    2450    2450    2450
   7839       10 1981    5000    2450    5000
   7934       10 1982    1300    2450    1300
   7369       20 1980       800       800       800
   7566       20 1981    2000       800    2000
   7788       20 1987    3000       800    3000
   7876       20 1987    1100       800    1100
   7902       20 1981    3000       800    3000
   7499       30 1981    1600    1600    1600
   7521       30 1981    1250    1600    1250
   7654       30 1981    1250    1600    1250

   EMPNO    DEPTNO HIRE       SAL    FIRST    LAST
---------- ---------- ---- ---------- ---------- ----------
   7698       30 1981    2850    1600    2850
   7844       30 1981    1500    1600    1500
   7900       30 1981       950    1600       950

14 rows selected.
再来看lag和lead，分别用两个sql来求部门内按照hiredate排序后的每个苦工上一个或者下一个的工钱值：
SQL> select a.empno,
  2       a.deptno,
  3       a.hiredate,
  4       a.sal,
  5       lag(sal, 1, 0) over(partition by a.deptno order by hiredate asc) pre_sal
  6 from scott.emp a;

   EMPNO    DEPTNO HIREDATE       SAL PRE_SAL
---------- ---------- --------- ---------- ----------
   7782       10 09-JUN-81    2450       0
   7839       10 17-NOV-81    5000    2450
   7934       10 23-JAN-82    1300    5000
   7369       20 17-DEC-80       800       0
   7566       20 02-APR-81    2000       800
   7902       20 03-DEC-81    3000    2000
   7788       20 19-APR-87    3000    3000
   7876       20 23-MAY-87    1100    3000
   7499       30 20-FEB-81    1600       0
   7521       30 22-FEB-81    1250    1600
   7698       30 01-MAY-81    2850    1250

   EMPNO    DEPTNO HIREDATE       SAL PRE_SAL
---------- ---------- --------- ---------- ----------
   7844       30 08-SEP-81    1500    2850
   7654       30 28-SEP-81    1250    1500
   7900       30 03-DEC-81       950    1250

14 rows selected.
SQL> select a.empno,
  2       a.deptno,
  3       a.hiredate,
  4       a.sal,
  5       lead(sal, 1, 0) over(partition by a.deptno order by hiredate asc) next_sal
  6 from scott.emp a;

   EMPNO    DEPTNO HIREDATE       SAL NEXT_SAL
---------- ---------- --------- ---------- ----------
   7782       10 09-JUN-81    2450    5000
   7839       10 17-NOV-81    5000    1300
   7934       10 23-JAN-82    1300       0
   7369       20 17-DEC-80       800    2000
   7566       20 02-APR-81    2000    3000
   7902       20 03-DEC-81    3000    3000
   7788       20 19-APR-87    3000    1100
   7876       20 23-MAY-87    1100       0
   7499       30 20-FEB-81    1600    1250
   7521       30 22-FEB-81    1250    2850
   7698       30 01-MAY-81    2850    1500

   EMPNO    DEPTNO HIREDATE       SAL NEXT_SAL
---------- ---------- --------- ---------- ----------
   7844       30 08-SEP-81    1500    1250
   7654       30 28-SEP-81    1250       950
   7900       30 03-DEC-81       950       0

14 rows selected.