首页 / 数据库 / MySQL / Oracle lag（）与lead（） 函数

一、简介lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据（lag）和后N行的数据（lead）作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。over（）表示 lag（）与lead（）操作的数据都在over（）的范围内，他里面可以使用partition by 语句（用于分组） order by 语句（用于排序）。partition by a order by b表示以a字段进行分组，再 以b字段进行排序，对数据进行查询。例如：lead（field, num, defaultvalue） field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。二、示例1、表机构与初始化数据如下-- 表结构
create table tb_test（
  id varchar2（64） not null,
  cphm varchar2（10） not null,
  create_date date not null,
  primary key （id）
）
-- 初始化数据
insert into tb_test values （"1000001", "AB7477", to_date（"2015-11-30 10:18:12","YYYY-MM-DD HH24:mi:ss"））;
insert into tb_test values （"1000002", "AB7477", to_date（"2015-11-30 10:22:12","YYYY-MM-DD HH24:mi:ss"））;
insert into tb_test values （"1000003", "AB7477", to_date（"2015-11-30 10:28:12","YYYY-MM-DD HH24:mi:ss"））;
insert into tb_test values （"1000004", "AB7477", to_date（"2015-11-30 10:29:12","YYYY-MM-DD HH24:mi:ss"））;
insert into tb_test values （"1000005", "AB7477", to_date（"2015-11-30 10:39:13","YYYY-MM-DD HH24:mi:ss"））;
insert into tb_test values （"1000006", "AB7477", to_date（"2015-11-30 10:45:12","YYYY-MM-DD HH24:mi:ss"））;
insert into tb_test values （"1000007", "AB7477", to_date（"2015-11-30 10:56:12","YYYY-MM-DD HH24:mi:ss"））;
insert into tb_test values （"1000008", "AB7477", to_date（"2015-11-30 10:57:12","YYYY-MM-DD HH24:mi:ss"））;
-- ---------------------
insert into tb_test values （"1000009", "AB3808", to_date（"2015-11-30 11:00:12","YYYY-MM-DD HH24:mi:ss"））;
insert into tb_test values （"1000010", "AB3808", to_date（"2015-11-30 11:10:13","YYYY-MM-DD HH24:mi:ss"））;
insert into tb_test values （"1000011", "AB3808", to_date（"2015-11-30 11:15:12","YYYY-MM-DD HH24:mi:ss"））;
insert into tb_test values （"1000012", "AB3808", to_date（"2015-11-30 11:26:12","YYYY-MM-DD HH24:mi:ss"））;
insert into tb_test values （"1000013", "AB3808", to_date（"2015-11-30 11:30:12","YYYY-MM-DD HH24:mi:ss"））;表初始化数据为： 2、示例 a、获取当前记录的id，以及下一条记录的id  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 运行结果如下： b、获取当前记录的id，以及上一条记录的idselect 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 运行结果如下： c、获取号牌号码相同的，当前记录的id与，下一条记录的id（使用partition by）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 运行结果如下： d、查询 cphm的总数，当create_date与下一条记录的create_date时间间隔不超过10分钟则忽略。select cphm, count（1） total from
（
select t.id,
  t.create_date t1,
  lead（t.create_date,1, null） over（ partition by  cphm order by create_date asc ） t2, 
  （ lead（t.create_date,1, null） over（  partition by  cphm order by create_date asc ）  - t.create_date ） * 86400 as itvtime,
  t.cphm
from tb_test t
  order by t.cphm, t.create_date asc
） tt
where tt.itvtime >= 600 or  tt.itvtime  is null
group by tt.cphm结果如下： 更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址