英文:
BigQuery: Count non-null values across all columns with REGEX
问题
以下是翻译好的代码部分：
SELECT col_name, COUNT(1) non_nulls_count
FROM table t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"(\w+)":(?!null)')) col_name
GROUP BY col_name;
请注意，这段代码用于计算每个列中的非空值数量。
英文:
I have the following query that helps me count how many null values were reported in each column across all columns of a table in BQ:
SELECT col_name, COUNT(1) nulls_count
FROM table t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"(\w+)":null')) col_name
GROUP BY col_name
;
I need to adjust it so it counts the non-null values. I tried to use negative lookahead but it doesn't seem to work.
My end goal is to indicate wether a certain column reports at least 1 non-null value.
Input example (the table):
Output example:
column_c is not present since all of its values are nulls.
答案1
得分: 1
以下是你要翻译的代码部分：
select * from (select column, countif(val!= 'null') non_null
from `dataset.table` table1
,unnest(array(
select as struct trim(ar[offset(0)], '"') column, trim(ar[offset(1)], '"') val
from unnest(split(trim(to_json_string(table1), '{}'))) pb,
unnest([struct(split(pb, ':') as ar)])
)) record
group by column) where non_null!=0
英文:
You can try this, (without REGEX) solution
select * from (select column, countif(val!= 'null') non_null
from `dataset.table` table1
,unnest(array(
select as struct trim(ar[offset(0)], '"') column, trim(ar[offset(1)], '"') val
from unnest(split(trim(to_json_string(table1), '{}'))) pb,
unnest([struct(split(pb, ':') as ar)])
)) record
group by column) where non_null!=0
output:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论