Oracle lag () and lead () Functions

Oracle lag () and lead () Functions

I. Introduction

The lag and lead functions are two offset-related analysis functions. These two functions can be used to retrieve the data of the first N rows of the same field in a query (lag) and the next N rows of data (lead) as independent columns, so that more convenient data filtering. This operation can replace table self-join, and LAG and LEAD have higher efficiency.

Over () indicates that the data of the lag () and lead () operations is within the range of over (). You can use the partition by statement (used for grouping) order by statement (used for sorting ). Partition by a order by B indicates grouping by field a, sorting by field B, and querying data.

For example, for the fields to be searched by lead (field, num, defaultvalue) field, and for the data of the num row to be searched by num, defaultvalue does not have the default value that meets the condition.

Ii. Example

1. Table Structure and initialization data are as follows:

-- Table structure
Create table tb_test (
Id varchar2 (64) not null,
Cphm varchar2 (10) not null,
Create_date date not null,
Primary key (id)
)
-- Initialize data
Insert into tb_test values ('20170101', 'ab7477', to_date ('2017-11-30 10:18:12 ', 'yyyy-MM-DD HH24: mi: ss '));
Insert into tb_test values ('20170101', 'ab7477', to_date ('2017-11-30 10:22:12 ', 'yyyy-MM-DD HH24: mi: ss '));
Insert into tb_test values ('20170101', 'ab7477', to_date ('2017-11-30 10:28:12 ', 'yyyy-MM-DD HH24: mi: ss '));
Insert into tb_test values ('20170101', 'ab7477', to_date ('2017-11-30 10:29:12 ', 'yyyy-MM-DD HH24: mi: ss '));
Insert into tb_test values ('20170101', 'ab7477', to_date ('2017-11-30 10:39:13 ', 'yyyy-MM-DD HH24: mi: ss '));
Insert into tb_test values ('20170101', 'ab7477', to_date ('2017-11-30 10:45:12 ', 'yyyy-MM-DD HH24: mi: ss '));
Insert into tb_test values ('20170101', 'ab7477', to_date ('2017-11-30 10:56:12 ', 'yyyy-MM-DD HH24: mi: ss '));
Insert into tb_test values ('20170101', 'ab7477', to_date ('2017-11-30 10:57:12 ', 'yyyy-MM-DD HH24: mi: ss '));
-----------------------
Insert into tb_test values ('20170101', 'ab1_8', to_date ('2017-11-30 11:00:12 ', 'yyyy-MM-DD HH24: mi: ss '));
Insert into tb_test values ('20170101', 'ab1_8', to_date ('2017-11-30 11:10:13 ', 'yyyy-MM-DD HH24: mi: ss '));
Insert into tb_test values ('20170101', 'ab1_8', to_date ('2017-11-30 11:15:12 ', 'yyyy-MM-DD HH24: mi: ss '));
Insert into tb_test values ('20170101', 'ab1_8', to_date ('2017-11-30 11:26:12 ', 'yyyy-MM-DD HH24: mi: ss '));
Insert into tb_test values ('20170101', 'ab1_8', to_date ('2017-11-30 11:30:12 ', 'yyyy-MM-DD HH24: mi: ss '));

The table initialization data is:

　　

2. Example

A. Obtain the id of the current record and the id of the next record.