使用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

[@more@]