如何让PostgreSQL的LAG()/LEAD()函数取同分区下的下一个排名值而非相邻行？

没问题！你现在碰到的是分区里有多个相同排名的行，默认的LAG()和LEAD()只会按行的物理顺序取相邻行，但我们可以通过两步处理来让它们跳过同排名的行，直接定位到分区里的上一个/下一个不同排名值。我给你两种常用的实现方式：

方法1：提取唯一排名后关联原表 #

这种思路是先把每个分区（比如按client_id分区）内的唯一排名提取出来，在这个去重后的数据集上计算上一个/下一个排名，再关联回原表，让所有同排名的行都能拿到对应的目标排名值。

WITH unique_ranks AS (
    SELECT 
        client_id,
        rank,
        -- 取当前分区内上一个不同的排名
        LAG(rank) OVER (PARTITION BY client_id ORDER BY rank) AS prev_rank,
        -- 取当前分区内下一个不同的排名
        LEAD(rank) OVER (PARTITION BY client_id ORDER BY rank) AS next_rank
    FROM (
        -- 先提取每个client_id下的唯一rank值
        SELECT DISTINCT client_id, rank
        FROM your_table
    ) AS distinct_ranks
)
SELECT 
    t.*,
    ur.prev_rank,
    ur.next_rank
FROM your_table t
JOIN unique_ranks ur 
    ON t.client_id = ur.client_id 
    AND t.rank = ur.rank;

针对你的示例数据，运行这段SQL后，client_id=1的所有rank=1的行都会得到prev_rank=null、next_rank=4；所有rank=4的行得到prev_rank=1、next_rank=6；rank=6的行得到prev_rank=4、next_rank=null，完全符合你要的效果。

方法2：用窗口函数广播组内的排名结果 #

如果你不想用关联查询，也可以通过给同排名的行编号，再用FIRST_VALUE()把组内第一行的正确排名值广播到整个组：

WITH ranked_groups AS (
    SELECT 
        *,
        -- 给每个同排名的组内编序号
        ROW_NUMBER() OVER (PARTITION BY client_id, rank ORDER BY order_id) AS row_in_rank,
        -- 这里按client_id分区、rank排序，组内第一行的LAG/LEAD会指向不同的排名
        LAG(rank) OVER (PARTITION BY client_id ORDER BY rank) AS temp_prev_rank,
        LEAD(rank) OVER (PARTITION BY client_id ORDER BY rank) AS temp_next_rank
    FROM your_table
)
SELECT 
    client_id, order_id, product_id, year, rank,
    -- 把组内第一行的prev_rank值应用到所有同rank的行
    FIRST_VALUE(temp_prev_rank) OVER (PARTITION BY client_id, rank ORDER BY row_in_rank) AS prev_rank,
    FIRST_VALUE(temp_next_rank) OVER (PARTITION BY client_id, rank ORDER BY row_in_rank) AS next_rank
FROM ranked_groups;

这个方法的逻辑是：同一排名的行会被分到同一个组，组内第一行的LAG()会拿到上一个不同的排名，后面的行因为和前一行是同排名，所以LAG()结果会是当前排名，我们用FIRST_VALUE()把第一行的正确结果提取出来，让组内所有行共享这个值。

小提示 #

如果你的rank字段本身是通过RANK()或DENSE_RANK()生成的，可以直接把生成rank的逻辑整合到CTE里，不需要单独处理原表的rank字段。

内容的提问来源于stack exchange，提问作者blahblah