Introduction to new features of Oracle 11g R2 analysis Functions (i) Analysis function nth_value

In 11GR2, Oracle analysis functions are further enhanced.

This article introduces the new analysis function Nth_value.

Previous versions of the analytic functions, which provided first_value and last_value functionality, and in 11GR2, Oracle added a nth_value feature that includes First_value and Last_value functions, You can also take any number of positive or reciprocal records.

A simple look at the usage of this analytic function:

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, name)

2 as Select RowNum, Tablespace_name

3 from Dba_tablespaces;

Table has been created.

Sql> select * from T;

ID NAME

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

1 SYSTEM

2 Sysaux

3 UNDOTBS1

4 TEMP

5 USERS

6 YANGTK

6 rows have been selected.

Sql> Select ID,

2 Name,

3 First_value (name) over (the Order by ID) F_name,

4 Last_value (name) over (order by ID) l_name

5 from T;

ID NAME f_name L_name

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

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

1 System System System

2 Sysaux SYSTEM Sysaux

3 UNDOTBS1 SYSTEM UNDOTBS1

4 Temp SYSTEM Temp

5 Users SYSTEM users

6 YANGTK SYSTEM YANGTK

6 rows have been selected.

Sql> Select ID,

2 Name,

3 Nth_value (name, 2) over (order by ID) F_2_name,

4 Nth_value (name, 2) from the last over (order by ID) l_2_name

5 from T;

ID NAME F_2_name L_2_name

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

1 SYSTEM

2 Sysaux Sysaux SYSTEM

3 UNDOTBS1 Sysaux Sysaux

4 TEMP Sysaux UNDOTBS1

5 USERS Sysaux TEMP

6 YANGTK Sysaux USERS

6 rows have been selected.

The second parameter in Nth_value refers to the number of records in which the function is ranked, and from the first or from the last indicates whether the rank is calculated from the first, or from the last.