PostgreSQL中仅为特定行生成有序排名的实现问询

问题拆解 #

你需要按COL1、COL2排序后，对连续的COL3='V1'行分配递增的排名（同一连续块内排名相同），COL3='V2'的行则将RANK设为NULL——核心是识别被V2分隔的连续V1块，再给每个块分配唯一排名。

解决方案代码 #

WITH block_identifier AS (
    SELECT 
        COL1,
        COL2,
        COL3,
        -- 计算连续V1块的分组ID：每当新的V1块开始，ID递增
        SUM(
            CASE 
                WHEN COL3 = 'V1' 
                     AND (LAG(COL3) OVER (PARTITION BY COL1 ORDER BY COL2) != 'V1' 
                          OR LAG(COL3) OVER (PARTITION BY COL1 ORDER BY COL2) IS NULL)
                THEN 1 
                ELSE 0 
            END
        ) OVER (PARTITION BY COL1 ORDER BY COL2) AS group_rank
    FROM your_table_name
)
SELECT 
    COL1,
    COL2,
    COL3,
    -- 仅V1行显示排名，V2行返回NULL
    CASE WHEN COL3 = 'V1' THEN group_rank ELSE NULL END AS RANK
FROM block_identifier
ORDER BY COL1, COL2;

代码说明 #

1. CTE block_identifier：

   • LAG(COL3) OVER (...)：按COL1分组、COL2排序，获取当前行的上一行COL3值，用来判断是否进入新的V1块。
   • SUM() OVER (...)：作为累计窗口函数，每当遇到新的V1起始行（当前行是V1且上一行不是V1，或是分区内第一个V1行），就给group_rank加1，这样同一连续V1块内的行共享同一个group_rank。

2. 最终查询：

   • 用CASE语句过滤：只有COL3='V1'的行显示group_rank作为排名，V2行直接返回NULL。
   • 最后按COL1、COL2排序，得到符合预期的结果。

效果验证 #

替换your_table_name为你的实际表名后，执行结果将与你给出的示例完全一致：

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