PostgreSQL中如何计算指定列内给定值的百分位排名

不用生成全量百分位序列，直接通过统计行数占比就能高效算出0.25对应的百分位排名——也就是该值大于多少百分比的观测值。

高效查询语句 #

SELECT 
  (COUNT(*) FILTER (WHERE score < 0.25) * 100.0) / COUNT(*) AS percentile_rank
FROM mytable;

逻辑说明 #

• COUNT(*) FILTER (WHERE score < 0.25)：统计score列中小于0.25的观测行数
• 除以总行数COUNT(*)再乘以100，得到的结果就是0.25大于的观测值占比，也就是你要的百分位排名
• 用100.0而非100是为了触发浮点数计算，避免整数除法丢失精度

如果需要把等于0.25的观测值也纳入统计（即计算0.25大于等于多少百分比的观测值），只需将条件改为score <= 0.25即可。

性能优化建议 #

针对50万行的表，给score列创建索引能显著提升查询速度：

CREATE INDEX idx_mytable_score ON mytable(score);

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