SQL中的LAG函数与LEAD函数用法
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 字段）