PostgreSQL：按user_id保留指定规则的10行并删除多余行

表结构 #

假设你的表名为your_table，结构如下：

id          int8
user_id     varchar
is_favorite boolean
join_time   timestamptz

需求 #

• 每个user_id最多保留10行数据
• 必须保留该用户所有is_favorite=true的行（已知每个用户这类行不超过5行）
• 剩余名额优先保留join_time最新的非收藏行

解决方案 #

使用窗口函数给每个用户的行排序，筛选出需要保留的记录，删除超出部分：

删除语句 #

DELETE FROM your_table
WHERE id IN (
    SELECT id
    FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (
                PARTITION BY user_id 
                ORDER BY is_favorite DESC, join_time DESC
            ) AS rn
        FROM your_table
    ) t
    WHERE rn > 10
);

逻辑说明 #

1. 内层子查询通过ROW_NUMBER()窗口函数按user_id分区处理：
   • 优先将is_favorite=true的行排在前面（ORDER BY is_favorite DESC）
   • 同一状态下，按join_time倒序排列，最新的行排在前面
   • 给每一行分配唯一行号rn
2. 筛选出行号rn > 10的记录，这些就是需要删除的目标行
3. 外层DELETE语句通过ID匹配删除对应记录

示例验证 #

• 对于user_id=655caab8-ce81-11ed-afa1-0242ac120002：
  3条收藏行+最新7条非收藏行共10条保留，行号11-14的记录被删除，符合预期
• 对于user_id=81c126b6-ce81-11ed-afa1-0242ac120002：
  5条收藏行+最新5条非收藏行共10条保留，行号25、26的记录被删除，和示例结果一致

注意事项 #

• 替换语句中的your_table为实际表名
• 执行删除前，建议先运行以下查询确认要删除的记录：

SELECT id, user_id, rn
FROM (
    SELECT 
        id,
        user_id,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY is_favorite DESC, join_time DESC
        ) AS rn
    FROM your_table
) t
WHERE rn > 10;

• 若表数据量较大，建议在user_id字段创建索引，提升窗口函数的执行效率

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