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 tmporder_id | order_date | customer_name |  price   | product_id | order_status 
----------+------------+---------------+----------+------------+--------------1 | 2020-07-28 | Jark          | 50.50000 |        102 | f2 | 2020-07-29 | Sally         | 15.00000 |        105 | f3 | 2020-07-30 | Edward        | 25.25000 |        106 | f4 | 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            |  992 | 2020-07-29 | Sally         | 15.00000 |        105 | f            |   13 | 2020-07-30 | Edward        | 25.25000 |        106 | f            |   24 | 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            |    22 | 2020-07-29 | Sally         | 15.00000 |        105 | f            |    33 | 2020-07-30 | Edward        | 25.25000 |        106 | f            |    44 | 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 字段）