Redshift存在重复项时的Upsert优化方案咨询

你碰到的这个问题很常见——当CSV里存在相同post_id但其他字段不同的记录时，SELECT DISTINCT *只会过滤完全一模一样的行，根本没法针对post_id做去重，所以最终插入后posts表还是会出现重复的post_id。

我给你两种修改方案，核心思路都是先对临时表的post_id做去重，确保每个post_id只保留一条符合你业务规则的记录，再执行UPSERT操作：

方案一：先创建去重后的临时表（更直观） #

BEGIN;
CREATE TABLE posts_staging (LIKE posts);
COPY posts_staging (post_id,user_id,timestamp,votes,comments) FROM 's3://posts' CREDENTIALS 'aws_access_key_id=xxxx;aws_secret_access_key=yyyy' CSV;

-- 对临时表按post_id去重，这里示例保留timestamp最新的那条记录
CREATE TABLE posts_staging_deduped AS
SELECT post_id, user_id, timestamp, votes, comments
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY timestamp DESC) AS rn
    FROM posts_staging
) t
WHERE rn = 1;

DROP TABLE posts_staging; -- 清理原始临时表

-- 执行标准UPSERT逻辑
DELETE FROM posts USING posts_staging_deduped WHERE posts.post_id = posts_staging_deduped.post_id;
INSERT INTO posts SELECT * FROM posts_staging_deduped;

DROP TABLE posts_staging_deduped;
END;

方案二：直接在INSERT时去重（更简洁） #

如果不想多建一张临时表，可以把去重逻辑直接写到INSERT的子查询里：

BEGIN;
CREATE TABLE posts_staging (LIKE posts);
COPY posts_staging (post_id,user_id,timestamp,votes,comments) FROM 's3://posts' CREDENTIALS 'aws_access_key_id=xxxx;aws_secret_access_key=yyyy' CSV;

DELETE FROM posts USING posts_staging WHERE posts.post_id = posts_staging.post_id;

-- 插入时直接完成去重，每个post_id仅保留一条
INSERT INTO posts
SELECT post_id, user_id, timestamp, votes, comments
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY timestamp DESC) AS rn
    FROM posts_staging
) t
WHERE rn = 1;

DROP TABLE posts_staging;
END;

关键说明 #

• 业务规则调整：上面的示例用ORDER BY timestamp DESC来保留最新的帖子记录，你可以根据自己的需求修改排序规则——比如想保留投票数最高的，就改成ORDER BY votes DESC；如果没有特殊优先级，也可以用ORDER BY 1随便取一条。
• 为什么用ROW_NUMBER？ 它能精准地对每个post_id分组，然后给组内的每条记录编号，我们只取编号为1的那条，就能保证每个post_id唯一。相比DISTINCT，它能灵活处理“同ID不同字段”的去重场景。

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