SQL中的LAG函数与LEAD函数用法

最新推荐文章于 2024-05-28 13:45:13 发布

yyf960126

最新推荐文章于 2024-05-28 13:45:13 发布

阅读量309

点赞数 2

文章标签： sql 数据库

本文链接：https://blog.csdn.net/yyf960126/article/details/128412568

版权

LAG：函数用于获取结果集中当前行之前的某一行的值

LAG (scalar_expression [,offset] [,default]) OVER ([partition_by_clause ] order_by_clause )
-----汉字解释
LAG (字段 [,偏移量默认为1] [,如果没有值时候默认值]) OVER ( [ partition_by 字段 ] order_by 字段 )

Lead：函数用于获取结果集中当前行之后的某一行的值

LEAD (scalar_expression [,offset] [,default]) OVER ([partition_by_clause ] order_by_clause )
-----汉字解释
LEAD (字段 [,偏移量默认为1] [,如果没有值时候默认值]) OVER ( [ partition_by 字段 ] order_by 字段 )

原数据

select * from tmp
 order_id | order_date | customer_name |  price   | product_id | order_status 
----------+------------+---------------+----------+------------+--------------
        1 | 2020-07-28 | Jark          | 50.50000 |        102 | f
        2 | 2020-07-29 | Sally         | 15.00000 |        105 | f
        3 | 2020-07-30 | Edward        | 25.25000 |        106 | f
        4 | 2020-07-31 | Edward        | 25.25000 |        106 | f

select *,lag(order_id,1,99) over(partition by order_status order by order_Date) from tmp;

 order_id | order_date | customer_name |  price   | product_id | order_status | lag 
----------+------------+---------------+----------+------------+--------------+-----
        1 | 2020-07-28 | Jark          | 50.50000 |        102 | f            |  99
        2 | 2020-07-29 | Sally         | 15.00000 |        105 | f            |   1
        3 | 2020-07-30 | Edward        | 25.25000 |        106 | f            |   2
        4 | 2020-07-31 | Edward        | 25.25000 |        106 | f            |   3
------------------------------------------------------------------------------------
select *,lead(order_id,1,99) over(partition by order_status order by order_Date) from tmp;

 order_id | order_date | customer_name |  price   | product_id | order_status | lead 
----------+------------+---------------+----------+------------+--------------+------
        1 | 2020-07-28 | Jark          | 50.50000 |        102 | f            |    2
        2 | 2020-07-29 | Sally         | 15.00000 |        105 | f            |    3
        3 | 2020-07-30 | Edward        | 25.25000 |        106 | f            |    4
        4 | 2020-07-31 | Edward        | 25.25000 |        106 | f            |   99

lag和lead的用法中order by的顺序都是asc的，也就是这两个可以使用降序排序的写法就能等于另一个了，

lag（）over（order by 字段 desc）=lead over（order by 字段）

lead（）over (order by 字段 desc）=lag over（order by 字段）