如何在SQL中按价格区间动态划分Bin箱并统计分布直方图？-SQL-PHP中文网

WIDTH_BUCKET()是PostgreSQL和Oracle中快速生成等宽价格分箱的原生函数，将数值映射到1~n的整数桶中，如WIDTH_BUCKET(price, 0, 1000, 10)划分[0,1000)为10个等宽桶；需注意NULL、边界值处理及跨数据库兼容性。

如何在sql中按价格区间动态划分bin箱并统计分布直方图？

用 `WIDTH_BUCKET` 快速生成等宽价格分箱

PostgreSQL 和 Oracle 原生支持 WIDTH_BUCKET 函数，它是实现动态等宽分箱最直接的方式。它自动把数值列映射到指定数量的连续整数桶中（从 1 开始），不需要手写 CASE WHEN 或预设边界。

常见错误是传入的最小值/最大值用了实际数据的 MIN()/MAX() 聚合结果——这会导致子查询嵌套复杂、无法在窗口中复用；更稳妥的做法是先用 CTE 算出全局范围，再 JOIN 或用窗口函数广播。

WIDTH_BUCKET(price, 0, 1000, 10) 表示将 price 映射到 [0,1000) 区间内的 10 个等宽桶（每桶宽 100），price → 桶 0，price >= 1000 → 桶 11
若想让所有数据严格落在 1~N 桶内，确保 min_val ≤ 所有值 ≤ max_val，否则要额外 FILTER 或用 CASE 截断
MySQL / SQL Server 不支持该函数，需改用 FLOOR((price - min_val) / bucket_width) 手动计算，注意除零和 NULL 处理

MySQL 中用 `FLOOR` + 变量模拟动态分箱

MySQL 缺少原生分箱函数，但可以用 FLOOR 配合预计算的区间参数实现相同效果。关键在于避免硬编码边界——把 min_price、max_price、bucket_count 提取为变量或派生表字段，保证语句可复用。

容易忽略的是浮点精度问题：FLOOR((price - 0) / 100) 在 price = 100.0000001 时可能因计算误差变成 1，导致本该进第 1 桶（[0,100)）的数据跑到第 2 桶。建议统一转成 DECIMAL 运算，或对 price 先 ROUND(price, 2)。

先查范围：SELECT @min:=MIN(price), @max:=MAX(price), @n:=10 FROM products
再分箱：FLOOR((price - @min) / ((@max - @min + 1) / @n)) + 1 AS bucket_id（+1 是为了从 1 起编号）
必须加 +1 到分母防止除零；@max - @min + 1 是为避免当所有 price 相等时分母为 0

用 `GROUP BY` + 字符串拼接生成直方图标签

统计完每个桶的频次后，用户真正需要的是带可读区间的直方图输出，比如 “[200, 300)”、“[300, 400)” —— 这不能靠 WIDTH_BUCKET 自动给出，得用计算字段拼接。

典型陷阱是边界类型混淆：左闭右开 [a,b) 是数学和多数数据库分箱惯例，但业务报表常要求“¥200-¥299”这种左闭右闭形式。二者在整数价格上看似等价，一旦涉及小数（如 299.99）就可能漏数据。

PostgreSQL 示例：CONCAT('[', bucket_min, ', ', bucket_min + width, ')') AS range_label，其中 bucket_min = (bucket_id - 1) * width + min_val
别直接用 TO_CHAR 格式化浮点边界，先 ROUND(..., 2) 再拼，否则可能输出 “[199.99999999999997, 299.99999999999994)”
如果桶数固定但价格范围波动大，建议把 width 定义为 CEIL((max_val - min_val) / bucket_count) 向上取整，避免最后一桶过窄

处理空桶：用 `GENERATE_SERIES` 补全直方图横轴

原始分组只会返回有数据的桶，而直方图要求横轴连续（比如 10 个桶，即使第 3 桶计数为 0 也要显示）。PostgreSQL 可用 GENERATE_SERIES(1, 10) 生成完整桶 ID 序列，再 LEFT JOIN 实际统计结果；其他数据库需借助数字辅助表或递归 CTE。

很多人试过用 RIGHT JOIN 却发现结果为空——因为 RIGHT JOIN 要求右表有记录才能保留左表，正确做法永远是「以完整桶序列为左表，LEFT JOIN 统计结果」。另外，GENERATE_SERIES 返回的是整数，记得 CAST 成与桶 ID 相同类型（如 ::INT），否则 JOIN 失败。

PostgreSQL 补空桶：SELECT s.bucket_id, COALESCE(t.cnt, 0) AS count FROM GENERATE_SERIES(1, 10) AS s(bucket_id) LEFT JOIN (SELECT bucket_id, COUNT(*) AS cnt FROM ...) AS t USING (bucket_id)
SQL Server 可用 master..spt_values 辅助表或 VALUES (1),(2),...,(10) 手动枚举（适用于桶数固定场景）
补完后若需按价格升序展示，排序字段必须用桶对应的边界值（如 (bucket_id - 1) * width + min_val），而非 bucket_id 本身

动态分箱真正的复杂点不在公式，而在边界对齐：数据类型精度、NULL 处理、空桶补全、以及不同数据库对“区间包含关系”的默认约定。一旦 price 字段含 NULL 或异常值（负数、极大值），WIDTH_BUCKET 或 FLOOR 计算会静默偏移，建议始终加 WHERE price IS NOT NULL AND price >= 0 显式过滤。