SQL如何计算全量分组总条数与带过滤分组总条数的比值？

这个需求完全可以实现，无需执行两次独立查询，通过条件聚合即可在单次查询中同时得到两种统计值并计算比例，还能避免两次查询之间数据变更导致的结果不一致问题。

实现思路 #

• 全量分组的统计值直接用COUNT(*)计算即可
• 带WHERE过滤条件的统计值可以用COUNT(CASE WHEN 过滤条件 THEN 1 END)实现，等价于先过滤再计数
• 计算比例时增加非0判断，避免除零报错

单分组对应比例查询SQL #

如果要获取每个X分组对应的比例，语句如下：

SELECT 
  X,
  COUNT(*) AS full_total, -- 无过滤的全量分组统计值
  COUNT(CASE WHEN Y IS NULL THEN 1 END) AS filtered_total, -- 带Y IS NULL过滤的分组统计值
  -- 计算比例，避免除零，过滤值为0时返回NULL或者自定义值
  CASE WHEN COUNT(CASE WHEN Y IS NULL THEN 1 END) <> 0 
       THEN COUNT(*) * 1.0 / COUNT(CASE WHEN Y IS NULL THEN 1 END) 
       ELSE NULL END AS ratio
FROM `table`
GROUP BY X

全局总比例查询SQL #

如果要计算所有分组的总全量条数 / 总过滤条数，不需要按X分组的话，语句如下：

SELECT 
  SUM(full_total) AS global_full_total,
  SUM(filtered_total) AS global_filtered_total,
  CASE WHEN SUM(filtered_total) <> 0 
       THEN SUM(full_total) * 1.0 / SUM(filtered_total)
       ELSE NULL END AS global_ratio
FROM (
  SELECT 
    COUNT(*) AS full_total,
    COUNT(CASE WHEN Y IS NULL THEN 1 END) AS filtered_total
  FROM `table`
  GROUP BY X
) t

注意事项 #

• 乘以1.0是为了避免整数除法自动截断小数的问题，部分数据库也可以用CAST转浮点类型实现
• 如果过滤条件复杂，只需要修改CASE WHEN里的判断逻辑即可，不需要调整整体查询结构

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