下一级的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）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！