如何在PostgreSQL中按Rank分组计算特定规则的50th percentile？

根据你的需求，我们需要基于Primary Key的位置来计算每个Rank分组的50th百分位数，核心是先确定每个分组对应的目标Primary Key，再提取或计算对应的Value。下面是具体的实现方案：

核心思路 #

1. 先统计每个Rank分组的总行数，以及当前分组之前所有Rank的累计行数
2. 根据规则计算每个分组的目标Primary Key：前置累计行数 + 0.5 * 当前分组行数
3. 针对目标Primary Key是整数或小数的情况，分别处理（小数时取前后两行的平均值，更符合百分位数的定义）

完整SQL代码 #

假设你的表名为user_data，列名分别为user_id, rank, value, primary_key（注意rank是PostgreSQL关键字，所以用双引号包裹）：

WITH rank_group_stats AS (
    -- 统计每个Rank的行数，以及前置Rank的累计总行数
    SELECT
        "rank",
        COUNT(*) AS group_row_count,
        -- 计算当前Rank之前所有分组的总行数，第一个Rank的前置数为0
        COALESCE(SUM(COUNT(*)) OVER (ORDER BY "rank" ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS prev_rank_total
    FROM user_data
    GROUP BY "rank"
    ORDER BY "rank"
),
target_primary_keys AS (
    -- 计算每个Rank对应的目标Primary Key
    SELECT
        "rank",
        prev_rank_total + (0.5 * group_row_count) AS target_pk
    FROM rank_group_stats
)
-- 最终查询，获取每个Rank的50th百分位数
SELECT
    tp."rank",
    CASE
        -- 如果目标PK是整数，直接取对应行的Value
        WHEN tp.target_pk = FLOOR(tp.target_pk) THEN
            (SELECT value FROM user_data WHERE primary_key = tp.target_pk)
        -- 如果是小数，取前后两个整数PK对应的Value的平均值
        ELSE
            (SELECT AVG(value) FROM user_data WHERE primary_key IN (FLOOR(tp.target_pk), CEIL(tp.target_pk)))
    END AS "50th percentile"
FROM target_primary_keys tp;

代码解释 #

1. rank_group_stats CTE：

   • 按rank分组，用COUNT(*)得到每个分组的总行数group_row_count
   • 窗口函数SUM(COUNT(*)) OVER (...)用来累计当前Rank之前所有分组的行数，ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING表示只包含当前行之前的所有行，COALESCE确保第一个Rank的前置累计数为0

2. target_primary_keys CTE：

   • 严格按照你指定的规则计算目标Primary Key：前置累计行数加上当前分组行数的一半

3. 主查询：

   • 处理两种情况：如果目标PK是整数，直接查询对应行的Value；如果是小数（比如分组行数为奇数时），取前后两个PK对应Value的平均值，这更符合50th百分位数（中位数）的常见计算方式

注意事项 #

• 这个方案基于你的假设：primary_key是全局连续递增的，且同一Rank内的primary_key是连续的。如果你的数据不符合这个假设，需要调整逻辑，比如先对每个Rank内的行按primary_key排序，用组内行号来计算中位数位置，再映射到全局的primary_key
• 如果你的需求是基于value排序的百分位数（而非primary_key位置），那需要用PostgreSQL内置的PERCENTILE_CONT或PERCENTILE_DISC函数，比如：

  SELECT
      "rank",
      PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS "50th percentile"
  FROM user_data
  GROUP BY "rank";
  

  但这和你指定的基于primary_key位置的规则不同，需要根据实际需求选择

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