sqlgoogle-cloud-platformgoogle-bigquery

BigQuery : is it possible to iterate over an array?


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'`

Solution

  • 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