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@ www.bkjia.com> set pagesize 10000
SCOTT@ www.bkjia.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.bkjia.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.bkjia.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.bkjia.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.
|
评论暂时关闭