PostgreSQL如何查询各属性下无标记且得票数最高的赋值记录

实现逻辑 #

• 用左连接关联assignments、votes、flags表，避免无投票、无标记的赋值记录被过滤
• 聚合统计每个赋值的得票数和标记数，过滤存在错误标记的记录
• 通过窗口函数对同一属性下的合法赋值按得票数倒序排序，取排名第一的记录
• 最后关联properties表获取属性名称

完整查询语句 #

WITH assignment_stats AS (
    -- 统计每个赋值的得票数、标记数
    SELECT
        a.id AS assignment_id,
        a.property_id,
        a.value,
        COUNT(v.id) AS vote_count,
        COUNT(f.id) AS flag_count
    FROM assignments a
    LEFT JOIN votes v ON a.id = v.assignment_id
    LEFT JOIN flags f ON a.id = f.assignment_id
    GROUP BY a.id, a.property_id, a.value
    HAVING COUNT(f.id) = 0 -- 过滤有错误标记的赋值
),
ranked_assignments AS (
    -- 对同一属性下的赋值按得票数倒序排名
    SELECT
        property_id,
        value,
        vote_count,
        -- 得票相同时按赋值ID升序排序，可按需调整规则
        ROW_NUMBER() OVER (PARTITION BY property_id ORDER BY vote_count DESC, assignment_id ASC) AS rank_num
    FROM assignment_stats
)
-- 取每个属性排名第一的赋值
SELECT
    p.name AS property_name,
    ra.value AS top_assignment_value,
    ra.vote_count
FROM properties p
INNER JOIN ranked_assignments ra ON p.id = ra.property_id
WHERE ra.rank_num = 1;

补充说明 #

1. 原来的伪代码使用INNER JOIN关联votes和flags会直接过滤掉没有投票、没有标记的赋值，不符合需求，因此改用LEFT JOIN
2. 聚合函数COUNT不能直接写在WHERE条件中，需要放在HAVING子句或者子查询中使用
3. 如果需要返回同一属性下得票并列最高的所有赋值，把ROW_NUMBER()替换为RANK()即可
4. 即使某个赋值没有任何投票记录，只要它是对应属性下唯一的合法赋值，也会正常返回，完全匹配需求

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