邢台建站企业邮箱,网站导出链接查询,网上做网站网站,抓取微博 WordpressLAG#xff1a;函数用于获取结果集中当前行之前的某一行的值
LAG (scalar_expression [,offset] [,default]) OVER ([partition_by_clause ] order_by_clause )
-----汉字解释
LAG (字段 [,偏移量默认为1] [,如果没有值时候默认值]) OVER ( [ partition_by 字段 ] order_by 字…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 | fselect *,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 | 99lag和lead的用法中order by的顺序都是asc的也就是这两个可以使用降序排序的写法就能等于另一个了
lagoverorder by 字段 desclead overorder by 字段
leadover (order by 字段 desclag overorder by 字段
