求PostgreSQL中按3分位数统计数据行数的查询语句

需求说明 #

根据给定的水果价格数据，统计三个3分位数区间内的记录行数：

• percentile_3_1：price < 10 的行数
• percentile_3_2：处于中间3分位数区间的行数
• percentile_3_3：处于最高3分位数区间的行数

输入数据 #

假设数据存储在名为 fruits 的表中，结构如下：

查询语句 #

方式一：按指定区间边界（price < 10）

用条件聚合直接统计各区间行数：

SELECT
  COUNT(CASE WHEN price < 10 THEN 1 END) AS percentile_3_1,
  COUNT(CASE WHEN price >= 10 AND price < 26 THEN 1 END) AS percentile_3_2,
  COUNT(CASE WHEN price >= 26 THEN 1 END) AS percentile_3_3
FROM fruits;

方式二：自动计算分位数边界（通用方案）

通过 PERCENTILE_DISC 函数自动计算分位点，避免硬编码数值，适配任意数据分布：

WITH quantiles AS (
  -- 计算3分位数的两个分界点
  SELECT
    PERCENTILE_DISC(1.0/3) WITHIN GROUP (ORDER BY price) AS q1,
    PERCENTILE_DISC(2.0/3) WITHIN GROUP (ORDER BY price) AS q2
  FROM fruits
)
SELECT
  COUNT(CASE WHEN price < q1 THEN 1 END) AS percentile_3_1,
  COUNT(CASE WHEN price >= q1 AND price < q2 THEN 1 END) AS percentile_3_2,
  COUNT(CASE WHEN price >= q2 THEN 1 END) AS percentile_3_3
FROM fruits, quantiles;

输出结果 #

两种方式都会得到符合期望的结果：

补充说明 #

• PERCENTILE_DISC 返回数据集中实际存在的离散分位数，若需要连续分位数，可替换为 PERCENTILE_CONT 函数。
• 条件聚合中，不满足条件的 CASE 语句返回 NULL，COUNT 函数会自动忽略 NULL 值，从而得到各区间的有效行数。

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