PostgreSQL中按权重排序并截取至指定element_id的查询实现

数据准备 #

执行以下SQL插入测试数据：

INSERT INTO "public"."catalog_element" 
("id", "catalogue_id", "element_id", "weight") VALUES
(1,100,1,0),
(2,100,2,1),
(3,100,3,2),
(4,10,1,0),
(5,10,5,0),
(6,10,6,1),
(7,100,7,1);

表结构与原始数据 #

这是一个PostgreSQL的catalog *- to -* element多对多关联表，包含权重字段，原始数据如下：

查询需求 #

编写一条查询语句，获取指定catalog_id的记录，按weight排序，返回从第一条到包含指定element_id的所有记录。

示例场景 #

当catalog_id = 100，按weight降序排序，返回所有直到element_id = 7的记录时，预期结果如下：

解决方案 #

可以通过窗口函数先确定目标元素在排序后的位置，再筛选出位置小于等于该位置的记录：

WITH ranked_elements AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY catalogue_id ORDER BY weight DESC) AS rn
    FROM catalog_element
    WHERE catalogue_id = 100
),
target_rn AS (
    SELECT rn FROM ranked_elements WHERE element_id = 7
)
SELECT id, catalogue_id, element_id, weight
FROM ranked_elements
WHERE rn <= (SELECT rn FROM target_rn)
ORDER BY rn;

也可以使用更简洁的子查询写法：

SELECT ce.id, ce.catalogue_id, ce.element_id, ce.weight
FROM catalog_element ce
WHERE ce.catalogue_id = 100
AND ROW_NUMBER() OVER (PARTITION BY ce.catalogue_id ORDER BY ce.weight DESC) 
    <= (
        SELECT ROW_NUMBER() OVER (PARTITION BY catalogue_id ORDER BY weight DESC)
        FROM catalog_element
        WHERE catalogue_id = 100 AND element_id = 7
    )
ORDER BY ce.weight DESC;

说明：如果存在相同权重的记录，ROW_NUMBER()会给每条记录分配唯一序号；若需将相同权重的记录视为同一组，可替换为RANK()或DENSE_RANK()，具体根据业务需求选择。

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