Oracle分析函数之lag和lead 分析函数详解

lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；
lag ，lead 分别是向前，向后；

lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值.

我们先看下scott的emp表的两列数据:

select deptno, sal from scott.emp order by deptno

DEPTNOSAL

102450.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

现在看看lag函数实现的功能：

select deptno, sal a,lag(sal, 1, sal) over(order by deptno) b from scott.emp;

DEPTNO AB

10 2450.002450 --ps:这里的之所以是2450是因为lag(sal, 1, sal)我让它给了他本身的值

10 5000.002450

10 1300.005000

20 2975.001300

20 3000.002975

20 1100.003000

20 800.001100

20 3000.00800

30 1250.003000

30 1500.001250

30 1600.001500

30 950.001600

30 2850.00950

30 1250.002850

而lead刚好和lag功能相反：

select deptno, sal a, lead(sal, 1, sal) over(order by deptno) b from scott.emp;

DEPTNO AB

10 2450.005000

10 5000.001300

10 1300.002975

20 2975.003000

20 3000.001100

20 1100.00800

20 800.003000

20 3000.001250

30 1250.001500

30 1500.001600

30 1600.00950

30 950.002850

30 2850.001250

30 1250.001250

另外那个偏移值1是可以随便取的如果是2那就是偏移两个值了

select deptno, sal a, lag(sal, 2，null) over(order by deptno) b

  from scott.emp

DEPTNO AB

102450.00      --注意这里是null空了

105000.00

101300.00 2450  --A列1300的上两个值是多少？2450是吧

202975.00 5000

203000.00 1300

201100.00 2975

20800.00 3000

203000.00 1100

301250.00 800

301500.00 3000

301600.00 1250

30950.00 1500

302850.00 1600

301250.00 950

那其实lag,lead还可以加上分组偏移的

select deptno,

       sal a,

       lag(sal, 1, null) over(partition by deptno order by deptno) b

  from scott.emp

DEPTNOA B

102450.00

105000.00 2450

101300.00 5000

202975.00

203000.00 2975

201100.00 3000

20800.00 1100

203000.00 800

301250.00

301500.00 1250

301600.00 1500

30950.00 1600

302850.00 950

301250.00 2850

注意deptno不同的分组间的临界值你看明白了吧!

整理自网络

------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tanzuai
IT群:69254049