snowflake-cloud-data-platform - 如何删除雪花数据库表中的重复记录

Question

如何从雪花表中删除重复记录。谢谢

ID Name
1  Apple
1  Apple
2  Apple
3  Orange
3  Orange

结果应该是:

ID Name
1  Apple
2  Apple
3  Orange

Answer 1

如果您有这样的主键:

CREATE TABLE fruit (key number, id number, name text);

insert into fruit values (1,1, 'Apple'), (2,1,'Apple'),
      (3,2, 'Apple'), (4,3, 'Orange'), (5,3, 'Orange');

和那时一样

DELETE FROM fruit
WHERE key in (
  SELECT key 
  FROM (
      SELECT key
          ,ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY key) AS rn
      FROM fruit
  )
  WHERE rn > 1
);

但是，如果您没有唯一键，则无法以这种方式删除。此时一个

CREATE TABLE new_table_name AS
SELECT id, name FROM (
    SELECT id
        ,name
        ,ROW_NUMBER() OVER (PARTITION BY id, name) AS rn
    FROM table_name
)
WHERE rn > 1

然后 swap他们

ALTER TABLE table_name SWAP WITH new_table_name