oracle上浮下浮分析函数_【分析函数】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 值之后 第一行对应的值 为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.

指定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！这个结果是否是应用想要的结果？