lagn matlab,sql 使用LAG和LEAD函数统计

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