本文介绍了下一级的LAG（）/ LEAD（）（PostgreSQL）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
有什么方法可以强制PostgreSQL LAG（）和 LEAD（）函数使用非前导值行，但来自同一分区的下一个排名？
Is there any way to force PostgreSQL LAG() and LEAD() functions to use values not from leading row but next rank from the same partition?
---------------------------------------------------
| client_id | order_id | product_id | year | rank |
---------------------------------------------------
| 1 | 1 | 111345 | 1995 | 1 |
| 1 | 1 | 912346 | 1995 | 1 |
| 1 | 1 | 212346 | 1995 | 1 |
| 1 | 2 | 233368 | 1998 | 4 |
| 1 | 2 | 133368 | 1998 | 4 |
| 1 | 3 | 412341 | 2005 | 6 |
| 2 | 55 | 312344 | 1995 | 1 |
| 2 | 57 | 812343 | 1999 | 2 |
---------------------------------------------------
预期结果将是：
---------------------------------------------------------------------------
| client_id | order_id | product_id | year | rank | prev_year | next_year |
---------------------------------------------------------------------------
| 1 | 1 | 111345 | 1995 | 1 | null | 1998 |
| 1 | 1 | 912346 | 1995 | 1 | null | 1998 |
| 1 | 1 | 212346 | 1995 | 1 | null | 1998 |
| 1 | 2 | 233368 | 1998 | 4 | 1995 | 2005 |
| 1 | 2 | 133368 | 1998 | 4 | 1995 | 2005 |
| 1 | 3 | 412341 | 2005 | 6 | 1998 | null |
| 2 | 55 | 312344 | 1995 | 1 | null | 1999 |
| 2 | 57 | 812343 | 1999 | 2 | 1995 | null |
---------------------------------------------------------------------------
如果是 year 在给定排名中具有 distinct 值的情况，则 prev_year 和 next_year 可以是任何这些值。例如：
If it were the case that year had distinct values in given rank, then prev_year and next_year could be any of those values. eg:
---------------------------------------------------------------------------
| client_id | order_id | product_id | year | rank | prev_year | next_year |
---------------------------------------------------------------------------
| 1 | 1 | 111345 | 1994 | 1 | null | 1998 |
| 1 | 1 | 912346 | 1995 | 1 | null | 1998 |
| 1 | 1 | 212346 | 1996 | 1 | null | 1998 |
| 1 | 2 | 233368 | 1998 | 4 | ???? | null |
???? 可以等于1994 ，1995或1996
???? can equal to 1994, 1995 or 1996
推荐答案
您应使用 lag（）和 lead（）函数减少为每对（client_id，rank）：
You should use lag() and lead() functions on dataset reduced to a single row per the pair (client_id, rank):
select
client_id, order_id, product_id,
t.year, rank, prev_year, next_year
from my_table t
join (
select distinct on (client_id, rank)
client_id, rank, year,
lag(year) over w as prev_year,
lead(year) over w as next_year
from my_table
window w as (partition by client_id order by rank)
order by 1, 2, 3 desc
) s using (client_id, rank)
order by client_id, rank
client_id | order_id | product_id | year | rank | prev_year | next_year
-----------+----------+------------+------+------+-----------+-----------
1 | 1 | 212346 | 1995 | 1 | | 1998
1 | 1 | 912346 | 1995 | 1 | | 1998
1 | 1 | 111345 | 1995 | 1 | | 1998
1 | 2 | 133368 | 1998 | 4 | 1995 | 2005
1 | 2 | 233368 | 1998 | 4 | 1995 | 2005
1 | 3 | 412341 | 2005 | 6 | 1998 |
2 | 55 | 312344 | 1995 | 1 | | 1999
2 | 57 | 812343 | 1999 | 2 | 1995 |
(8 rows)
这篇关于下一级的LAG（）/ LEAD（）（PostgreSQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！