lag和lead 分析函数

oracle 的分析函数是非常好的一个功能，借助它们，我们可以很方便的实现一些特殊的语句需求，省去了自己实现的诸多麻烦。

 今天用到了lag 和lead 这两个分析函数，稍稍整理一下。

lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；
lag ，lead 分别是向前，向后；
lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值）

举例如下：



SQL> select *  from kkk;                                          
                                                                  
        ID NAME                                                   
---------- --------------------                                   
         1 1name                                                  
         2 2name                                                  
         3 3name                                                  
         4 4name                                                  
         5 5name                                                  
                                                                  
SQL> select id,name,lag(name,1,0) over ( order by id )  from kkk; 
                                                                  
        ID NAME                 LAG(NAME,1,0)OVER(ORDERBYID)      
---------- -------------------- ----------------------------      
         1 1name                0                                 
         2 2name                1name                             
         3 3name                2name                             
         4 4name                3name                             
         5 5name                4name                             
                                                                  
SQL> select id,name,lead(name,1,0) over ( order by id )  from kkk;
                                                                  
        ID NAME                 LEAD(NAME,1,0)OVER(ORDERBYID)     
---------- -------------------- -----------------------------     
         1 1name                2name                             
         2 2name                3name                             
         3 3name                4name                             
         4 4name                5name                             
         5 5name                0                                 
                                                                  
SQL>  
SQL> select id,name,lead(name,2,0) over ( order by id )  from kkk;                                                            
                                                                  
        ID NAME                 LEAD(NAME,2,0)OVER(ORDERBYID)     
---------- -------------------- -----------------------------     
         1 1name                3name                             
         2 2name                4name                             
         3 3name                5name                             
         4 4name                0                                 
         5 5name                0                                 
                                                                  
SQL> 
SQL> select id,name,lead(name,1,'alsdfjlasdjfsaf') over ( order by id )  from kkk;                                                             
                                                                                  
        ID NAME                 LEAD(NAME,1,'ALSDFJLASDJFSAF')                    
---------- -------------------- ------------------------------                    
         1 1name                2name                                             
         2 2name                3name                                             
         3 3name                4name                                             
         4 4name                5name                                             
         5 5name                alsdfjlasdjfsaf                                   
                                                                                  
SQL>