在11gr2中，Oracle分析函数的功能进一步增强。

这篇介绍分析函数LAG和LEAD的增强。


11gr2对LAG和LEAD函数进行了增强，添加了IGNORE NULLS的功能。

SQL> select * from v$version;


BANNER


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


Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production


PL/SQL Release 11.2.0.1.0 - Production


CORE    11.2.0.1.0      Production


TNS for Linux: Version 11.2.0.1.0 - Production


NLSRTL Version 11.2.0.1.0 - Production


SQL> create table t (id number, name varchar2(30), type varchar2(20));


表已创建。


SQL> insert into t select rownum, object_name, object_type from dba_objects;


已创建71968行。


SQL> commit;


提交完成。


SQL> select id, name, type


 2  from t


 3  where rownum < 11;


       ID NAME                           TYPE


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


      508 SYS_C00644                     INDEX


      509 SYS_LOB0000000528C00002$$      LOB


      510 KOTTB$                         TABLE


      511 SYS_C00645                     INDEX


      512 SYS_LOB0000000532C00002$$      LOB


      513 KOTAD$                         TABLE


      514 SYS_C00646                     INDEX


      515 SYS_LOB0000000536C00002$$      LOB


      516 KOTMD$                         TABLE


      517 SYS_C00647                     INDEX


已选择10行。


LAG和LEAD可以获取当前行前或后N行的记录：

SQL> select id,


 2  name,


 3  type,


 4  lag(name) over(order by id) n_name,


 5  lead(name) over(order by id) l_name


 6  from t


 7  where rownum < 11;


ID NAME                      TYPE   N_NAME                    L_NAME


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


508 SYS_C00644                INDEX                            SYS_LOB0000000528C00002$$


509 SYS_LOB0000000528C00002$$ LOB    SYS_C00644                KOTTB$


510 KOTTB$                    TABLE  SYS_LOB0000000528C00002$$ SYS_C00645


511 SYS_C00645                INDEX  KOTTB$                    SYS_LOB0000000532C00002$$


512 SYS_LOB0000000532C00002$$ LOB    SYS_C00645                KOTAD$


513 KOTAD$                    TABLE  SYS_LOB0000000532C00002$$ SYS_C00646


514 SYS_C00646                INDEX  KOTAD$                    SYS_LOB0000000536C00002$$


515 SYS_LOB0000000536C00002$$ LOB    SYS_C00646                KOTMD$


516 KOTMD$                    TABLE  SYS_LOB0000000536C00002$$ SYS_C00647


517 SYS_C00647                INDEX  KOTMD$


已选择10行。


如果LAG或LEAD的表达式结果为空，则返回结果也是空：

SQL> select id,


 2  name,


 3  type,


 4  lag(decode(type, 'LOB', null, name)) over(order by id) n_name,


 5  lead(decode(type, 'LOB', null, name)) over(order by id) l_name


 6  from t


 7  where rownum < 11;


       ID NAME                      TYPE   N_NAME                    L_NAME


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


      508 SYS_C00644                INDEX


      509 SYS_LOB0000000528C00002$$ LOB    SYS_C00644                KOTTB$


      510 KOTTB$                    TABLE                            SYS_C00645


      511 SYS_C00645                INDEX  KOTTB$


      512 SYS_LOB0000000532C00002$$ LOB    SYS_C00645                KOTAD$


      513 KOTAD$                    TABLE                            SYS_C00646


      514 SYS_C00646                INDEX  KOTAD$


      515 SYS_LOB0000000536C00002$$ LOB    SYS_C00646                KOTMD$


      516 KOTMD$                    TABLE                            SYS_C00647


      517 SYS_C00647                INDEX  KOTMD$


已选择10行。


而新增的IGNORE NULLS功能，可以忽略NULL结果，去寻找另一个满足条件的结果：

SQL> select id,


 2  name,


 3  type,


 4  lag(decode(type, 'LOB', null, name)) ignore nulls over(order by id) n_name,


 5  lead(decode(type, 'LOB', null, name)) ignore nulls over(order by id) l_name


 6  from t


 7  where rownum < 11;


       ID NAME                      TYPE   N_NAME                    L_NAME


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


      508 SYS_C00644                INDEX                            KOTTB$


      509 SYS_LOB0000000528C00002$$ LOB    SYS_C00644                KOTTB$


      510 KOTTB$                    TABLE  SYS_C00644                SYS_C00645


      511 SYS_C00645                INDEX  KOTTB$                    KOTAD$


      512 SYS_LOB0000000532C00002$$ LOB    SYS_C00645                KOTAD$


      513 KOTAD$                    TABLE  SYS_C00645                SYS_C00646


      514 SYS_C00646                INDEX  KOTAD$                    KOTMD$


      515 SYS_LOB0000000536C00002$$ LOB    SYS_C00646                KOTMD$


      516 KOTMD$                    TABLE  SYS_C00646                SYS_C00647


      517 SYS_C00647                INDEX  KOTMD$


已选择10行。



oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html