sql - 删除 Postgres 中的重复项

Question

使用 Postgres 9.5，我有一个表 properties:

CREATE TABLE properties (
    id serial PRIMARY KEY,
    property_id integer,
    state character(2),
    record_type character(1),
    ...
);

• id 是我唯一的内部 ID。
• property_id 来自第 3 方。来自不同州的属性可能共享相同的 property_id，但每个州只有一个 property_id。原因是，properties 表包含所有状态而不是每个表一个状态，property_id 计数器从每个状态的 1 开始。
• state 是美国各州的缩写(例如 MA、CA、NY)。当与 property_id 连接时，它引用一个属性，例如12345NY。
• record_type 可以是 A(添加)、C(更改)或 D(删除)。

当新属性添加到表中时，它们的 record_type 为 A。随着时间的推移，属性的详细信息发生变化，并且有新行添加到表中，其 C 作为它们的 record_type。

例子:

id,   property_id, state, record_type, ...
7353, 6001,        'MA',  'A',         ...
7354, 6001,        'MA',  'C',         ...
7355, 6001,        'MA',  'C',         ...

问题在于:我只想保留属性的最新行(无论 record_type 是什么)并删除所有旧行。所以在这个例子中，只保留最后一行。没有日期列，但我们可以假设 id 越高，记录越新。作为旁注，所有具有 D 记录类型的行之前已被删除，因此我们只处理 add 和 change 记录类型。

Answer 1

WITH CTE AS
  (SELECT *,ROW_NUMBER() OVER(PARTITION by property_id,state
                              ORDER BY id DESC) AS rn
   FROM properties)
DELETE
FROM properties WHERE id IN (SELECT id FROM CTE WHERE rn >1)