BigQuery简化表缺失值统计查询的方法咨询

嗨，这个问题我太懂了——手动写47个几乎一样的UNION块确实是个折磨人的活儿！在BigQuery里有两种更高效的方法来搞定这个缺失值统计，不用重复写那么多冗余代码：

方法一：用UNPIVOT+一次性聚合（半自动化，适合列数固定的场景） #

这种方法只需要你把所有列名列两次，比写47个SELECT块轻松太多：

WITH column_counts AS (
  SELECT
    COUNT(id) AS id,
    COUNT(flag_tsunami) AS flag_tsunami,
    -- 这里依次添加所有47列的COUNT(列名) AS 列名
    COUNT(magnitude) AS magnitude,
    COUNT(depth) AS depth,
    -- ... 把剩下的列都按这个格式补上
    COUNT(*) AS total_entries
  FROM `youtube-factcheck.earthquake_analysis.earthquakes_copy`
),
unpivoted AS (
  SELECT
    column_name,
    non_missing_entries,
    total_entries,
    (total_entries - non_missing_entries) * 100.0 / total_entries AS percentage_missing
  FROM column_counts
  UNPIVOT (
    non_missing_entries FOR column_name IN (
      id, flag_tsunami, magnitude, depth,
      -- 对应上面的列名，依次补上剩下的列
    )
  )
)
SELECT column_name, non_missing_entries, percentage_missing
FROM unpivoted
ORDER BY column_name;

原理很简单：先在第一个CTE里一次性算出所有列的非缺失数和总条目数，再用UNPIVOT把列结构转成你需要的行结构，最后计算缺失率就行。

方法二：动态SQL+INFORMATION_SCHEMA（全自动化，彻底解放双手） #

如果连列名都不想手动写，用动态SQL自动获取表的所有列名，完全不用管47列的繁琐：

DECLARE column_list STRING;

-- 自动从元数据里拉取目标表的所有列名，拼接成聚合查询需要的字符串
SET column_list = (
  SELECT STRING_AGG(
    CONCAT('COUNT(', column_name, ') AS ', column_name),
    ', '
  )
  FROM `youtube-factcheck.earthquake_analysis.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'earthquakes_copy'
);

-- 动态生成并执行完整的统计查询
EXECUTE IMMEDIATE FORMAT("""
  WITH column_counts AS (
    SELECT
      %s,
      COUNT(*) AS total_entries
    FROM `youtube-factcheck.earthquake_analysis.earthquakes_copy`
  ),
  unpivoted AS (
    SELECT
      column_name,
      non_missing_entries,
      total_entries,
      (total_entries - non_missing_entries) * 100.0 / total_entries AS percentage_missing
    FROM column_counts
    UNPIVOT (
      non_missing_entries FOR column_name IN (
        %s
      )
    )
  )
  SELECT column_name, non_missing_entries, percentage_missing
  FROM unpivoted
  ORDER BY column_name;
""", column_list, REPLACE(REPLACE(column_list, 'COUNT(', ''), ') AS ', ', '));

这个方法会自动读取表的元数据（所有列名），然后动态拼接出完整的SQL语句执行，完全不用手动处理任何列名，一劳永逸。如果以后表的列有增减，这个查询也能自动适配。

两种方法各有优势：方法一更直观，适合不太熟悉动态SQL的朋友；方法二彻底自动化，适合列数多或者列可能变化的场景。

备注：内容来源于stack exchange，提问作者Musebe Ivan