【分析函数】Oracle分析函数之LAG和LEAD

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。

SCOTT@yangdb> select ename,job,sal ,lag(sal,1,500) over(order by sal) last_sal from emp; ENAME      JOB              SAL   LAST_SAL ---------- --------- ---------- ---------- SMITH      CLERK            800        500 JAMES      CLERK            950        800 ADAMS      CLERK           1100        950 WARD       SALESMAN        1250       1100 MARTIN     SALESMAN        1250       1250 MILLER      CLERK           1300       1250 TURNER     SALESMAN        1500       1300 ALLEN      SALESMAN        1600       1500 CLARK      MANAGER         2450       1600 BLAKE      MANAGER         2850       2450 JONES      MANAGER         2975       2850 SCOTT      ANALYST         3000       2975 FORD       ANALYST         3000       3000 KING       PRESIDENT       5000       3000 14 rows selected.

指定offset的值为2时 SCOTT@yangdb> select ename,job,sal ,lag(sal,2) over(order by sal) last_sal from emp; ENAME      JOB              SAL   LAST_SAL ---------- --------- ---------- ---------- SMITH      CLERK            800 JAMES      CLERK            950 ADAMS      CLERK           1100        800 WARD       SALESMAN        1250        950 MARTIN     SALESMAN        1250       1100 MILLER     CLERK           1300       1250 TURNER     SALESMAN        1500       1250 ALLEN      SALESMAN        1600       1300 CLARK      MANAGER         2450       1500 BLAKE      MANAGER         2850       1600 JONES      MANAGER         2975       2450 SCOTT      ANALYST         3000       2850 FORD       ANALYST         3000       2975 KING       PRESIDENT       5000       3000 14 rows selected.

offset的值为3 SCOTT@yangdb> select ename,job,sal ,lag(sal,3) over(order by sal) last_sal from emp; ENAME      JOB              SAL   LAST_SAL ---------- --------- ---------- ---------- SMITH      CLERK            800 JAMES      CLERK            950 ADAMS      CLERK           1100 WARD       SALESMAN        1250        800 MARTIN     SALESMAN        1250        950 MILLER     CLERK           1300       1100 TURNER     SALESMAN        1500       1250 ALLEN      SALESMAN        1600       1250 CLARK      MANAGER         2450       1300 BLAKE      MANAGER         2850       1500 JONES      MANAGER         2975       1600 SCOTT      ANALYST         3000       2450 FORD       ANALYST         3000       2850 KING       PRESIDENT       5000       2975 14 rows selected.

使用lead分析函数 SCOTT@yangdb> select ename,job,sal ,lead(sal) over(order by sal) last_sal from emp; ENAME      JOB              SAL   LAST_SAL ---------- --------- ---------- ---------- SMITH      CLERK            800        950 JAMES      CLERK            950       1100 ADAMS      CLERK           1100       1250 WARD       SALESMAN        1250       1250 MARTIN     SALESMAN        1250       1300 MILLER     CLERK           1300       1500 TURNER     SALESMAN        1500       1600 ALLEN      SALESMAN        1600       2450 CLARK      MANAGER         2450       2850 BLAKE      MANAGER         2850       2975 JONES      MANAGER         2975       3000 SCOTT      ANALYST         3000       3000 FORD       ANALYST         3000       5000 KING       PRESIDENT       5000 14 rows selected. SCOTT@yangdb> select ename,job,sal ,lead(sal,1) over(order by sal) last_sal from emp; ENAME      JOB              SAL   LAST_SAL ---------- --------- ---------- ---------- SMITH      CLERK            800        950 JAMES      CLERK            950       1100 ADAMS      CLERK           1100       1250 WARD       SALESMAN        1250       1250 MARTIN     SALESMAN        1250       1300 MILLER     CLERK           1300       1500 TURNER     SALESMAN        1500       1600 ALLEN      SALESMAN        1600       2450 CLARK      MANAGER         2450       2850 BLAKE      MANAGER         2850       2975 JONES      MANAGER         2975       3000 SCOTT      ANALYST         3000       3000 FORD       ANALYST         3000       5000 KING       PRESIDENT       5000 14 rows selected.

Lead和Lag函数也可以使用分组，以下是使用 job 分组的例子：

SCOTT@yangdb> select ename,job,sal ,lead(sal,1) over(partition by job order by sal) last_sal from emp; ENAME      JOB              SAL   LAST_SAL ---------- --------- ---------- ---------- FORD       ANALYST         3000       3000 SCOTT      ANALYST         3000 SMITH      CLERK            800        950 JAMES      CLERK            950       1100 ADAMS      CLERK           1100       1300 MILLER     CLERK           1300 CLARK      MANAGER         2450       2850 BLAKE      MANAGER         2850       2975 JONES      MANAGER         2975 KING       PRESIDENT       5000 MARTIN     SALESMAN        1250       1250 WARD       SALESMAN        1250       1500 TURNER     SALESMAN        1500       1600 ALLEN      SALESMAN        1600 14 rows selected. SCOTT@yangdb> select ename,job,sal ,lag(sal,1) over(partition by job order by sal) last_sal from emp; ENAME      JOB              SAL   LAST_SAL ---------- --------- ---------- ---------- FORD       ANALYST         3000 SCOTT      ANALYST         3000       3000 SMITH      CLERK            800 JAMES      CLERK            950        800 ADAMS      CLERK           1100        950 MILLER     CLERK           1300       1100 CLARK      MANAGER         2450 BLAKE      MANAGER         2850       2450 JONES      MANAGER         2975       2850 KING       PRESIDENT       5000 MARTIN     SALESMAN        1250 WARD       SALESMAN        1250       1250 TURNER     SALESMAN        1500       1250 ALLEN      SALESMAN        1600       1500 14 rows selected. SCOTT@yangdb>

使用分析函数的时候注意空值 或者null 给数据带来的影响，数据是否允许为空或者null 计算的时候会导致一定的差错 比如 800-null 肯定为null！这个结果是否是应用想要的结果？