本文详细介绍了SQL中的Lag和Lead函数的使用方法及应用场景，包括如何获取前后N行数据、设置默认值以及结合分组使用等技巧。
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@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 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.
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) 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.
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！这个结果是否是应用想要的结果？
细心很重要！！尤其是在计算和钱有关的情况下！！
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数我们可以取到当前行列的偏移N行列的值 lag可以看着是正的向上的偏移 lead可以认为负的向下的偏移 具体我们来看几个例子：
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数我们可以取到当前行列的偏移N行列的值 lag可以看着是正的向上的偏移 lead可以认为负的向下的偏移 具体我们来看几个例子：
我们先看下scott的emp表的两列数据:
select deptno, sal from scott.emp order by deptno
DEPTNO
SAL
10
2450.00
10
5000.00
10
1300.00
20
2975.00
20
3000.00
20
1100.00
20
800.00
20
3000.00
30
1250.00
30
1500.00
30
1600.00
30
950.00
30
2850.00
30
1250.00
ok那现在比方我有个这样的需求（我们只看sal列）我想问你2450的上一个值是多少？回答是没有 那5000的上一个值是多少？是：2450 1300的上一个值是多少呢？是：5000 Ok以此类推我想得到当前值的上一个值
就像：2450 xxx(xxx代表空）--这个值是前一列的上一个值
5000.00
2450
1300.00
5000
2975.00
1300
3000.00
2975
1100.00
3000
... ...
1250.00 2850
OK就这样的需求 那我们现在用SQL应该如何写呢？是的你猜对了就是用lag分析函数:
select deptno, sal a, lag(sal, 1, sal) b over(order by deptno)
from scott.emp
DEPTNO
A
B
10
2450.00
2450 --ps:这里的之所以是2450是因为lag(sal, 1, sal)我让它给了他本身的值
10
5000.00
2450
10
1300.00
5000
20
2975.00
1300
20
3000.00
2975
20
1100.00
3000
20
800.00
1100
20
3000.00
800
30
1250.00
3000
30
1500.00
1250
30
1600.00
1500
30
950.00
1600
30
2850.00
950
30
1250.00
2850
是的就这么简单你看出A列与B列之间有何联系了吧 相对A列B列是她的上一个值
关于lead她就刚好与lag相反了
select deptno, sal a, lead(sal, 1, sal) over(order by deptno) b
from scott.emp
DEPTNO
A
B
10
2450.00
5000
10
5000.00
1300
10
1300.00
2975
20
2975.00
3000
20
3000.00
1100
20
1100.00
800
20
800.00
3000
20
3000.00
1250
30
1250.00
1500
30
1500.00
1600
30
1600.00
950
30
950.00
2850
30
2850.00
1250
30
1250.00
1250
相对A列B列是她的下一个值
另外那个偏移值1是可以随便取的如果是2那就是偏移两个值了
select deptno, sal a, lag(sal, 2，null) over(order by deptno) b
from scott.emp
DEPTNO
A
B
10
2450.00
--注意这里是null空了
10
5000.00
10
1300.00
2450 --A列1300的上两个值是多少？2450是吧
20
2975.00
5000
20
3000.00
1300
20
1100.00
2975
20
800.00
3000
20
3000.00
1100
30
1250.00
800
30
1500.00
3000
30
1600.00
1250
30
950.00
1500
30
2850.00
1600
30
1250.00
950
OK 那其实lag,lead还可以加上分组偏移的
select deptno,
sal a,
lag(sal, 1, null) over(
partition by deptno order by deptno) b
from scott.emp
DEPTNO
A
B
10
2450.00
10
5000.00
2450
10
1300.00
5000
20
2975.00
20
3000.00
2975
20
1100.00
3000
20
800.00
1100
20
3000.00
800
30
1250.00
30
1500.00
1250
30
1600.00
1500
30
950.00
1600
30
2850.00
950
30
1250.00
2850
注意deptno不同的分组间的临界值你看明白了吧
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
