如何在Snowflake中删除表重复行并保留一行？代码异常求助

问题分析 #

你的DELETE语句存在两个核心问题：

• 语法错误：WHERE id , in里多余的逗号，导致语句无法正确解析
• 逻辑错误：仅通过id匹配要删除的行，会把所有拥有重复id的行全部删除（因为子查询返回的是重复的id值，所有该id的行都会被命中DELETE条件），而非仅删除重复项中多余的行。

此外子查询中选取的SK_DIM_CHANNEL未被实际使用，且缺少对单一行的唯一标识，无法精准区分要保留和删除的行。

正确解法 #

方法1：用CTE标记重复行，精准删除 #

通过ROW_NUMBER()给每组重复行标记序号，仅删除序号大于1的行：

WITH duplicate_rows AS (
    SELECT 
        id,
        name,
        ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id) AS rn
    FROM int_ga.DIM_table
)
DELETE FROM int_ga.DIM_table
USING duplicate_rows
WHERE int_ga.DIM_table.id = duplicate_rows.id
  AND int_ga.DIM_table.name = duplicate_rows.name
  AND duplicate_rows.rn > 1;

如果表有唯一主键（比如SK_DIM_CHANNEL），用主键关联会更精准，避免id+name仍存在重复的情况：

WITH duplicate_rows AS (
    SELECT 
        SK_DIM_CHANNEL,
        ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id) AS rn
    FROM int_ga.DIM_table
)
DELETE FROM int_ga.DIM_table
WHERE SK_DIM_CHANNEL IN (SELECT SK_DIM_CHANNEL FROM duplicate_rows WHERE rn > 1);

方法2：利用Snowflake的QUALIFY简化删除 #

Snowflake支持在DELETE中直接结合QUALIFY子句，写法更简洁：

DELETE FROM int_ga.DIM_table
WHERE SK_DIM_CHANNEL IN (
    SELECT SK_DIM_CHANNEL
    FROM int_ga.DIM_table
    QUALIFY ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id) > 1
);

方法3：重建表（大数据量场景更高效） #

如果表数据量较大，重建表的方式通常比逐行删除性能更优：

CREATE OR REPLACE TABLE int_ga.DIM_table AS
SELECT DISTINCT id, name
FROM int_ga.DIM_table;

若表包含其他字段，保留原结构的写法：

CREATE OR REPLACE TABLE int_ga.DIM_table AS
SELECT *
FROM int_ga.DIM_table
QUALIFY ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id) = 1;

验证结果 #

执行上述任一方法后，表会保留每组id+name的唯一一行，得到期望结果：

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