Oracle的LAG和LEAD分析函数

Oracle的LAG和LEAD分析函数

本篇文章来源于 Linux公社网站(www.linuxidc.com)  原文链接：http://www.linuxidc.com/Linux/2012-03/56765.htm

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

lead 和lag 的语法类似，以下以lag为例进行讲解

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


以下是lag和lead的例子

SCOTT@ www.linuxidc.com> set pagesize 10000
SCOTT@ www.linuxidc.com> select ename,job,sal,lag(sal) over(order by sal) last_sal from emp;
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.


设置了default值之后，第一行对应的值为500

SCOTT@ www.linuxidc.com> 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
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.


指定offset的值为2时

SCOTT@ www.linuxidc.com> 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
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.


offset的值为3

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


使用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.


SCOTT@yangdb> select ename,job,sal,lead(sal,1) 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.


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


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


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

Lead和Lag函数也可以使用分组，以下是使用job分组的例子：
SCOTT@yangdb> 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.

SCOTT@yangdb> 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！这个结果是否是应用想要的结果？
细心很重要！！尤其是在计算和钱有关的情况下！！