PostgreSQL中保留单条实例清理Buildings_national表重复数据的方法

嘿，这个需求太常见了！你说的“反向distinct”其实就是给重复的建筑记录做去重保留唯一实例，刚好PostgreSQL有好几种靠谱的方案，我给你一步步拆解：

⚠️ 重要前置提醒：操作删除数据前一定要先备份表！避免误删无法恢复，备份命令很简单：

CREATE TABLE Buildings_national_backup AS SELECT * FROM Buildings_national;

方法1：窗口函数（最灵活，强烈推荐） #

假设你的建筑唯一标识是building_unique_key（比如专属编号、唯一名称这类能区分不同建筑的字段），每条记录的唯一ID是record_id（比如自增主键）。我们可以用ROW_NUMBER()窗口函数给每个重复组的记录排序，精准删除多余的记录。

步骤1：先验证要删除的记录（别直接删，先核对！） #

SELECT *
FROM (
    SELECT *,
           -- 按建筑唯一标识分组，给每组记录编序号
           ROW_NUMBER() OVER (PARTITION BY building_unique_key ORDER BY record_id) AS rn
    FROM Buildings_national
) sub
WHERE rn > 1; -- 序号>1的就是重复要删的记录

执行完可以检查结果，确认这些确实是你想删除的重复项。

步骤2：确认无误后执行删除 #

DELETE FROM Buildings_national
WHERE record_id IN (
    SELECT record_id
    FROM (
        SELECT record_id,
               ROW_NUMBER() OVER (PARTITION BY building_unique_key ORDER BY record_id) AS rn
        FROM Buildings_national
    ) sub
    WHERE rn > 1
);

这里ORDER BY record_id是保留每组中最早插入的记录，如果你想保留最新插入的，改成ORDER BY record_id DESC就行，灵活性拉满。

方法2：DELETE USING（简洁高效，适合简单场景） #

如果你的表有明确的主键（比如record_id），可以用USING子句关联重复组，直接删除除了最小/最大ID之外的记录。

步骤1：先验证要删除的记录 #

SELECT b2.*
FROM Buildings_national b1
JOIN Buildings_national b2 ON b1.building_unique_key = b2.building_unique_key
WHERE b1.record_id < b2.record_id; -- 找出同组中ID更大的记录（也就是要删除的）

步骤2：执行删除 #

DELETE FROM Buildings_national b2
USING Buildings_national b1
WHERE b1.building_unique_key = b2.building_unique_key
AND b1.record_id < b2.record_id; -- 保留同组中ID最小的记录，改成>就是保留最大的

这个写法比窗口函数更简洁，但只能按主键的大小来决定保留哪条，适合需求简单的场景。

方法3：创建新表（适合超大数据量） #

如果你的表数据量特别大，直接删除可能会拖慢数据库性能，不如创建一个去重后的新表，再替换原表：

步骤1：生成去重后的新表 #

CREATE TABLE Buildings_national_deduplicated AS
SELECT DISTINCT ON (building_unique_key) *
FROM Buildings_national
ORDER BY building_unique_key, record_id; -- 排序规则决定保留哪条，这里是保留ID最小的

DISTINCT ON (column)是PostgreSQL的特有语法，会自动为每个building_unique_key组保留第一条符合排序规则的记录。

步骤2：替换原表（低峰期操作！） #

-- 先给原表改名存底
ALTER TABLE Buildings_national RENAME TO Buildings_national_old;
-- 把新表改成原表的名字
ALTER TABLE Buildings_national_deduplicated RENAME TO Buildings_national;

这个方法避免了大量删除操作的性能开销，而且可以先完全验证新表数据正确后再替换，非常稳妥。

额外小技巧 #

• 如果你不确定哪些字段是建筑的唯一标识，可以先查询重复组的情况：

SELECT building_unique_key, COUNT(*) AS duplicate_count
FROM Buildings_national
GROUP BY building_unique_key
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

这样能清楚看到哪些建筑有重复，以及重复了多少次。

• 如果建筑的唯一标识是多个字段的组合（比如name+address），只需要把PARTITION BY或DISTINCT ON里的字段改成多个即可，比如PARTITION BY name, address。

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