PostgreSQL多列识别重复数据并按type字段删除指定行

需求 #

基于id、datetimestamp、status1、status2四列识别重复项，当同一分组下同时存在type='AR'和type='AN'的行时，删除type='AR'的行，保留type='AN'的行。

样本数据 #

期望输出 #

原查询问题 #

原查询语句返回的是type='AN'的行，而非需要删除的type='AR'行，语句如下：

select * from test_data e
where exists
 ( select * from test_data e2 
   where e.datetimestamp=e2.datetimestamp and e.id=e2.id 
     and e.status1=e2.status1 
     and e.status2=e2.status2 
     and e.type='AN' and e2.type='AR') order by e.datetimestamp asc;

问题原因：原查询将e.type='AN'作为外层筛选条件，导致仅返回符合条件的AN行，而非需要删除的AR行。

解决方案 #

1. 先查询需要删除的AR行（验证用） #

SELECT *
FROM test_data e
WHERE e.type = 'AR'
AND EXISTS (
    SELECT 1
    FROM test_data e2
    WHERE e.id = e2.id
      AND e.datetimestamp = e2.datetimestamp
      AND e.status1 = e2.status1
      AND e.status2 = e2.status2
      AND e2.type = 'AN'
)
ORDER BY e.datetimestamp ASC;

2. 删除指定AR行 #

方式一：使用EXISTS子句

DELETE FROM test_data e
WHERE e.type = 'AR'
AND EXISTS (
    SELECT 1
    FROM test_data e2
    WHERE e.id = e2.id
      AND e.datetimestamp = e2.datetimestamp
      AND e.status1 = e2.status1
      AND e.status2 = e2.status2
      AND e2.type = 'AN'
);

方式二：使用USING子句（PostgreSQL专属）

DELETE FROM test_data e
USING test_data e2
WHERE e.type = 'AR'
  AND e2.type = 'AN'
  AND e.id = e2.id
  AND e.datetimestamp = e2.datetimestamp
  AND e.status1 = e2.status1
  AND e.status2 = e2.status2;

建表及插入数据SQL #

CREATE TABLE test_data (
 id character varying(2) NOT NULL,
 type character varying(2),
 cycle integer,
 datetimestamp timestamp without time zone NOT NULL,
 status1 character varying(10),
 status2 character varying(10),
 PRIMARY KEY(id, cycle, datetimestamp)
);

INSERT INTO test_data VALUES
  (27, 'AN', 123, '2022-12-28 04:12:31', 'Normal A', 'Normal A')
, (27, 'AR', 124, '2022-12-28 04:12:31', 'Normal A', 'Normal A')
, (19, 'AN', 125, '2022-12-28 05:24:30', 'Normal A', 'Normal A')
, (19, 'AR', 126, '2022-12-28 06:18:20', 'Normal A', 'Normal A')
, (19, 'AR', 234, '2022-12-28 07:22:20', 'Normal A', 'Normal A')
, (19, 'AN', 235, '2022-12-28 07:22:20', 'Normal A', 'Normal A')
, (20, 'AR', 236, '2022-12-28 08:25:49', 'Normal A', 'Normal A')
, (20, 'AN', 237, '2022-12-28 08:25:49', 'Normal A', 'Normal A')
, (19, 'AR', 129, '2022-12-28 09:08:19', 'Normal A', 'Normal A')
, (19, 'AN', 127, '2022-12-28 09:08:19', 'Normal A', 'Normal A')
, (19, 'AR', 238, '2022-12-28 10:04:31', 'Normal A', 'Normal A')
, (19, 'AN', 230, '2022-12-28 10:04:31', 'Normal A', 'Normal A')
, (22, 'AN', 239, '2022-12-28 11:04:58', 'Normal A', 'Normal A')
, (22, 'AR', 256, '2022-12-28 11:04:58', 'Normal A', 'Normal A')
;

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