Lag函数可以在一次查询中取出当前行的同一字段的前面第N行的数据。
Lead函数可以在一次查询中取出当前行的同一字段的后面第N行的值。
这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。
over()表示 Lag()与Lead()操作的数据都在over()的范围内，他里面可以使用partition by 语句（用于分组） order by 语句（用于排序）。
partition by a order by b表示以a字段进行分组，再 以b字段进行排序，对数据进行查询。
例如：lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值,num默认值为1。
leg(field, num, defaultvalue) field需要查找的字段，num往前查找的num行的数据，defaultvalue没有符合条件的默认值,num默认值为1。
以下是lag例子：
没有设置defaultvalue值.num默认是1:
select ename,job,sal ,lag(sal) over(order by sal) last_sal from emp;
ENAME JOB SAL LAST_SAL
---------- --------- ---------- ----------
SMITH CLERK 800 --此时没有设置defaultvalue值 则为空值
JAMES CLERK 950 800 --这里的800来自第一行字段sal里的值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.
-------------------------------------------------------------------------------------------------------------------------
设置了defaultvalue值之后 第一行对应的值 为500：
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 --此时设置defaultvalue 值
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
-------------------------------------------------------------------------------------------------------------------------
指定num的值为2时
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 --这里的800来自第一行字段sal里的值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.
使用lead分析函数:
select ename,job,sal ,lead(sal) over(order by sal) last_sal from emp;
ENAME JOB SAL LAST_SAL
---------- --------- --------- ----------
SMITH CLERK 800 950 --这里的950来自第二行字段sal里的值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 --此时没有设置defaultvalue值 则为空值
14 rows selected.
----------------------------------------------------------------------------------------------------------------------------
Lead和Lag函数也可以使用分组，以下是使用job 分组的例子：
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.
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！这个结果是否是应用想要的结果？