oracle lead和lag函数用法
Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。以下是LAG和LEAD的例子：

SQL> select year,region,profit ,lag (profit,1) over (order by year)

?

2?????? as last_year_exp from test;

?

YEAR REGION????? PROFIT LAST_YEAR_EXP

---- ------- ---------- -------------

2003 West??????????? 88

2003 West??????????? 88??????????? 88

2003 Central??????? 101??????????? 88

2003 Central??????? 100?????????? 101

2003 East?????????? 102?????????? 100

2004 West??????????? 77?????????? 102

2004 East?????????? 103??????????? 77

2004 West??????????? 89?????????? 103

?

SQL> select year,region,profit ,lead (profit,1) over (order by year)

?

2?????? as next_year_exp from test;

?

YEAR REGION????? PROFIT NEXT_YEAR_EXP

---- ------- ---------- -------------

2003 West??????????? 88??????????? 88

2003 West??????????? 88?????????? 101

2003 Central??????? 101?????????? 100

2003 Central??????? 100?????????? 102

2003 East?????????? 102??????????? 77

2004 West??????????? 77?????????? 103

2004 East?????????? 103????? ??????89

2004 West??????????? 89

Lag函数为Lag(exp,N,defval)，defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。

Lead和Lag函数也可以使用分组，以下是使用region分组的例子：

SQL> select year,region,profit ,

2 lag (profit,1,0) over (PARTITION BY region order by year)

?

3?????? as last_year_exp from test;

?

YEAR REGION????? PROFIT LAST_YEAR_EXP

---- ------- ---------- -------------

2003 Central??????? 101???????????? 0

2003 Central??????? 100?????????? 101

2003 East?????????? 102???????????? 0

2004 East?????????? 103?????????? 102

2003 West?????? ?????88???????????? 0

2003 West??????????? 88??????????? 88

2004 West??????????? 77??????????? 88

2004 West??????????? 89??????????? 77