【转】oracle的LAG跟LEAD分析函数
日期：2014-05-16 浏览次数：20453 次
【转】oracle的LAG和LEAD分析函数
![]()
![]()
SCOTT@yangdb> set pagesize 10000
SCOTT@yangdb> 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
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。
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@yangdb> 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@yangdb> 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.
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@yangdb> 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.
SCOTT@yangdb> 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@yangdb> select ename,job,sal ,lag(sal,3) over(order by sal) last_sal from emp;
ENAME????? JOB????????????? SAL?? LAST_SAL
---------- --------- ---------- ----------
SMITH????? CLERK??
SCOTT@yangdb> select ename,job,sal ,lag(sal,3) over(order by sal) last_sal from emp;
ENAME????? JOB????????????? SAL?? LAST_SAL
---------- --------- ---------- ----------
SMITH????? CLERK??
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
