PostgreSQL中如何按id2分组选取最小差值的唯一id记录？

需求说明 #

需从数据表中查询满足以下条件的记录：

• 每个唯一id2对应唯一id
• b - a、a1 - b1的差值均为最小
• 需满足前置条件b >= a且b1 >= a1

示例测试数据中符合前置条件的唯一id2有2个，预期返回2条记录。此前尝试distinct on(rank() over (order by b - a, a1 - b1, id))的写法未得到正确结果，错误查询语句及测试数据如下：

with data as (
select 1 id, 1 as id2, 150 a, 200 b, 200 a1, 150 b1
union
select 1 id, 2 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 1 id, 3 as id2, 150 a, 100 b, 200 a1, 100 b1
union
select 1 id, 4 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 2 id, 1 as id2, 150 a, 200 b, 200 a1, 150 b1
union
select 2 id, 2 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 2 id, 3 as id2, 150 a, 100 b, 200 a1, 100 b1
union
select 2 id, 4 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 3 id, 1 as id2, 150 a, 200 b, 200 a1, 150 b1
union
select 3 id, 4 as id2, 150 a, 150 b, 200 a1, 200 b1
union
select 3 id, 3 as id2, 150 a, 100 b, 200 a1, 100 b1
union
select 3 id, 2 as id2, 150 a, 150 b, 200 a1, 200 b1
)
select *
from data
where b >= a  and b1 >= a1
order by  id2, b - a, a1 - b1, rank() over (order by id2, id);

正确解决方案 #

使用窗口函数按id2分区，计算每个分组内的记录排名，再筛选排名第一的记录：

WITH data AS (
    SELECT 1 id, 1 AS id2, 150 a, 200 b, 200 a1, 150 b1
    UNION
    SELECT 1 id, 2 AS id2, 150 a, 150 b, 200 a1, 200 b1
    UNION
    SELECT 1 id, 3 AS id2, 150 a, 100 b, 200 a1, 100 b1
    UNION
    SELECT 1 id, 4 AS id2, 150 a, 150 b, 200 a1, 200 b1
    UNION
    SELECT 2 id, 1 AS id2, 150 a, 200 b, 200 a1, 150 b1
    UNION
    SELECT 2 id, 2 AS id2, 150 a, 150 b, 200 a1, 200 b1
    UNION
    SELECT 2 id, 3 AS id2, 150 a, 100 b, 200 a1, 100 b1
    UNION
    SELECT 2 id, 4 AS id2, 150 a, 150 b, 200 a1, 200 b1
    UNION
    SELECT 3 id, 1 AS id2, 150 a, 200 b, 200 a1, 150 b1
    UNION
    SELECT 3 id, 4 AS id2, 150 a, 150 b, 200 a1, 200 b1
    UNION
    SELECT 3 id, 3 AS id2, 150 a, 100 b, 200 a1, 100 b1
    UNION
    SELECT 3 id, 2 AS id2, 150 a, 150 b, 200 a1, 200 b1
), ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY id2 ORDER BY (b - a), (a1 - b1), id) AS rn
    FROM data
    WHERE b >= a AND b1 >= a1
)
SELECT id, id2, a, b, a1, b1
FROM ranked_data
WHERE rn = 1;

方案说明 #

1. 分区逻辑：通过PARTITION BY id2将数据按id2分组，确保每个id2单独计算最优记录
2. 排序规则：ORDER BY (b - a), (a1 - b1), id优先按b-a的最小差值排序，再按a1-b1的最小差值排序，最后用id处理同分情况
3. 筛选逻辑：ROW_NUMBER()为每个分组内的记录分配唯一序号，取rn=1即可得到每个id2下符合要求的唯一记录

错误原因分析 #

之前的写法错误在于：

• DISTINCT ON的参数使用不当，它需要指定分组字段而非窗口函数计算结果
• 窗口函数未按id2分区，导致无法针对每个id2单独筛选最小差值记录

内容的提问来源于stack exchange，提问作者Павел