如何在PostgreSQL中基于公民投票出现频率计算候选人百分位排名

我有一张名为votes的表，包含voter_id、candidate_id和is_citizen（布尔类型）三列。选民可多次投票，每为一位候选人投票就会在表中新增一条记录，其中is_citizen标识选民是否为公民。选民若为多位候选人投票会多次出现在表中，候选人若获得多人投票也会多次出现，且每个选民-候选人组合唯一。

给定一个candidate_id，我需要计算该候选人基于其在表中出现频率的百分位排名（仅统计公民的投票，排除非公民投票）。例如，现有三位候选人：candidate_id为1、2、3，其中候选人1获得5次公民投票，候选人2获得7次，候选人3获得20次。此时查询candidate_id为2的候选人，应返回0.5，即其处于50百分位（按出现频率而非总票数计算）。

我尝试编写了如下SQL语句，但运行报错：

SELECT
  candidate_id,
  PERCENT_RANK() WITHIN GROUP (ORDER BY COUNT(*) DESC)
FROM votes
GROUP BY candidate_id
HAVING candidate_id = <candidate_id>;

你的SQL报错主要有几个问题：

1. PERCENT_RANK()是窗口函数，不能用WITHIN GROUP的写法，必须通过OVER()子句定义窗口范围；
2. 没有过滤非公民的投票，不符合统计要求；
3. 直接在聚合后用HAVING筛选，无法正确计算全局的百分位排名。

下面是修正后的SQL，兼容MySQL 8.0+、PostgreSQL、SQL Server等主流数据库：

WITH candidate_votes AS (
    -- 先统计每个候选人的公民投票数
    SELECT
        candidate_id,
        COUNT(*) AS vote_count
    FROM votes
    WHERE is_citizen = TRUE -- 只保留公民投票
    GROUP BY candidate_id
),
candidate_ranks AS (
    -- 计算每个候选人的百分位排名（按得票数降序）
    SELECT
        candidate_id,
        vote_count,
        PERCENT_RANK() OVER (ORDER BY vote_count DESC) AS percentile_rank
    FROM candidate_votes
)
-- 筛选目标候选人的排名结果
SELECT
    candidate_id,
    percentile_rank
FROM candidate_ranks
WHERE candidate_id = <目标候选人ID>; -- 替换为实际的candidate_id，比如2

说明 #

• 第一个CTE candidate_votes 先过滤出公民投票，统计每位候选人的有效得票数；
• 第二个CTE candidate_ranks 基于得票数降序，用PERCENT_RANK()计算全局的百分位排名，公式为(当前排名-1)/(总候选人数-1)，和你示例中的结果一致（候选人2排名第2，总人数3，(2-1)/(3-1)=0.5）；
• 最后一步筛选目标候选人，得到对应的百分位排名。

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