ORACLE lag () and lead () functions, oraclelag

ORACLE lag () and lead () functions, oraclelag

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:

1 -- table structure 2 create table tb_test (3 id varchar2 (64) not null, 4 cphm varchar2 (10) not null, 5 create_date date not null, 6 primary key (id) 7) 8 -- initialize data 9 insert into tb_test values ('20140901', 'ab7477', to_date ('2017-11-30 10:18:12 ', 'yyyy-MM-DD HH24: mi: ss '); 10 insert into tb_test values ('201312', 'ab7477', to_date ('2017-11-30 10:22:12', 'yyyy-MM-DD HH24: mi: ss '); 11 insert into tb_test values ('20140901', 'ab7477', to_date ('2017-11-30 10:28:12', 'yyyy-MM-DD HH24: mi: ss '); 12 insert into tb_test values ('20140901', 'ab7477', to_date ('2017-11-30 10:29:12', 'yyyy-MM-DD HH24: mi: ss '); 13 insert into tb_test values ('20140901', 'ab7477', to_date ('2017-11-30 10:39:13', 'yyyy-MM-DD HH24: mi: ss '); 14 insert into tb_test values ('201312', 'ab7477', to_date ('2017-11-30 10:45:12', 'yyyy-MM-DD HH24: mi: ss '); 15 insert into tb_test values ('201312', 'ab7477', to_date ('2017-11-30 10:56:12', 'yyyy-MM-DD HH24: mi: ss '); 16 insert into tb_test values ('201312', 'ab7477', to_date ('2017-11-30 10:57:12', 'yyyy-MM-DD HH24: mi: ss '); 17 -- ------------------- 18 insert into tb_test values ('000000', 'ab1_8', to_date ('2017-11-30 11:00:12', 'yyyy-MM-DD HH24: mi: ss'); 19 insert into tb_test values ('000000', 'ab1_8', to_date ('2017-11-30 11:10:13 ', 'yyyy-MM-DD HH24: mi: ss '); 20 insert into tb_test values ('20140901', 'ab1_8', to_date ('2017-11-30 11:15:12', 'yyyy-MM-DD HH24: mi: ss '); 21 insert into tb_test values ('20140901', 'ab1_8', to_date ('2017-11-30 11:26:12', 'yyyy-MM-DD HH24: mi: ss '); 22 insert into tb_test values ('20140901', '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.

select t.id id ,       lead(t.id, 1, null) over (order by t.id)  next_record_id, t.cphmfrom tb_test t         order by t.id asc

The running result is as follows:

　　

B. Obtain the id of the current record and the id of the previous record.

select t.id id ,       lag(t.id, 1, null) over (order by t.id)  next_record_id, t.cphmfrom tb_test t         order by t.id asc

The running result is as follows:

　　

C. Obtain the id of the current record and the id of the next record (using partition by) with the same number)

select t.id id,        lead(t.id, 1, null) over(partition by cphm order by t.id) next_same_cphm_id, t.cphmfrom tb_test t     order by t.id asc   

The running result is as follows:

　　

D. query the total number of cphm instances. If the interval between create_date and create_date of the next record does not exceed 10 minutes, ignore.

 1 select cphm, count(1) total from 2 ( 3 select t.id,  4   t.create_date t1, 5   lead(t.create_date,1, null) over( partition by  cphm order by create_date asc ) t2,   6   ( lead(t.create_date,1, null) over(  partition by  cphm order by create_date asc )  - t.create_date ) * 86400 as itvtime, 7   t.cphm 8 from tb_test t  9   order by t.cphm, t.create_date asc10 ) tt11 where tt.itvtime >= 600 or  tt.itvtime  is null12 group by tt.cphm

The result is as follows: