PostgreSQL 如何通过聚合signature值查找screenshots重复数据

你原有查询存在两个问题：多余的空WHERE子句，且array_agg未排序，相同signature集合如果元素顺序不同会被判定为不同数组，无法正确匹配重复项。修正后可直接输出重复组结果：

WITH screenshot_signature_sets AS (
    SELECT 
        os.id,
        os."originalFilename",
        os."createdAt",
        -- 排序+过滤空值，保证相同signature集合生成的数组完全一致
        array_agg(s.signature ORDER BY s.signature) FILTER (WHERE s.signature IS NOT NULL) AS signature_set
    FROM "development".screenshots os
    LEFT JOIN "development"."screenshotSignatures" s ON os.id = s."screenshotId"
    GROUP BY os.id, os."originalFilename", os."createdAt"
),
duplicate_groups AS (
    SELECT
        signature_set,
        -- 按创建时间升序排列重复ID，第一个即为最早创建的截图
        array_agg(id ORDER BY "createdAt") AS duplicate_screenshot_ids,
        array_agg("originalFilename" ORDER BY "createdAt") AS duplicate_filenames,
        COUNT(*) AS duplicate_count
    FROM screenshot_signature_sets
    GROUP BY signature_set
    -- 只保留存在重复的分组
    HAVING COUNT(*) > 1
)
SELECT 
    duplicate_screenshot_ids,
    duplicate_filenames,
    duplicate_count,
    signature_set
FROM duplicate_groups;

查询结果中每一行对应一组重复截图，duplicate_screenshot_ids字段就是所有拥有完全相同signature集合的截图ID，可直接匹配你需要的「screenshot a、b、c拥有相同signature集合」的输出要求。

执行删除前请先运行SELECT版本验证待删除ID，避免误删数据：

WITH screenshot_signature_sets AS (
    SELECT 
        os.id,
        os."createdAt",
        array_agg(s.signature ORDER BY s.signature) FILTER (WHERE s.signature IS NOT NULL) AS signature_set
    FROM "development".screenshots os
    LEFT JOIN "development"."screenshotSignatures" s ON os.id = s."screenshotId"
    GROUP BY os.id, os."createdAt"
),
ranked_screenshots AS (
    SELECT
        id,
        -- 按创建时间升序排名，最早创建的截图排名为1
        ROW_NUMBER() OVER (PARTITION BY signature_set ORDER BY "createdAt" ASC) AS rn
    FROM screenshot_signature_sets
)
-- 如需验证待删除数据，将下方DELETE语句替换为 SELECT id FROM ranked_screenshots WHERE rn = 1 即可
DELETE FROM "development".screenshots
WHERE id IN (
    SELECT id FROM ranked_screenshots WHERE rn = 1
    -- 如果需要每个重复组只保留1条最新截图、删除其余所有重复，把 rn = 1 改成 rn > 1 即可
);

如果screenshotSignatures表未设置screenshotId外键的级联删除规则，需要先删除对应screenshotId的signature记录，再执行上述删除screenshot的操作。

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