如何删除Redshift无主键表中的完全重复行？

问题分析 #

你之前的尝试失败核心原因有两点：

1. 窗口函数（row_number()/rank()）不能直接在子查询的WHERE子句中使用，必须嵌套子查询先计算行号；
2. IN子句仅匹配id列，无法区分重复行中的具体条目，导致要么全删要么全留。

可行解决方案 #

方案1：临时表去重（推荐，性能更优）

Redshift作为列存数据仓库，DELETE操作会产生大量删除标记，影响后续查询性能。更高效的方式是通过临时表存储去重后的数据，再替换原表：

-- 1. 创建临时表，存储去重后的唯一行
CREATE TEMP TABLE temp_test_table AS
SELECT *
FROM (
    SELECT *,
           -- 按所有列分区，确保完全重复的行被分组
           ROW_NUMBER() OVER (PARTITION BY id, "Col 1", "Col 2" ORDER BY (SELECT NULL)) AS rownumber
    FROM test_table
) t
WHERE rownumber = 1; -- 只保留每组的第一行

-- 2. 清空原表（需保留原表结构时使用）
TRUNCATE TABLE test_table;

-- 3. 将去重后的数据插入原表
INSERT INTO test_table (id, "Col 1", "Col 2")
SELECT id, "Col 1", "Col 2"
FROM temp_test_table;

注：ORDER BY (SELECT NULL)是因为Redshift要求窗口函数必须包含ORDER BY子句，若无需特定排序顺序，用此语法即可。

方案2：直接DELETE（仅适用于小表）

如果必须用DELETE操作，可通过CTE结合USING子句关联去重后的行，精准删除重复条目：

WITH duplicate_rows AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id, "Col 1", "Col 2" ORDER BY (SELECT NULL)) AS rownumber
    FROM test_table
)
DELETE FROM test_table
USING duplicate_rows
-- 匹配所有列，确保定位到具体重复行
WHERE test_table.id = duplicate_rows.id
  AND test_table."Col 1" = duplicate_rows."Col 1"
  AND test_table."Col 2" = duplicate_rows."Col 2"
  AND duplicate_rows.rownumber > 1; -- 删除每组中除第一行外的重复行

对之前尝试的修正说明 #

• 尝试1：子查询中直接在WHERE使用row_number()违反语法规则，且IN仅匹配id，导致所有id=1的行被删除；
• 尝试2：子查询WHERE子句中错误使用row_number（应为定义的别名rownumber），同时IN仅匹配id，无法筛选出需要删除的重复行。

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