如何在PostgreSQL中实现指定阈值分箱数据列创建

你给出的分箱逻辑分界点为 0, 30, 200, 500, 1000, num_subjects_enrolled字段全局最大值，区间规则为左开右闭，和示例中583 → 500 <x ≤ 1000的效果完全匹配，以下是可直接使用的PostgreSQL实现：

逻辑最直观，查询性能最优：

SELECT
  num_subjects_enrolled,
  CASE
    WHEN num_subjects_enrolled <= 0 THEN 'x ≤ 0' -- 兼容小于等于0的异常值
    WHEN num_subjects_enrolled <= 30 THEN '0 <x ≤ 30'
    WHEN num_subjects_enrolled <= 200 THEN '30 <x ≤ 200'
    WHEN num_subjects_enrolled <= 500 THEN '200 <x ≤ 500'
    WHEN num_subjects_enrolled <= 1000 THEN '500 <x ≤ 1000'
    ELSE CONCAT('1000 <x ≤ ', (SELECT MAX(num_subjects_enrolled) FROM 你的表名))
  END AS bin_num_subjects_enrolled_int
FROM 你的表名;

不需要手动确认数据最大值，自动取字段全局最大值作为最后一个分箱的上界：

WITH max_val_cte AS (
  SELECT MAX(num_subjects_enrolled) AS max_enrolled FROM 你的表名
)
SELECT
  t.num_subjects_enrolled,
  CASE
    WHEN t.num_subjects_enrolled <= 0 THEN 'x ≤ 0'
    WHEN t.num_subjects_enrolled <= 30 THEN '0 <x ≤ 30'
    WHEN t.num_subjects_enrolled <= 200 THEN '30 <x ≤ 200'
    WHEN t.num_subjects_enrolled <= 500 THEN '200 <x ≤ 500'
    WHEN t.num_subjects_enrolled <= 1000 THEN '500 <x ≤ 1000'
    ELSE CONCAT('1000 <x ≤ ', mv.max_enrolled)
  END AS bin_num_subjects_enrolled_int
FROM 你的表名 t
CROSS JOIN max_val_cte mv;

验证：传入示例值583时，会命中num_subjects_enrolled <= 1000分支，返回500 <x ≤ 1000，和预期效果完全一致。
如果需要返回整数类型的分箱序号而非字符串标签，只需要把THEN后面的字符串替换为对应序号数字即可。

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