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>