如何计算weather表中各zip_code下events字段为NULL的行占比？

需求：统计weather表中，每个zip_code分组里events字段值为NULL的行占该分组总行数的比例。表包含events（TEXT类型）、zip_code（INTEGER类型）两个字段。

当前查询仅能统计各分组内events为NULL的行数：

SELECT zip_code, COUNT(*) AS percentage
FROM weather
WHERE events IS NULL
GROUP BY zip_code, events;

查询输出：

zip_code percentage
94041        639
94063        639
94107        574
94301        653
95113        638

困惑：无法获取每个zip_code分组的总行数，无法执行(空值行数*100)/总行数的占比计算。

解决方案 #

方法1：使用窗口函数+聚合函数直接计算 #

利用FILTER子句统计空值行数，结合分组的总行数计算占比：

SELECT 
  zip_code,
  ROUND(
    (COUNT(*) FILTER (WHERE events IS NULL) * 100.0) / COUNT(*),
    2
  ) AS null_percentage
FROM weather
GROUP BY zip_code;

• COUNT(*) FILTER (WHERE events IS NULL)：精准统计当前分组内events为NULL的行数
• COUNT(*)：统计当前分组的总行数
• ROUND(..., 2)：将结果保留两位小数，可根据需求调整小数位数

方法2：子查询预统计总行数关联计算 #

先分别统计各分组的空值行数和总行数，再通过关联得到占比：

SELECT 
  w.zip_code,
  ROUND(
    (null_count * 100.0) / total_count,
    2
  ) AS null_percentage
FROM (
  SELECT zip_code, COUNT(*) AS null_count
  FROM weather
  WHERE events IS NULL
  GROUP BY zip_code
) w
JOIN (
  SELECT zip_code, COUNT(*) AS total_count
  FROM weather
  GROUP BY zip_code
) t ON w.zip_code = t.zip_code;

方法3：利用AVG函数简化逻辑 #

利用布尔值在数值计算中等价于1/0的特性，用AVG直接计算占比：

SELECT 
  zip_code,
  ROUND(
    AVG(CASE WHEN events IS NULL THEN 100.0 ELSE 0 END),
    2
  ) AS null_percentage
FROM weather
GROUP BY zip_code;

部分数据库支持布尔值转数值的简化写法：

SELECT 
  zip_code,
  ROUND(AVG((events IS NULL)::numeric) * 100, 2) AS null_percentage
FROM weather
GROUP BY zip_code;

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