如何用SQL计算分组后的累计百分比分布

说到SQL里算分组累计百分比，很多人第一反应就是PERCENT_RANK或CUME_DIST。但真用起来才发现，这两个函数默认是对整个结果集排序，根本分不了组。那怎么办呢？得自己动手组合窗口函数——核心就是用ROW_NUMBER() OVER(PARTITION BY...ORDER BY...)编号，再用COUNT(*) OVER(PARTITION BY...)算分母，最后算百分比。说起来简单，但细节上有不少坑。

用 ROW_NUMBER() 和 COUNT(*) 手动算累计占比

直接套用 PERCENT_RANK() 或 CUME_DIST() 看似省事，但它们的默认行为是按整个结果集排序计算，压根不认「分组内」的边界。真要按每个 category 单独算从低到高的累计占比，就得自己组合窗口函数。

核心思路很简单：先按组内排序并编号，再用组内总行数做分母。示例语句：

SELECT   category,  value,  ROW_NUMBER() OVER (PARTITION BY category ORDER BY value) AS rn,  COUNT(*) OVER (PARTITION BY category) AS total_in_group,  ROUND(100.0 * ROW_NUMBER() OVER (PARTITION BY category ORDER BY value) / COUNT(*) OVER (PARTITION BY category), 2) AS cum_pctFROM sales;

不过有几个点需要留意：

• ROW_NUMBER() 从 1 开始编号，所以第一个值的占比是 100.0 / total_in_group，不会是 0%。
• 如果存在相同 value，ROW_NUMBER() 会强制分配不同序号，导致相同的值却算出不同占比；想让同值共享同一累计比例，可以改用 RANK()，但分子逻辑也得同步调整。
• 分母必须用 COUNT(*) OVER (PARTITION BY ...)，不能用子查询或关联，否则窗口范围对不上。

当需要「等于或小于当前值」的累计占比时，用 SUM() OVER 配合布尔转整数

上面那个方法只算了「排在当前行及之前的位置占比」，但业务场景常常要的是「值 ≤ 当前行 value 的记录占组内多少百分比」——比如统计「价格 ≤ 当前商品价格的销量占比」。这时不能依赖排序序号，得直接统计满足条件的行数：

SELECT   category,  price,  SUM(CASE WHEN price <= t1.price THEN 1 ELSE 0 END)     OVER (PARTITION BY category ORDER BY price ROWS UNBOUNDED PRECEDING) AS cum_count,  COUNT(*) OVER (PARTITION BY category) AS total,  ROUND(100.0 * SUM(CASE WHEN price <= t1.price THEN 1 ELSE 0 END)     OVER (PARTITION BY category ORDER BY price ROWS UNBOUNDED PRECEDING) / COUNT(*) OVER (PARTITION BY category), 2) AS cum_pctFROM products t1;

这里有几个关键细节：

• ROWS UNBOUNDED PRECEDING 是必须的，否则默认的 RANGE 模式在相同 price 下会重复累加，结果偏高。
• CASE WHEN ... THEN 1 ELSE 0 把布尔判断转成可累加的数值，比子查询高效得多。
• ORDER BY 必须明确，且字段要和 CASE 中的比较字段一致，否则窗口范围不可控。

CUME_DIST() 的真实行为与适用边界

CUME_DIST() 返回「小于等于当前行值的行数 / 总行数」，听起来正好符合需求，但它真的不支持 PARTITION BY 后按组内排序计算吗？——其实支持。很多人误写成：

-- ❌ 错误：没写 ORDER BY，CUME_DIST 无意义CUME_DIST() OVER (PARTITION BY category)

正确写法必须带 ORDER BY：

-- ✅ 正确：按组内 price 排序后计算累计分布CUME_DIST() OVER (PARTITION BY category ORDER BY price)

但要注意：

• 它对相同 price 的所有行返回相同值（比如三个并列第2名，都返回 3/10 = 0.3），而 ROW_NUMBER() 方案中它们会得到 2/10、3/10、4/10 不同的值。
• 它天然包含「等于当前值」的逻辑，省去手动 CASE，但无法控制是否去重或跳过空值。
• 一些旧版 PostgreSQL（尤其 < 8.4）没有 CUME_DIST()，得退回手动方案。

NULL 值和排序方向对累计占比的影响

默认排序把 NULL 排在最前（ORDER BY value），如果业务要求 NULL 当作最大值或者干脆忽略，必须显式声明：

• 把 NULL 排最后：ORDER BY value NULLS LAST
• 完全排除 NULL 行：WHERE value IS NOT NULL（推荐，避免干扰分母计数）
• 升序（ASC）对应「从小到大累计」，降序（DESC）则变成「从大到小累计」，别搞反了业务含义。

另外，ROUND() 的精度取舍会影响展示一致性——比如 ROUND(99.999, 2) 得到 100.00，但实际分母除不尽时，各组最后一行未必正好是 100。这是浮点运算和四舍五入共同导致的，别当成逻辑错误。