PostgreSQL合并除Consoles外相同行及去重等技术问题咨询

问题背景 #

我有一个视频游戏数据表agames，包含6个TEXT类型字段：Game_Title、Consoles、ESRB_Rating、Content_Descriptors、Interactive_Elements、Content_Summary。表中存在大量除Consoles外其他字段均相同的行，部分行的Content_Summary仅差1个字符，还有完全重复的行。

此前尝试执行以下查询，但因Content_Summary的细微差异未达预期：

SELECT Game_Title, string_agg(Consoles, ', ') AS Consoles, ESRB_Rating, Content_Descriptors, Interactive_Elements, Content_Summary
FROM  agames
GROUP BY Game_Title, ESRB_Rating, Content_Descriptors, Interactive_Elements, Content_Summary;

需要解决三个问题：

1. 如何合并Game_Title、ESRB_Rating、Content_Descriptors、Interactive_Elements完全相同的行的Consoles字段？
2. 合并后的行如何获取Content_Summary（两行摘要几乎一致，取任意即可）？
3. 如何删除完全重复的行？

解决方案 #

问题1&2：合并指定字段相同的行并聚合Consoles #

因为Content_Summary存在细微差异，分组时排除该字段，用聚合函数任意选取一个摘要值即可。推荐给string_agg加上DISTINCT避免重复的主机名：

SELECT 
    Game_Title,
    string_agg(DISTINCT Consoles, ', ') AS Consoles,
    ESRB_Rating,
    Content_Descriptors,
    Interactive_Elements,
    MAX(Content_Summary) AS Content_Summary -- 用MIN()也能实现任意取一个摘要
FROM agames
GROUP BY Game_Title, ESRB_Rating, Content_Descriptors, Interactive_Elements;

如果要保留原始数据，可将此查询结果存入新表；若需直接更新原表，可结合CTE逻辑调整（具体视业务需求而定）。

问题3：删除完全重复的行 #

完全重复指所有字段值完全一致的行，可通过窗口函数标记重复行后删除多余项：

WITH duplicate_rows AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Game_Title, Consoles, ESRB_Rating, Content_Descriptors, Interactive_Elements, Content_Summary
            ORDER BY (SELECT NULL) -- 无需特定排序，保留任意一行即可
        ) AS row_num
    FROM agames
)
DELETE FROM duplicate_rows WHERE row_num > 1;

如果使用PostgreSQL，还可以用更简洁的写法（利用ctid标识物理行）：

DELETE FROM agames a
USING agames b
WHERE a.ctid > b.ctid -- 保留物理位置靠前的行
AND a.Game_Title = b.Game_Title
AND a.Consoles = b.Consoles
AND a.ESRB_Rating = b.ESRB_Rating
AND a.Content_Descriptors = b.Content_Descriptors
AND a.Interactive_Elements = b.Interactive_Elements
AND a.Content_Summary = b.Content_Summary;

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