Introduction to new features of Oracle 11g R2 analysis Functions (III.) analysis function lag and lead enhancements

In 11GR2, Oracle analysis functions are further enhanced.

This article describes the analysis function lag and the enhancement of the lead.

The 11GR2 lag and lead functions are enhanced to add ignore Nulls functionality.

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 (), type VARCHAR2 (20));

Table has been created.

sql> INSERT INTO T-select RowNum, object_name, object_type from Dba_objects;

71968 lines have been created.

Sql> commit;

Submit completed.

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

sys_lob0000000532c00002$$ LOB

513 kotad$ TABLE

514 sys_c00646 INDEX

515 sys_lob0000000536c00002$$ LOB

516 kotmd$ TABLE

517 sys_c00647 INDEX

10 rows have been selected.

Lag and leads can get records for the front or back n rows of the current row:

Sql> Select ID,

2 Name,

3 Type,

4 Lag (name) over (the 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$$

sys_lob0000000532c00002$$ LOB sys_c00645 kotad$

513 kotad$ TABLE sys_lob0000000532c00002$$ sys_c00646

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45458.htm