lag和lead函数

最新推荐文章于 2024-07-05 14:27:09 发布

cizongzhic79075

最新推荐文章于 2024-07-05 14:27:09 发布

阅读量365

点赞数

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中的第三个参数指定了默认值，这是可选的。如果分析函数中引用了不存在的行则会返回空值，这是默认的行为。不过这个值可以改变如：

SYS@orcl> 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;

SYS@orcl> 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行。

SYS@orcl> 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函数相似，只是他访问当前行前面行的数据。

SYS@orcl> 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子句可以改变分区内的排序。通过有效的选择分区和排序列可以访问一个结果集中的任意一行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27042095/viewspace-740448/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27042095/viewspace-740448/