sql - SQL DELETE - 仅保留组中的最后 n 条记录（删除组中的旧记录）

Question

[编辑]：这个答案帮助DELETE FROM 表 WHERE 'RANK' > 1

我有一个表可以跟踪某些实体的状态历史记录（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, -- this is an enumeration
    -- 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)
);

这是一个例子

status_id	ref_entity_aa	ref_entity_ab	地位
1	“一个”	1	好的
1	“一个”	2	好的
1	“乙”	1	好的
1	“乙”	2	好的
2	“一个”	1	好的
2	“一个”	2	好的
2	“乙”	1	好的
2	“乙”	2	错误

我想为每个状态的每个主键保留最后 n 条记录

所以如果删除后 n 为 1，则表应如下所示

status_id	ref_entity_aa	ref_entity_ab	地位
1	“乙”	2	好的
2	“一个”	1	好的
2	“一个”	2	好的
2	“乙”	1	好的
2	“乙”	2	错误

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

此 group by 查询返回每个组的行数

在示例中，这将是

ref_entity_aa	ref_entity_ab	地位	数数
“一个”	1	好的	2
“一个”	2	好的	2
“乙”	1	好的	2
“乙”	2	好的	1
“乙”	2	错误	1

删除查询执行 group by 查询后应该返回

ref_entity_aa	ref_entity_ab	地位	数数
“一个”	1	好的	1
“一个”	2	好的	1
“乙”	1	好的	1
“乙”	2	好的	1
“乙”	2	错误	1

目标是删除旧记录（即 status_id 值最低的记录），以便此 group by 查询返回计数 <= n

我能够提出这个查询

delete from tb_status as ts
where (ts.ref_entity_aa || ts.ref_entity_ab || ts.status_id) in ( -- the same constructed_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 -- the n in the question
);

标签： sqlpostgresql

Answer 1

我使用 rank() 窗口函数来确定哪个记录较旧（按降序排列）。然后我使用子查询来隔离标识要删除的记录的主键。此主键数组可用于 WHERE 子句中，以根据子查询中定义的主键删除记录。

  IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
        DROP TABLE #TEMP
    
    CREATE TABLE #TEMP (
        PrimKey             INT
        ,status_id          INT
        ,ref_entity_aa      NVARCHAR(10)
        ,ref_entity_ab      INT
        ,[status]           NVARCHAR(10)
    );
    
    INSERT INTO #TEMP (PrimKey,status_id,ref_entity_aa,ref_entity_ab,[status])
    VALUES (1000,1,'a',1,'OK')
        ,(1001,1,'a',2,'OK')
        ,(1002,1,'b',1,'OK')
        ,(1003,1,'b',2,'OK')
        ,(1004,2,'a',1,'OK')
        ,(1005,2,'a',2,'OK')
        ,(1006,2,'b',1,'OK')
        ,(1007,2,'b',2,'ERROR')
    
    SELECT * FROM #TEMP
    
    DELETE #TEMP WHERE Primkey NOT IN (
    SELECT Primkey 
    FROM(
    SELECT PrimKey
        ,status_id
        ,RANK() OVER(PARTITION BY ref_entity_aa,ref_entity_ab,[status] ORDER BY status_id DESC) [rank]
        ,ref_entity_aa
        ,ref_entity_ab
        ,[status]
    FROM #TEMP
    )A
    WHERE [rank] <= 1 --'N'
    )
    
    SELECT * FROM #TEMP

让我知道这个是否奏效！

删除前后的输出：

或者，如果您没有主键，请使用此解决方案。您可以使用复合键列计算一个并执行与上述相同的想法。请记住，当涉及 NVARCHAR 列时，需要将数值数据类型转换为 nvarchar() 。

    SELECT --Before Delete
        CAST(T.[status_id] AS NVARCHAR(1))+T.ref_entity_aa+CAST(T.ref_entity_ab AS NVARCHAR(1))+T.[status]
        ,* FROM #TEMP T
    
    DELETE #TEMP WHERE CAST([status_id] AS NVARCHAR(1))+ref_entity_aa+CAST(ref_entity_ab AS NVARCHAR(1))+[status] NOT IN (
    SELECT CAST([status_id] AS NVARCHAR(1))+ref_entity_aa+CAST(ref_entity_ab AS NVARCHAR(1))+[status] 
    FROM(
    SELECT status_id
        ,RANK() OVER(PARTITION BY ref_entity_aa,ref_entity_ab,[status] ORDER BY status_id DESC) [rank]
        ,ref_entity_aa
        ,ref_entity_ab
        ,[status]
    FROM #TEMP
    )A
    WHERE [rank] <= 1 --'N'
    )
    
SELECT --After Delete
    CAST([status_id] AS NVARCHAR(1))+ref_entity_aa+CAST(ref_entity_ab AS NVARCHAR(1))+[status]
    ,T.ref_entity_aa
    ,T.ref_entity_ab
    ,T.[status]
FROM #TEMP t

带有连接键的输出：（之前和之后）