BigQuery检查整个表格是否为空值 [英] BigQuery check entire table for null values

问题描述

不确定此处是否需要可复制的示例.我在BigQuery中有一个很大的表格(10000行x 100列)，我想知道任何列是否具有空值，以及有多少个空值.我是否可以运行一个查询，该查询将返回一个1行的表，该表指示每列中的空值数量，而不需要100个 ifnull 调用?

Not sure if a reproducible example is necessary here. I have a big-ish and wide-ish table in BigQuery (10K rows x 100 cols) and I would like to know if any columns have null values, and how many null values there are. Is there a query that I can run that would return a 1-row table indicating the number of null values in each column, that doesn't require 100 ifnull calls?

谢谢！

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL
SELECT col_name, COUNT(1) nulls_count
FROM `project.dataset.table` t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"(\w+)":null')) col_name
GROUP BY col_name 

而不是仅返回一行-它返回其中包含NULL的那些列-每列并在单独的行中计数-如下面的示例

Instead of returning just one row - it returns those column which have NULL in them - each column and count in separate row - like in below example

Row col_name    nulls_count  
1   col_a       21   
2   col_d       12   

这篇关于BigQuery检查整个表格是否为空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！