基于报告阅读量获取用户百分位排名的技术问询

嘿，我当初刚接触这两个百分位函数的时候也懵，尤其是对着文档里的公式绕半天，后来拿实际数据试了几次才彻底理清！结合你要根据用户阅读报告数量算百分位的场景，我给你掰扯清楚核心区别和用法：

核心差异：返回值是否来自原始数据 #

这俩函数都是用来计算指定百分位的数值，但最本质的区别在于返回值是不是你数据表中真实存在的数：

1. PERCENTILE_DISC（离散型） #

DISC是Discrete的缩写，它会从你的原始数据里挑一个真实存在的值。逻辑是：找到最小的那个值，使得至少p比例的记录（p是你指定的百分位，比如0.9对应90%）小于等于它。

举个例子，假设你的用户阅读量是[1,2,3,4,5,6,7,8,9,10]，要算90百分位：

• 90%的用户（9个）阅读量≤9，而10个用户里只有10%的人超过9，所以PERCENTILE_DISC(0.9)会返回9——这个值是原始数据里实实在在存在的。

如果数据是[1,1,2,3,5,8,13]，算50百分位（中位数）：

• 刚好一半的用户（3个）阅读量≤3，所以返回3，同样是原始数据里的数。

2. PERCENTILE_CONT（连续型） #

CONT是Continuous的缩写，它会通过线性插值计算一个可能不存在于原始数据中的值。逻辑是先通过公式确定百分位对应的位置，再在相邻的两个数据点之间做插值计算。

还是用第一个例子[1,2,...,10]算90百分位：

• 位置公式是：(N-1)*p + 1，N是总记录数（10），p=0.9，算出来是(10-1)*0.9 +1 =9.1
• 这个位置在第9个值（9）和第10个值（10）之间，插值计算：9 + (10-9)*(0.1) =9.1——这个值不在原始数据里，但更精确地反映了连续分布的百分位。

如果是中位数，当N是奇数时（比如刚才的7条数据），位置刚好落在第4个值上，所以PERCENTILE_CONT和PERCENTILE_DISC结果一样；但如果N是偶数，比如8条数据，50百分位的位置是4.5，就会取第4和第5个值的平均值。

结合你的场景怎么选？ #

你的数据是用户阅读报告的数量，是离散的整数，所以分两种情况：

• 如果业务需求是返回一个真实存在的阅读量作为分界（比如“要进入前10%，最少需要读X篇报告”），选PERCENTILE_DISC，用户能直接对应到自己的阅读数；
• 如果是做统计分析需要更精确的连续值（比如绘制百分位趋势图），选PERCENTILE_CONT。

实际SQL示例 #

假设你的表叫user_report_stats，字段是user_id（用户ID）和report_read_count（阅读报告数量）：

计算整体90百分位的阅读数 #

-- 离散型：返回真实存在的阅读数
SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY report_read_count) OVER () AS p90_disc
FROM user_report_stats;

-- 连续型：返回插值后的精确值
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY report_read_count) OVER () AS p90_cont
FROM user_report_stats;

给每个用户计算他的阅读量对应的百分位排名 #

如果你是想知道“这个用户的阅读量超过了百分之多少的用户”，那用PERCENT_RANK()更直接（和你问的两个函数互补）：

SELECT 
    user_id,
    report_read_count,
    -- 乘以100转成百分比形式，保留两位小数
    ROUND(PERCENT_RANK() OVER (ORDER BY report_read_count) * 100, 2) AS percentile_rank
FROM user_report_stats;

比如返回85.23，就表示这个用户的阅读量超过了85.23%的用户。

小提醒 #

• 两个函数都支持PARTITION BY，比如按用户所在部门分组计算百分位：

  PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY report_read_count) OVER (PARTITION BY department_id)
  

• 注意排序方向：默认是升序，如果你想算“超过90%的用户”（即Top10%的分界值），可以把排序改成降序：ORDER BY report_read_count DESC

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