PostgreSQL：按用户与客户维度清理blog_history表重复行

针对你的需求——在blog_history表中，为每个customer_username和created_by组合保留唯一的post_id记录，删除其余重复行，以下是可行的解决方案：

方法一：使用窗口函数+CTID（推荐） #

PostgreSQL中每个行都有唯一的ctid标识，结合窗口函数可以精准标记并删除重复行：

1. 验证重复行（可选） #

执行以下语句查看将要被删除的重复记录：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_username, created_by, post_id 
            ORDER BY created_at ASC -- 按创建时间升序保留最早记录；要保留最新的改为DESC
        ) AS row_num
    FROM blog_history
) duplicate_check
WHERE row_num > 1;

2. 删除重复行 #

执行以下语句删除重复记录，仅保留每组中的第一条（按created_at排序）：

WITH duplicate_rows AS (
    SELECT 
        ctid,
        ROW_NUMBER() OVER (
            PARTITION BY customer_username, created_by, post_id 
            ORDER BY created_at ASC
        ) AS row_num
    FROM blog_history
)
DELETE FROM blog_history
WHERE ctid IN (SELECT ctid FROM duplicate_rows WHERE row_num > 1);

方法二：直接关联删除 #

如果同组内的created_at不会重复，也可以用关联方式删除：

DELETE FROM blog_history bh
USING (
    SELECT 
        customer_username, created_by, post_id, created_at,
        ROW_NUMBER() OVER (
            PARTITION BY customer_username, created_by, post_id 
            ORDER BY created_at ASC
        ) AS row_num
    FROM blog_history
) sub
WHERE bh.customer_username = sub.customer_username
  AND bh.created_by = sub.created_by
  AND bh.post_id = sub.post_id
  AND bh.created_at = sub.created_at
  AND sub.row_num > 1;

预防重复的措施 #

为避免后续再出现重复记录，可给表添加唯一约束：

ALTER TABLE blog_history
ADD CONSTRAINT unique_user_author_post 
UNIQUE (customer_username, created_by, post_id);

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