SQL DELETE技巧：按分组保留最新n条记录，删除分组内历史旧记录

我有一张用于存储实体状态历史的表 tb_status，相关表结构DDL如下：

CREATE TABLE table_aa (
    id varchar(255) NOT NULL,
    -- other columns
    CONSTRAINT table_aa_pkey PRIMARY KEY (id)
);

CREATE TABLE table_ab (
    id           int4 NOT NULL,
    ref_entity_a varchar(255) NOT NULL,
    -- other columns
    CONSTRAINT table_ab_pkey PRIMARY KEY (id, ref_entity_a),
    CONSTRAINT fk_entity_a   FOREIGN KEY (ref_entity_a) REFERENCES table_aa(id)
);

CREATE TABLE tb_status (
    status_id        int8         NOT NULL,
    ref_entity_aa    varchar(255) NOT NULL,
    ref_entity_ab    int4         NOT NULL,
    insert_timestamp timestamptz  NOT NULL,
    status           varchar(255)     NULL, -- 枚举类型字段
    -- other columns
    CONSTRAINT estatus_pkey PRIMARY KEY (ref_entity_aa, ref_entity_ab, status_id),
    CONSTRAINT fk_entity_aa FOREIGN KEY (ref_entity_aa)                REFERENCES table_aa(id),
    CONSTRAINT fk_entity_ab FOREIGN KEY (ref_entity_aa, ref_entity_ab) REFERENCES table_ab(ref_entity_a,id)
);

示例数据 #

需求说明 #

需要按 (ref_entity_aa, ref_entity_ab, status) 字段分组，每组仅保留最新的n条记录，旧记录判定标准为status_id值更小。以n=1为例，删除后tb_status的剩余数据如下：

分组统计验证 #

我们可以通过以下SQL查询每个分组的行数：

select ref_entity_aa, ref_entity_ab, status, count(*)
from tb_status
group by ref_entity_aa, ref_entity_ab, status;

原数据的分组统计结果：

删除操作执行后，分组统计预期结果：

已实现的删除语句 #

delete from tb_status as ts
where (ts.ref_entity_aa || ts.ref_entity_ab || ts.status_id) in ( -- 匹配构造的唯一ID
    select ranked_query.constructed_id
    from (
        select (ts.ref_entity_aa || ts.ref_entity_ab || ts.status_id) as constructed_id,
               rank() over (partition by ts.ref_entity_aa, ts.ref_entity_ab, ts.status order by ts.status_id desc) as ranking
        from tb_status as ts
    ) as ranked_query
    where ranked_query.ranking > :numberOfRecordsToKeep -- 对应需求中的n值
);

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