如何编写SQL DELETE语句保留分组内最新记录及各月首条记录？

当然可行！我们可以借助PostgreSQL的窗口函数精准筛选出需要保留的记录，然后一次性删除其余数据。针对你的需求（保留分组内最新一条记录 + 每个月份的第一条记录），具体实现如下：

核心思路 #

通过两个窗口函数分别标记两类需要保留的记录：

• 标记每个other_table_id分组中最新的一条记录（按日期倒序排序后取行号为1的条目）
• 标记每个分组内每个月份的第一条记录（按年份-月份分区，日期正序排序后取行号为1的条目）

然后将这两类记录的ID集合作为保留列表，删除不在该列表中的数据即可。

具体SQL语句 #

假设你的表名为test_table（可根据实际表名修改），主键为id，日期字段为date_column，执行以下语句即可完成需求：

DELETE FROM test_table
WHERE id NOT IN (
    SELECT id
    FROM (
        SELECT 
            id,
            other_table_id,
            date_column,
            -- 标记分组内最新记录
            ROW_NUMBER() OVER (PARTITION BY other_table_id ORDER BY date_column DESC) AS rn_latest,
            -- 标记分组内每月第一条记录
            ROW_NUMBER() OVER (PARTITION BY other_table_id, DATE_TRUNC('month', date_column) ORDER BY date_column ASC) AS rn_month_first
        FROM test_table
        WHERE other_table_id = 1 -- 仅针对分组1处理，若需全部分组可移除该条件
    ) filtered_records
    WHERE rn_latest = 1 OR rn_month_first = 1
);

语句解释 #

1. 内层子查询：通过两个ROW_NUMBER()窗口函数对数据进行标记：
   • rn_latest：按other_table_id分组，日期从新到旧排序，最新的记录行号为1
   • rn_month_first：按other_table_id+月份分组，日期从旧到新排序，每月第一条记录行号为1
2. 筛选保留记录：取出rn_latest=1（最新记录）或rn_month_first=1（每月第一条）的所有ID
3. 执行删除：删除不在保留ID列表中的数据，仅保留符合要求的记录

验证你的示例 #

针对other_table_id=1的分组，执行后会保留：

• 最新记录：('2017-12-14', 1)
• 各月第一条记录：('2017-01-02', 1)、('2017-03-24', 1)、('2017-04-03', 1)、('2017-05-24', 1)
  完全符合你预期的结果。

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