PLSQL专项学习之LEAD()和LAG()函数

背景：

在公司ETL工具中，在日志统计分析阶段，需要统计执行速度，也就是每秒执行多少条记录。因为需要取同一个字段的时间差，所以就想到了LEAD()函数。

 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字段进行排序，对数据进行查询。

通俗理解：

其实这2个函数的作用非常好理解，Lead()就是取当前顺序的下一条记录，相对Lag()就是取当前顺序的上一行记录。

经常会有判断在一定条件下的两条记录之间的时间差这样的需求。

Lead()函数的用法和Lag()是一样的，所以只说明一个就可以了。

语法：

 

LEAD(value_expr [, offset ] [, default ])
   OVER ([ query_partition_clause ] order_by_clause)

参数说明：

value_expr 值表达式，通常是字段，也可是是表达式。value_expr本身不支持分析函数，也就是lead不支持多层调用。
offset 偏移，应该是很熟悉的数学概念了，或者是相对偏移，表格来开当前行的第offset行，如果offset是整数就表示是顺序下的前第n行，如果是负数就是往后第n行。 如果不提供这个参数，就是默认为1.
default 默认值，如果没有找到，应该返回什么值的意思，有点类似nvl(col,value)。如果没有设置，且找不到，那么就返回Null
over  理解成在一个结果集范围内，如果后面的partition by为空，那么就是当前的结果集范围内。

query_partition_clause  分区语句，对结果集合分区的语句，是可选的，如果没有就是所有的一个分区。
Order_by_clause 排序语句 必须需要 ,形如order by xxx desc/asc

案例分析：

with tmp as(  
select '1' id ,'aa' name from dual union all  
select '2' id ,'bb' name from dual union all
select '3' id ,'cc' name from dual union all
select '4' id ,'dd' name from dual union all
select '5' id ,'ee' name from dual union all
select '6' id ,'ff' name from dual union all
select '7' id ,'gg' name from dual union all
select '8' id ,'hh' name from dual union all
select '9' id ,'ii' name from dual union all
select '10' id ,'jj' name from dual 
)  
select a.*,  
       lag(name,1) over (order by id desc) lag0,
       lead(name) over (order by id desc) lead0, 
       lead(name,1) over (order by id desc) lead1,
       lead(name,2) over (order by id desc) lead2,
       lead(name,2,'ww') over (order by id desc) lead3 
        
from tmp a  order by id  

 

至此为止，欢迎留言 ！