PostgreSQL中按条件删除表行 每个mainConId仅保留最高相似度CMax条

实现方案 #

我们可以用PostgreSQL窗口函数ROW_NUMBER()对分组内的记录做排名后删除冗余行，具体语句如下：

核心删除语句 #

将语句中的CMax替换为你需要为每个mainConId保留的最大记录条数即可执行：

WITH ranked_relations AS (
    SELECT 
        ctid,
        ROW_NUMBER() OVER (
            PARTITION BY mainConId 
            ORDER BY similarity DESC, relatedConAddedOn DESC
        ) AS rank_num
    FROM content_relations
)
DELETE FROM content_relations
WHERE ctid IN (SELECT ctid FROM ranked_relations WHERE rank_num > CMax);

排序规则说明：同分组内相似度一致时，默认优先保留添加时间最新的关联内容，你可根据业务需求调整ORDER BY后的次排序规则

前置校验语句 #

执行删除前建议先运行如下语句预览待删除的记录，避免误操作：

WITH ranked_relations AS (
    SELECT 
        mainConId,
        relatedConId,
        similarity,
        relatedConAddedOn,
        ROW_NUMBER() OVER (
            PARTITION BY mainConId 
            ORDER BY similarity DESC, relatedConAddedOn DESC
        ) AS rank_num
    FROM content_relations
)
SELECT * FROM ranked_relations WHERE rank_num > CMax;

性能优化建议 #

如果表数据量较大，建议提前建立mainConId + similarity的联合索引，可大幅降低分组排序和删除的执行耗时。

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