PostgreSQL中RANK()函数计算政党选举排名的错误问题

错误原因 #

你当前的查询逻辑是先筛选出绿党家族的政党，再计算排名。此时窗口函数PARTITION BY e.id只能在已筛选的小范围数据内分区排名，而非整场选举的所有政党，所以得到的排名是绿党内部的相对排名，不是真实的全场排名。

正确查询写法 #

需要先计算所有政党在目标选举中的排名，再筛选出绿党家族的政党数据，具体实现可以用CTE（公共表表达式）：

WITH election_full_ranks AS (
    -- 第一步：计算所有政党在对应选举中的全场排名
    SELECT
        e.country_name,
        EXTRACT(YEAR FROM e.e_date) AS year,
        e.party_name,
        ROUND(e.vote_share, 1) AS vote_share,
        e.party_id,
        -- 按选举ID分区，所有政党按得票率降序排名
        RANK() OVER (PARTITION BY e.id ORDER BY e.vote_share DESC) AS vote_rank_number
    FROM election e
    WHERE
        e.e_type = 'parliament'
        AND e.country_name = 'Luxembourg'
        AND e.e_date >= '1980-01-01'::date
        AND e.e_date < '2020-01-01'::date
)
-- 第二步：筛选出绿党家族的政党数据
SELECT
    country_name,
    year,
    party_name,
    vote_share,
    vote_rank_number
FROM election_full_ranks
JOIN party p ON election_full_ranks.party_id = p.id
WHERE p.family_name = 'Green/Ecologist'
ORDER BY country_name, year;

关键说明 #

• 先通过CTEelection_full_ranks处理所有参选政党，确保每个选举（e.id）的分区包含该场选举的全部数据，这样计算出的vote_rank_number是政党在整场选举中的真实排名。
• 后续关联party表筛选绿党家族，避免提前过滤导致排名计算范围错误。
• 用party_id关联两张表比用party_name更可靠，避免政党名称重复带来的匹配错误。

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