如何高效实现百分比数据直方图统计？求替代Sum(Case...)的SQL写法

嘿，这个问题提得很实在！我完全懂你那种写一堆CASE WHEN写到崩溃，还担心性能的感觉，给你分享几个更优的实现思路：

核心问题：为什么COUNT(PROP.Proportion <= 0.05)行不通？ #

先澄清一下你提到的简化写法——COUNT(PROP.Proportion <= 0.05)在大多数数据库里不会得到你想要的结果。因为COUNT()会统计所有非NULL值，而PROP.Proportion <= 0.05返回的是布尔值（TRUE/FALSE），这些值在数据库里会被视为非NULL，所以最终结果是全表总行数，而不是符合条件的记录数。如果要简化单个区间的统计，其实可以用SUM(CASE WHEN PROP.Proportion <= 0.05 THEN 1 ELSE 0 END)，但这和你原来的写法性能差异不大，真正的优化要从「批量区间统计」入手。

最优方案1：分组+区间映射（简洁高效） #

与其逐个区间写CASE WHEN，不如先把每个比例值映射到对应的区间，再分组计数。这种写法只需要扫描一次表，性能远优于多CASE WHEN，而且扩展性极强（加区间不用改逻辑）。

举个例子，假设你要按0.05为步长划分区间：

SELECT
  -- 生成区间标签，比如"0 - 0.05"、"0.05 - 0.1"
  CONCAT(
    FLOOR(PROP.Proportion / 0.05) * 0.05, 
    ' - ', 
    FLOOR(PROP.Proportion / 0.05) * 0.05 + 0.05
  ) AS proportion_range,
  COUNT(*) AS record_count
FROM your_table PROP
-- 按区间分组（用计算后的区间起始值分组，避免字符串分组的性能损耗）
GROUP BY FLOOR(PROP.Proportion / 0.05) * 0.05
-- 按区间顺序排序
ORDER BY FLOOR(PROP.Proportion / 0.05) * 0.05;

如果你的比例值可能超过1.0或者低于0，可以加个WHERE条件过滤：WHERE PROP.Proportion BETWEEN 0 AND 1。

最优方案2：固定区间+LEFT JOIN（保证空区间也显示） #

如果需要确保所有区间（哪怕没有数据）都出现在结果里（比如生成直方图时不能缺区间），可以先预定义所有区间，再通过LEFT JOIN统计：

-- 用CTE生成所有需要的区间
WITH proportion_ranges AS (
  SELECT 0 AS start_range, 0.05 AS end_range UNION ALL
  SELECT 0.05, 0.1 UNION ALL
  SELECT 0.1, 0.15 UNION ALL
  SELECT 0.15, 0.2 UNION ALL
  -- 继续添加到1.0的区间
  SELECT 0.95, 1.0
)
SELECT
  CONCAT(pr.start_range, ' - ', pr.end_range) AS proportion_range,
  -- COUNT(prop.Proportion)会自动忽略NULL，空区间显示0
  COUNT(PROP.Proportion) AS record_count
FROM proportion_ranges pr
LEFT JOIN your_table PROP
  ON PROP.Proportion > pr.start_range 
  AND PROP.Proportion <= pr.end_range
GROUP BY pr.start_range, pr.end_range
ORDER BY pr.start_range;

数据库专属简化技巧 #

有些数据库提供了专门的区间划分函数，能进一步简化代码：

• PostgreSQL：用width_bucket函数直接划分区间

  SELECT
    width_bucket(PROP.Proportion, 0, 1, 20) AS bucket_num, -- 0-1分成20个等宽区间
    CONCAT(
      (width_bucket(PROP.Proportion, 0, 1, 20)-1)*0.05, 
      ' - ', 
      width_bucket(PROP.Proportion, 0, 1, 20)*0.05
    ) AS proportion_range,
    COUNT(*) AS record_count
  FROM your_table PROP
  GROUP BY bucket_num
  ORDER BY bucket_num;
  

• MySQL：可以用ELT或FIELD函数，但不如分组映射直观，优先推荐第一种方案。

性能对比总结 #

• 多CASE WHEN写法：需要对每条记录进行N次条件判断（N是区间数），数据量大时会重复扫描表，性能最差。
• 分组映射写法：只扫描一次表，利用数据库的分组优化（如果PROP.Proportion有索引，还能走索引扫描），性能最优。
• 固定区间LEFT JOIN写法：预定义区间的开销可以忽略，核心还是一次JOIN扫描，性能接近分组映射，适合需要完整区间的场景。

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