lag和lead函式

LAG和LEAD是偏移量函式，提供了跨行引用的能力。 LAG提供了訪問結果集中前面的行的能力， Lead允許訪問結果集後面的行。

語法：lag(expression, offset, default) over(partition-clause order-by-clause)

分析型SQL中的資料是按照分割槽列的值來進行分割槽的。獲取前一行的值是一個與位置相關的運算，分割槽中的各行的順序對於維護邏輯上的一致性是很重要的。在一個資料分割槽內部，資料行透過order by子句來進行排序以控制某一行在結果集中的位置。

lag和lead函式都不支援開窗子句，僅支援partition-by子句和order by 子句。

1、從下一行中返回一個值

select * from tache_columns

       TCH_ID COL_NAME_CN                                                     COL_VALUE
------------- -------------------------------------------------------------------------------- ------
            1 張三                                                                             10
            2 李四                                                                             21
            3 王五                                                                             23
            1 aaa                                                                              14
            1 bbb                                                                              20

select t.tch_id,
       t.col_name_cn,
       t.col_value,
       lead(t.col_value,1,null) over(partition by t.tch_id order by t.col_value) sal
from tache_columns t；

 

<=>

select t.tch_id,
       t.col_name_cn,
       t.col_value,
       lead(t.col_value) over(partition by t.tch_id order by t.col_value) sal
from tache_columns t；

 

TCH_ID COL_NAME_C COL_VALUE                                SAL
---------- ---------- ---------------------------------------- ----------------------------------------
         1 張三       10                                       14
         1 aaa        14                                       20
         1 bbb        20
         2 李四       21
         3 王五       23

lead中的第三個引數指定了預設值， 這是可選的。如果分析函式中引用了不存在的行則會返回空值，這是預設的行為。不過這個值可以改變如：

 

> select tch_id, col_name_cn, col_value, lead(col_value,1,'') over(partition by tch_id order by col_value) sal from tache_columns;

    TCH_ID COL_NAME_C COL_VALUE                                SAL
---------- ---------- ---------------------------------------- ----------------------------------------
         1 張三       10                                       14
         1 aaa        14                                       20
         1 bbb        20                                      
         2 李四       21                                      
         3 王五       23                                      

 

 

 

 

> select tch_id, col_name_cn, col_value, lead(col_value,1,col_value) over(partition by tch_id order by col_value) sal from tache_columns;

TCH_ID COL_NAME_C COL_VALUE                                SAL
---------- ---------- ---------------------------------------- ----------------------------------------
         1 張三       10                                       14
         1 aaa        14                                       20
         1 bbb        20                                       20
         2 李四       21                                       21
         3 王五       23                                       23

由於lead函式的第三個引數是col_value,當引用行不存在的時候， lead函式就返回當前行的col_value值。

 

 

2、資料行的位移

 

透過指定一個位移可以來訪問一個資料分割槽中的其他行。

lead和lag函式第二次引數是可選的，預設是1，表示偏移量為1行。

> select tch_id, col_name_cn, col_value, lead(col_value,2,'') over(partition by tch_id order by col_value) sal from tache_columns;

    TCH_ID COL_NAME_C COL_VALUE                                SAL
---------- ---------- ---------------------------------------- ----------------------------------------
         1 張三       10                                       20
         1 aaa        14                                      
         1 bbb        20                                      
         2 李四       21                                      
         3 王五       23                                      

 

Lead函式透過指定偏移量2來訪問向後的第2行資料。

 

 

3、從前一行中返回一個值

 

lag函式和lead函式相似， 只是他訪問當前行前面行的資料。

> select tch_id, col_name_cn, col_value, lag(col_value,1, col_value) over(partition by tch_id order by col_value) sal from tache_columns;

    TCH_ID COL_NAME_C COL_VALUE                                SAL
---------- ---------- ---------------------------------------- ----------------------------------------
         1 張三       10                                       10
         1 aaa        14                                       10
         1 bbb        20                                       14
         2 李四       21                                       21
         3 王五       23                                       23

 

 

partition -by 子句可以用來指定不同的分割槽邊界， 而order-by子句可以改變分割槽內的排序。透過有效的選擇分割槽和排序列可以訪問一個結果集中的任意一行。