Ntile与Percent_Rank差异探究及分组结果异常问题排查

实验背景 #

通过R模拟含异常值的学生身高数据集，并用DuckDB模拟Netezza环境进行分组对比：

生成数据集的R代码 #

# 生成正常身高数据
df1 = data.frame(id = 1:100, height = rnorm(100, 130,10))
# 生成异常身高数据（超高值）
df2 = data.frame(id = 101:110, height = rnorm(10, 220, 1))
# 合并数据集
df = rbind(df1, df2)

连接DuckDB并上传数据 #

library(duckdb)
library(dplyr)
library(dbplyr)

# 建立内存连接
con <- DBI::dbConnect(duckdb::duckdb(), dbdir = ":memory:")
# 上传数据到数据库
copy_to(con, df)

预期与实际结果 #

预期：

• NTILE(5)：将110条数据均分为5组，每组22条，异常值会和部分高身高正常数据同组
• PERCENT_RANK()：按身高的百分位区间分组，异常值所在的最高区间人数应该更少（仅10条）

实际：两种分组方法结果完全一致，每组均为22条数据。

原因解释 #

问题出在PERCENT_RANK()的计算逻辑和分组条件的匹配上：

1. PERCENT_RANK的计算公式：PERCENT_RANK() = (RANK() - 1) / (总数据量 - 1)
   对于总数据量N=110的数据集：

   • 排名101-110的异常值，其PERCENT_RANK范围是(101-1)/(110-1)=100/109≈0.917到(110-1)/(110-1)=1
   • 排名88的正常数据，PERCENT_RANK为(88-1)/109=87/109≈0.798，刚好低于0.8

2. 分组条件的覆盖：
   你的CASE语句中，第5组是ELSE 5（即PERCENT_RANK>0.8），而排名89-110的所有数据（共22条）的PERCENT_RANK都≥(89-1)/109=88/109≈0.807，全部落入第5组，所以每组人数刚好都是22，和NTILE(5)的均分结果一致。

解决办法 #

要实现“按身高的实际百分位区间分组，让异常值单独占小部分”的需求，应该直接基于身高的分位数来划分区间，而非依赖PERCENT_RANK()的结果。可以用PERCENTILE_CONT()计算分位数点，再进行分组：

基于分位数的分组SQL #

WITH quantiles AS (
  SELECT
    PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY height) AS q20,
    PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY height) AS q40,
    PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY height) AS q60,
    PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY height) AS q80
  FROM df
),
rank_groups AS (
  SELECT
    d.height,
    CASE
      WHEN d.height <= q.q20 THEN 1
      WHEN d.height <= q.q40 THEN 2
      WHEN d.height <= q.q60 THEN 3
      WHEN d.height <= q.q80 THEN 4
      ELSE 5
    END AS rank_group
  FROM df d, quantiles q
)
SELECT rank_group, COUNT(*) AS count, MIN(height) AS min_height, MAX(height) AS max_height
FROM rank_groups
GROUP BY rank_group
ORDER BY rank_group;

结果说明 #

这个查询会先计算身高的20%、40%、60%、80%分位数，然后根据身高落在哪个区间分组。由于异常值远高于80%分位数，第5组只会包含这10条异常值，而前4组各包含25条正常数据（100条正常数据均分4组），符合你的预期。

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