Oracle常用函数--lag()函数和lead()函数

Lag函数可以在一次查询中取出当前行的同一字段的前面第N行的数据。

Lead函数可以在一次查询中取出当前行的同一字段的后面第N行的值。

这种操作可以使用对相同表的表连接来实现,不过使用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没有符合条件的默认值,num默认值为1。

     leg(field, num, defaultvalue)  field需要查找的字段,num往前查找的num行的数据,defaultvalue没有符合条件的默认值,num默认值为1。

以下是lag例子:

没有设置defaultvalue值.num默认是1:

select ename,job,sal ,lag(sal) over(order by sal) last_sal from emp;
ENAME       JOB                   SAL        LAST_SAL
----------      ---------             ----------       ----------
SMITH       CLERK               800                       --此时没有设置defaultvalue值 则为空值
JAMES      CLERK               950           800      --这里的800来自第一行字段sal里的值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.
-------------------------------------------------------------------------------------------------------------------------

设置了defaultvalue值之后 第一行对应的值 为500:
select ename,job,sal ,lag(sal,1,500) over(order by sal) last_sal from emp;
ENAME       JOB                   SAL        LAST_SAL
----------      ---------             ----------       ----------
SMITH       CLERK               800          500      --此时设置defaultvalue 值 
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
-------------------------------------------------------------------------------------------------------------------------

指定num的值为2时
select ename,job,sal ,lag(sal,2) over(order by sal) last_sal from emp;
ENAME         JOB                   SAL         LAST_SAL
----------       ---------               ----------      ----------
SMITH        CLERK               800
JAMES       CLERK               950
ADAMS      CLERK               1100          800   --这里的800来自第一行字段sal里的值800
WARD        SALESMAN       1250          950
MARTIN     SALESMAN       1250          1100
MILLER      CLERK               1300         1250
TURNER    SALESMAN       1500         1250
ALLEN       SALESMAN        1600         1300
CLARK       MANAGER         2450         1500
BLAKE       MANAGER         2850         1600
JONES       MANAGER         2975         2450
SCOTT       ANALYST           3000         2850
FORD        ANALYST            3000         2975
KING         PRESIDENT        5000         3000
14 rows selected.

使用lead分析函数:

select ename,job,sal ,lead(sal) over(order by sal) last_sal from emp;
ENAME          JOB              SAL       LAST_SAL
----------        ---------            ----------     ----------
SMITH        CLERK             800          950      --这里的950来自第二行字段sal里的值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                   --此时没有设置defaultvalue值 则为空值
14 rows selected.
----------------------------------------------------------------------------------------------------------------------------
Lead和Lag函数也可以使用分组,以下是使用job 分组的例子:

select ename,job,sal ,lead(sal,1) over(partition by job order by sal) last_sal from emp;
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.


select ename,job,sal ,lag(sal,1) over(partition by job order by sal) last_sal from emp;
ENAME       JOB                SAL          LAST_SAL
----------      ---------            ----------      ----------
FORD        ANALYST        3000
SCOTT      ANALYST        3000         3000
SMITH       CLERK            800
JAMES      CLERK            950           800
ADAMS     CLERK            1100          950
MILLER     CLERK            1300         1100
CLARK      MANAGER      2450
BLAKE      MANAGER       2850         2450
JONES      MANAGER       2975         2850
KING         PRESIDENT     5000
MARTIN    SALESMAN      1250
WARD       SALESMAN      1250         1250
TURNER   SALESMAN      1500         1250
ALLEN       SALESMAN      1600         1500
14 rows selected.
注意:

  使用分析函数的时候注意空值 或者null 给数据带来的影响,数据是否允许为空或者null计算的时候会导致一定的差错 比如 800-null 肯定为null!这个结果是否是应用想要的结果?

 

上一篇:Java-基础编程-多线程


下一篇:多线程