Oracle分析函数Lead()，Lag()

Oracle分析函数Lead()，Lag()

 

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

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

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

 

语法结构：

lead(value_expr [,offset][,default])over([query_partition_clause] order by 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

1、使用lag分析函数

SCOTT@yangdb> set pagesize 10000
SCOTT@yangdb> select ename,job,sal ,lag(sal) over(order by sal) last_sal fromemp;
ENAME     JOB             SAL   LAST_SAL
---------- --------- ---------- ----------
SMITH     CLERK            800     --此时没有设置default 值 则为空值
JAMES     CLERK           950        800
ADAMS     CLERK          1100        950
WARD      SALESMAN       1250       1100
MARTIN    SALESMAN       1250       1250
MILLER    CLERK          1300       1250
TURNER    SALESMAN       1500       1300
ALLEN     SALESMAN       1600       1500
CLARK     MANAGER        2450       1600
BLAKE     MANAGER        2850       2450
JONES     MANAGER        2975       2850
SCOTT     ANALYST        3000       2975
FORD      ANALYST        3000       3000
KING      PRESIDENT      5000       3000

14 rows selected.

 

2、使用lead分析函数
SCOTT@yangdb> select ename,job,sal,lead(sal) over(order by sal) last_sal from emp;
ENAME     JOB             SAL   LAST_SAL
---------- --------- ---------- ----------
SMITH     CLERK           800        950
JAMES     CLERK           950       1100
ADAMS     CLERK          1100       1250
WARD      SALESMAN       1250       1250
MARTIN    SALESMAN       1250       1300
MILLER    CLERK          1300       1500
TURNER    SALESMAN       1500       1600
ALLEN     SALESMAN       1600       2450
CLARK     MANAGER        2450       2850
BLAKE     MANAGER        2850       2975
JONES     MANAGER        2975       3000
SCOTT     ANALYST        3000       3000
FORD      ANALYST        3000       5000
KING      PRESIDENT       5000

14 rows selected.

lead 的offsetN 是以记录的第N行和第一做对比注意末尾的 null 值！

 

3、Lead和Lag函数也可以使用分组，以下是使用job 分组的例子：

SCOTT@yangdb> selectename,job,sal ,lead(sal,1) over(partition by job order by sal) last_sal fromemp;
ENAME     JOB             SAL   LAST_SAL
---------- --------- ---------- ----------
FORD      ANALYST        3000       3000
SCOTT     ANALYST         3000
SMITH     CLERK           800        950
JAMES     CLERK           950       1100
ADAMS     CLERK          1100       1300
MILLER    CLERK           1300
CLARK     MANAGER        2450       2850
BLAKE     MANAGER        2850       2975
JONES     MANAGER         2975
KING      PRESIDENT       5000
MARTIN    SALESMAN       1250       1250
WARD      SALESMAN       1250       1500
TURNER    SALESMAN        1500      1600
ALLEN     SALESMAN        1600

14 rows selected.