BigQuery中SQL分组计算百分比结果异常问题排查

百分比计算错误的核心原因是分母取值错误：

• 初始查询里的子查询SELECT count(primary_type) from bigquery-publicdata.austin_crime.crime`` 没有添加primary_type = "Theft: BOV"的过滤条件，统计的是全表所有犯罪类型的总记录数，不是目标类别的总记录数。
• 返回的8.59%刚好是10028（Not cleared的记录数）除以全表总记录数的结果，和预期95%的偏差完全匹配。
• 额外逻辑问题：用count(clearance_status)统计记录数时，会自动忽略clearance_status为null的行，导致null分组的统计值为0，分母也会少算对应记录。
• 之前窗口函数返回错误结果，大概率是没写PARTITION BY primary_type分区逻辑，窗口范围覆盖了全表数据，和子查询的错误逻辑一致。

推荐使用窗口函数写法，性能更好、扩展性更强，不需要重复写过滤条件：

SELECT 
  primary_type, 
  clearance_status, 
  ROUND(
    COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY primary_type) * 100,
    2
  ) AS percentage
FROM `bigquery-public-data.austin_crime.crime`
-- 若需要统计所有犯罪类别的结案状态占比，直接删除下面的WHERE条件即可
WHERE primary_type = "Theft: BOV"
GROUP BY primary_type, clearance_status

如果习惯用子查询写法，需要给子查询加上和外层完全一致的过滤条件，保证分母范围正确：

SELECT 
  primary_type, 
  clearance_status, 
  ROUND(
    COUNT(*) / (
      SELECT COUNT(*) 
      FROM `bigquery-public-data.austin_crime.crime` 
      WHERE primary_type = "Theft: BOV"
    ) * 100,
    2
  ) AS percentage
FROM `bigquery-public-data.austin_crime.crime`
WHERE primary_type = "Theft: BOV"
GROUP BY primary_type, clearance_status

用提供的数值代入计算：

• Not cleared：10028 / 10545 * 100 ≈ 95.10%，和手动核算结果一致
• Cleared by Arrest：242 / 10545 *100 ≈ 2.29%
• Cleared by Exception：30 / 10545 *100 ≈ 0.28%
• 所有分组占比相加为100%，结果正确。

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