oracle分析函数系列之LAG和LEAD:取出同一字段的前N行的数据和后N行的值

另一篇博文有关于此函数的变通使用:http://blog.csdn.net/rfb0204421/article/details/7609520

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

该函数也可以用于纵向表转横向表、解释示例：
     SQL> select * from test_value;

              MONS JJR               CJL       CJJE
        ---------- ---------- ---------- ----------
            200801 LZF               250       1999
            200802 LZF               200       2000
            200803 LZF               300       1000
            200804 LZF                23        189
            200805 LZF               356        456
            200806 LZF               100        200
            200807 LZF               600        700
            200808 LZF                23        123
            200809 LZF               400        500

        9 rows selected

        SQL>
        SQL> select rownum 序号,Mons,cjl cjl_01,
          2 lead(cjl,1) over (order by mons desc) cjl_02,
          3 lead(cjl,2) over (order by mons desc) cjl_03,
          4 lead(cjl,3) over (order by mons desc) cjl_04,
          5 lead(cjl,4) over (order by mons desc) cjl_05,
          6 lead(cjl,5) over (order by mons desc) cjl_06,
          7 lead(cjl,6) over (order by mons desc) cjl_07,
          8 lead(cjl,7) over (order by mons desc) cjl_08,
          9 lead(cjl,8) over (order by mons desc) cjl_09
         10   from test_value

              序号       MONS     CJL_01     CJL_02     CJL_03     CJL_04     CJL_05     CJL_06     CJL_07     CJL_08     CJL_09
        ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                 9     200809        400         23        600        100        356         23        300        200        250
                 8     200808         23        600        100        356         23        300        200        250
                 7     200807        600        100        356         23        300        200        250
                 6     200806        100        356         23        300        200        250
                 5     200805        356         23        300        200        250
                 4     200804         23        300        200        250
                 3     200803        300        200        250
                 2     200802        200        250
                 1     200801        250

秒客网

oracle分析函数系列之LAG和LEAD:取出同一字段的前N行的数据和后N行的值

相关文章