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 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 字段)
原文地址:https://blog.csdn.net/yyf960126/article/details/128412568
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!