从不工作的表中删除重复的工作记录

Question

我正在尝试从Redshift数据库中删除重复项

WITH CTE AS
(SELECT org_country_code,dest_country_code,postcode,
zone,kg,value,carrier,version,
ROW_NUMBER() OVER (
partition BY org_country_code, dest_country_code, postcode,
zone, kg, value,carrier, version
ORDER BY org_country_code, dest_country_code,
postcode, zone, kg, value,carrier, version) rnum
FROM dhl_gb.dhl_gb_td_exp_ww_l10kg_transformed
) DELETE FROM CTE WHERE rnum<>1

这是怎么回事？有什么需要帮忙的吗？

Answer 1

UPDATE这在Redshift中不起作用。

如果没有PK，您将需要使用ctid:

WITH CTE AS (
SELECT CTID as rid,
ROW_NUMBER() OVER (
partition BY org_country_code, dest_country_code, postcode,
zone, kg, value,carrier, version
) rnum
FROM dhl_gb.dhl_gb_td_exp_ww_l10kg_transformed
) 
DELETE FROM dhl_gb.dhl_gb_td_exp_ww_l10kg_transformed
WHERE CTID in (select rid from CTE where rnum > 1);

Answer 2

不确定是否可以修改CTE。这个怎么样？：

WITH CTE AS
(SELECT org_country_code,dest_country_code,postcode,
zone,kg,value,carrier,version,
ROW_NUMBER() OVER (
partition BY org_country_code, dest_country_code, postcode,
zone, kg, value,carrier, version
ORDER BY org_country_code, dest_country_code,
postcode, zone, kg, value,carrier, version) rnum
FROM dhl_gb.dhl_gb_td_exp_ww_l10kg_transformed
), 
CTE2 AS SELECT * FROM CTE WHERE rnum = 1
SELECT * FROM CTE2