调整BigQuery查询：统计列非空值并识别含非空值列

基于原JSON正则思路的调整方案 #

原查询通过提取JSON中值为null的列名统计空值数，要统计非空值，只需修改正则表达式，用负前瞻断言匹配值不为null的列名：

SELECT col_name, COUNT(1) non_nulls_count
FROM `你的项目.你的数据集.你的表` t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"([^"]+)":(?!null)')) col_name
GROUP BY col_name

说明：

• 正则"([^"]+)":(?!null)：
  • "([^"]+)"匹配所有列名（支持含特殊字符的列名，比\w+更通用）
  • (?!null)是负前瞻断言，确保列名后紧跟的不是null
• 每行中值不为null的列名会被提取，GROUP BY后统计的次数就是该列的非空行数
• 全空的列（如示例中的column_c）因为从未被匹配到，不会出现在结果中，正好符合"识别至少含1个非空值的列"的需求

更可靠的通用方案（推荐） #

用JSON正则可能存在边缘场景限制（比如列值为特殊JSON格式时），推荐用UNPIVOT结合动态SQL的方式，自动适配所有列：

EXECUTE IMMEDIATE (
  SELECT CONCAT(
    'SELECT col_name, COUNT(*) non_nulls_count FROM `你的项目.你的数据集.你的表` UNPIVOT (value FOR col_name IN (',
    STRING_AGG(QUOTENAME(column_name), ','),
    ')) WHERE value IS NOT NULL GROUP BY col_name'
  )
  FROM `你的项目.你的数据集.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = '你的表'
)

说明：

• 利用INFORMATION_SCHEMA.COLUMNS自动获取目标表的所有列名
• 动态生成UNPIVOT语句，将列转为行后过滤非空值，再统计每列的非空行数
• 无需手动维护列名，适合列数较多或列名经常变更的表

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