PostgreSQL数据清理：按agent_id保留最新100条记录并删除其余

你的问题在于原语句是全局选取最新100条记录，没有针对每个agent_id单独处理。要实现按每个agent_id保留最新100条的需求，可以借助窗口函数ROW_NUMBER()来分组排序，具体方案如下：

方法1：使用CTE（适用于PostgreSQL、MySQL 8.0+等支持窗口函数的数据库） #

WITH ranked_logs AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY agent_id ORDER BY at DESC) AS row_num
    FROM agent_audit_log
)
DELETE FROM agent_audit_log
WHERE id IN (
    SELECT id FROM ranked_logs WHERE row_num > 100
);

方法2：子查询方式（兼容更多数据库） #

DELETE FROM agent_audit_log
WHERE id IN (
    SELECT id
    FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY agent_id ORDER BY at DESC) AS row_num
        FROM agent_audit_log
    ) AS ranked
    WHERE row_num > 100
);

逻辑说明 #

1. PARTITION BY agent_id：将数据按agent_id分组，每个组单独处理
2. ORDER BY at DESC：在每个分组内，按时间戳at倒序排列，最新的记录排在最前面
3. ROW_NUMBER()：为每个分组内的记录分配序号，最新的记录序号为1，次新为2，以此类推
4. 最终删除序号大于100的记录，即每个agent_id只保留序号1-100的最新100条日志

注意事项 #

• 如果at字段存在相同时间戳的记录，ROW_NUMBER()会随机分配序号；如果需要稳定排序，可以在ORDER BY中加入id DESC，即ORDER BY at DESC, id DESC，确保相同时间下id大的记录优先保留
• 执行删除前建议先运行子查询查看要删除的记录，确认逻辑正确：

  SELECT *
  FROM (
      SELECT 
          *,
          ROW_NUMBER() OVER (PARTITION BY agent_id ORDER BY at DESC) AS row_num
      FROM agent_audit_log
  ) AS ranked
  WHERE row_num > 100;
  

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