转载

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

     Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。     
     lag的语法如下：     
          
     lead的语法如下：     
          
     lead 和lag 的语法类似以下以lag为例进行讲解！     
     lag(exp_str,offset,defval) over()     
     exp_str 是要做对比的字段     
     offset 是exp_str字段的偏移量 比如说 offset 为2 则 拿exp_str的第一行和第三行对比，第二行和第四行，依次类推，offset的默认值为1！     
     defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。     
     以下是lag和lead的例子     
SCOTT@yangdb> set pagesize 10000
 SCOTT@yangdb> select ename,job,sal ,lag(sal) over(order by sal) last_sal from emp;
 ENAME      JOB              SAL   LAST_SAL
 ---------- --------- ---------- ----------
 SMITH      CLERK            800       --此时没有设置default 值 则为空值
 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.
 设置了default 值之后 第一行对应的值 为500    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.    
     SCOTT@yangdb> select ename,job,sal ,lead(sal,2) over(order by sal) last_sal from emp;
 ENAME      JOB              SAL   LAST_SAL
 ---------- --------- ---------- ----------
 SMITH      CLERK            800       1100
 JAMES      CLERK            950       1250
 ADAMS      CLERK           1100       1250
 WARD       SALESMAN        1250       1300
 MARTIN     SALESMAN        1250       1500
 MILLER     CLERK           1300       1600
 TURNER     SALESMAN        1500       2450
 ALLEN      SALESMAN        1600       2850
 CLARK      MANAGER         2450       2975
 BLAKE      MANAGER         2850       3000
 JONES      MANAGER         2975       3000
 SCOTT      ANALYST         3000       5000
 FORD       ANALYST         3000
 KING       PRESIDENT       5000
 SCOTT@yangdb> select ename,job,sal ,lead(sal,3) over(order by sal) last_sal from emp;
 ENAME      JOB              SAL   LAST_SAL
 ---------- --------- ---------- ----------
 SMITH      CLERK            800       1250
 JAMES      CLERK            950       1250
 ADAMS      CLERK           1100       1300
 WARD       SALESMAN        1250       1500
 MARTIN     SALESMAN        1250       1600
 MILLER     CLERK           1300       2450
 TURNER     SALESMAN        1500       2850
 ALLEN      SALESMAN        1600       2975
 CLARK      MANAGER         2450       3000
 BLAKE      MANAGER         2850       3000
 JONES      MANAGER         2975       5000
 SCOTT      ANALYST         3000
 FORD       ANALYST         3000
 KING       PRESIDENT       5000
 14 rows selected.     
     lead 的offset N 是以记录的第N行和第一做对比注意末尾的 null 值！     
     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！这个结果是否是应用想要的结果？    

   细心很重要！！尤其是在计算和钱有关的情况下！！   

lag和lead之IGNORE NULLS

Oracle 11g增强LEAD和LAG函数的语法，加入了{RESPECT | IGNORE} NULLS，当前行为NULL，想找出该列上一个不为NULL的值，怎么办呢，用IGNORE NULLS吧。

            drop table ta;
 create table ta as select level id,dummy||level x from dual connect by level<=10;
 
 
 UPDATE TA SET X='' WHERE ID BETWEEN 3 AND 7;
 
 
 SELECT T.ID,
        T.X,
        NVL(T.X, LAG(T.X IGNORE NULLS) OVER(ORDER BY T.ID)) A,
        NVL(T.X, LAG(T.X RESPECT NULLS) OVER(ORDER BY T.ID)) B,
        NVL(T.X, LAG(T.X) OVER(ORDER BY T.ID)) C
   FROM TA T
  ORDER BY T.ID;      
    

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

About Me

...............................................................................................................................

● 本文整理自网络

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-04-28 09:00 ~ 2017-04-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。