Postgres v12如何获取重复分组最新记录并排除无重复条目

报错原因说明 #

你之前触发的must appear in the GROUP BY clause or be used in an aggregate function报错，是因为PostgreSQL默认开启SQL标准的ONLY_FULL_GROUP_BY校验：使用GROUP BY时，SELECT列表中的非聚合字段必须全部包含在GROUP BY子句中。网上很多旧MySQL教程里直接SELECT id, request_id, MAX(created_at) FROM table GROUP BY request_id的写法，依赖旧版MySQL非标准的语法放宽，在PostgreSQL中无法直接运行。

推荐实现（100%兼容PostgreSQL 12） #

使用窗口函数实现，逻辑清晰可维护，性能优异，不会触发GROUP BY语法约束。

-- 替换下方your_table为实际表名即可
WITH ranked_data AS (
    SELECT
        id,
        request_id,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY request_id
            ORDER BY created_at DESC, id DESC -- 时间相同时取id更大的记录，保证结果唯一
        ) AS group_rn,
        COUNT(*) OVER (PARTITION BY request_id) AS group_total
    FROM your_table
)
SELECT id, request_id, created_at
FROM ranked_data
WHERE
    group_rn = 1 -- 取组内最新记录
    AND group_total > 1; -- 排除无重复的单条分组

以上SQL执行后会返回你预期的结果：

备选聚合关联写法 #

如果需要兼容更老的PostgreSQL版本，可以用聚合+关联的写法实现：

SELECT t.id, t.request_id, t.created_at
FROM your_table t
JOIN (
    SELECT
        request_id,
        MAX(created_at) AS max_created_at
    FROM your_table
    GROUP BY request_id
    HAVING COUNT(*) > 1 -- 过滤掉无重复的分组
) t_group
ON t.request_id = t_group.request_id
AND t.created_at = t_group.max_created_at;

注意：该写法存在边界问题：如果同一个request_id下有多条记录created_at完全相同且都是最新时间，会返回多条匹配结果，对结果唯一性要求高的场景优先使用窗口函数写法。

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