Is it possible to iterate over an array in bigquery in standard sql?
Basically declare an array of strings representing table fields. ex :
DECLARE FIELDS_TO_CHECK ARRAY<STRING>;
SET FIELDS_TO_CHECK = ['field1', 'field2', 'field3' ];
and then iterate on this array to create requests getting percentage of non null value on this field :
select count(FIELD) / count(*) from
'table_name'`
Below is example for BigQuery Standard SQL
I am using here TEMP TABLE `table_name` to mimic your data with some simplistic dummy data, but you can just remove that CREATE statement and use your own table
#standardSQL
DECLARE FIELDS_TO_CHECK ARRAY<STRING>;
DECLARE i INT64 DEFAULT 0;
CREATE TEMP TABLE `table_name` AS
SELECT 1 field1, NULL field2, 3 field3, 4 field4, 5 field5 UNION ALL
SELECT NULL, NULL, 3, NULL, 5 UNION ALL
SELECT 1, NULL, 3, 4, 6;
CREATE TEMP TABLE result(field STRING, percentage FLOAT64);
SET FIELDS_TO_CHECK = ['field1', 'field2', 'field3' ];
LOOP
SET i = i + 1;
IF i > ARRAY_LENGTH(FIELDS_TO_CHECK) THEN
LEAVE;
END IF;
EXECUTE IMMEDIATE '''
INSERT result
SELECT "''' || FIELDS_TO_CHECK[ORDINAL(i)] || '''", COUNT(''' || FIELDS_TO_CHECK[ORDINAL(i)] || ''') / COUNT(*) FROM `table_name`
''';
END LOOP;
SELECT * FROM result;
Above example returns below output
Row field percentage
1 field2 0.0
2 field1 0.66666666666666663
3 field3 1.0