使用DISTINCT ON、COUNT()与ORDER BY时PostgreSQL结果异常问题

问题分析 #

核心需求很明确：每个用户对同一帖子的多次投票里，只取最新的一条作为有效状态，最终统计所有帖子的有效点赞（is_upvote = 't'）数量。之前的查询出错主要有两个原因：

• 用timestamp做排序字段不可靠，同一用户同一时间多次操作时，排序结果不确定，PostgreSQL可能随机返回一条；
• 批量统计时，若ORDER BY的字段和DISTINCT ON的分组逻辑不匹配，查询优化器可能跳过正确排序，导致取到的不是最新记录；而单独指定post_id时，查询范围小，优化器不会跳过排序，所以结果正确。

推荐查询方案 #

方案1：DISTINCT ON + 自增id排序（推荐） #

自增id是严格递增的，比timestamp更能保证排序的唯一性，避免歧义：

SELECT 
    post_id,
    COUNT(CASE WHEN is_upvote = 't' THEN 1 END) AS votes_count
FROM (
    SELECT DISTINCT ON (post_id, voter_id)
        post_id,
        voter_id,
        is_upvote
    FROM vote_table
    ORDER BY post_id, voter_id, id DESC  -- 按帖子、用户分组，取每个用户最新的投票（id最大的）
) AS latest_votes
GROUP BY post_id
ORDER BY post_id;

方案2：窗口函数ROW_NUMBER() #

逻辑更直观，也能避免DISTINCT ON可能的优化问题：

SELECT 
    post_id,
    COUNT(CASE WHEN is_upvote = 't' THEN 1 END) AS votes_count
FROM (
    SELECT 
        post_id,
        voter_id,
        is_upvote,
        ROW_NUMBER() OVER (PARTITION BY post_id, voter_id ORDER BY id DESC) AS rn
    FROM vote_table
) AS ranked_votes
WHERE rn = 1  -- 只保留每个用户对每个帖子的最新投票
GROUP BY post_id
ORDER BY post_id;

验证示例数据 #

假设你的带自增id的示例表数据如下：

id | post_id | voter_id | is_upvote | timestamp
----+---------+----------+-----------+-----------
1  |     440 |       28 | f         | timestamp
2  |     440 |      497 | f         | timestamp
3  |     440 |      959 | t         | timestamp
4  |     440 |      959 | f         | timestamp
5  |     440 |      959 | t         | timestamp
6  |     440 |      959 | f         | timestamp
7  |     440 |      959 | t         | timestamp

执行上述任一查询后，post_id=440的votes_count会是1（仅voter_id=959的最新状态为t，其余用户均为f），完全符合预期。

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