Oracle函数lag和lead详解

迈不过友情╰ 2022-10-27 12:10 1300阅读 0赞

1.函数功能

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。

2.lag和lead语法：

ag(exp\_str,offset,defval) over()  
exp\_str 是要做对比的字段  
offset 是exp\_str字段的偏移量 比如说 offset 为2 则 拿exp\_str的第一行和第三行对比，第二行和第四行，依次类推，offset的默认值为1！  
defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。

3.示例：

面以求同比、环比为例，给出lag的用法，lead类似：

数据准备：

create table tmp\_lxq\_1  
(  
statdate date,  
province varchar2(25) null,  
premium number(18)  
);  
  
insert into tmp\_lxq\_1 select date '2012-01-01', '北京', 100 from dual;  
insert into tmp\_lxq\_1 select date '2012-02-01', '北京', 110 from dual;  
insert into tmp\_lxq\_1 select date '2012-03-01', '北京', 120 from dual;  
insert into tmp\_lxq\_1 select date '2012-04-01', '北京', 130 from dual;  
insert into tmp\_lxq\_1 select date '2012-05-01', '北京', 150 from dual;  
insert into tmp\_lxq\_1 select date '2012-06-01', '北京', 160 from dual;  
insert into tmp\_lxq\_1 select date '2012-07-01', '北京', 170 from dual;  
insert into tmp\_lxq\_1 select date '2012-08-01', '北京', 180 from dual;  
insert into tmp\_lxq\_1 select date '2012-09-01', '北京', 190 from dual;  
insert into tmp\_lxq\_1 select date '2012-10-01', '北京', 1100 from dual;  
insert into tmp\_lxq\_1 select date '2012-11-01', '北京', 1110 from dual;  
insert into tmp\_lxq\_1 select date '2012-12-01', '北京', 1120 from dual;  
insert into tmp\_lxq\_1 select date '2013-01-01', '北京', 1130 from dual;  
commit;

求环比数据：

select t.statdate,  
       t.province,  
       t.premium as 当期数,  
       lag(premium, 1, null) over(order by statdate asc) as 前期数,  
       round((t.premium - (lag(premium, 1, null) over(order by statdate asc))) /  
       lag(premium, 1, null) over(order by statdate asc),2) as 环比增长  
  from tmp\_lxq\_1 t;

结果：

![SouthEast][]

求同比增长：

select t.statdate,  
       t.province,  
       t.premium as 当期数,  
       lag(premium, 12, null) over(order by statdate asc) as 前期数,  
       (t.premium - (lag(premium, 12, null) over(order by statdate asc))) /  
       lag(premium, 12, null) over(order by statdate asc) as 同比增长  
  from tmp\_lxq\_1 t;

结果：

![SouthEast 1][]

总结：

使用lag和lead相对来说比表连接效率高很多，近期做的项目中很多都求同比环比，使用起来非常方便。

[SouthEast]: /images/20221024/cdb313ce570540a28b197ac49e5cae31.png
[SouthEast 1]: /images/20221024/1e9fd636078c484d925da994419288cc.png