Oracle11gr2分析函式新特性（三）

在11gr2中，Oracle分析函式的功能進一步增強。

這篇介紹分析函式LAG和LEAD的增強。

Oracle11gr2分析函式新特性（一）：http://yangtingkun.itpub.net/post/468/491115

Oracle11gr2分析函式新特性（二）：http://yangtingkun.itpub.net/post/468/491137

 

 

11gr2對LAG和LEAD函式進行了增強，新增了IGNORE NULLS的功能。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.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行。