【转】oracle的LAG跟LEAD分析函数

【转】oracle的LAG和LEAD分析函数

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> 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 值之后 第一行对应的值 为500SCOTT@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.指定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.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??????????? 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.
SCOTT@yangdb>使用分析函数的时候注意空值 或者null 给数据带来的影响，数据是否允许为空或者null计算的时候会导致一定的差错 比如 800-null 肯定为null！这个结果是否是应用想要的结果？细心很重要！！尤其是在计算和钱有关的情况下！！ 1 楼 ptsd 2012-03-20   lag(column_x,number1,XX)用来返回当前数据行的字段column_x的前第number1行数据,当须返回的字段为空或不存在时，返回XX

查看更多 下一篇