Bigquery - Average between columns ignoring NULL values

user_12699191 0
Viewed 1016

TL;DR: Is there an easy way to calculate the average between a group of columns on google's bigquery?

I have a table with many estimates from a continuous variable, I'm giving an example with only three columns but the original table have something between 8 columns:

Estimate_A Estimate_B Estimate_C
4 2 3
1 2 2
4 NULL 2
2 3 NULL
4 NULL NULL

I want to produce a new column AVG_ESTIMATE which is an AVERAGE between these estimate columns but ignoring the NULL data

Estimate_A Estimate_B Estimate_C AVG_ESTIMATE
4 2 3 3
1 2 2 1.66
4 NULL 2 3
2 3 NULL 2.5
4 NULL NULL 4
google-bigquery sql
3 Answers

Consider below solution

select *, 
  (select round(avg(Estimate), 2) 
  from unnest([Estimate_A, Estimate_B, Estimate_C]) Estimate
  ) as AVG_ESTIMATE
from `project.dataset.table`

If applied to sample data in your question - output is

enter image description here

user_52219440

This isn't an elegent solution by any means, but would probably work:

SELECT 
   Estimate_A,
   Estimate_B,
   Estimate_C,

--Add values of each column, divide by number of non-null columns

   (
    IFNULL(Estimate_A,0) +
    IFNULL(Estimate_B,0) +
    IFNULL(Estimate_C,0)
   ) 
   / 
   (
    IF(Estimate_A IS NULL, 0,1) + 
    IF(Estimate_B IS NULL, 0,1) + 
    IF(Estimate_C IS NULL, 0,1)
    )  AS AVG_ESTIMATE
user_47749600

Below is most generic solution - no need in referencing column names and works for any number of columns

select *, 
  (select round(avg(cast(split(kv, ':')[offset(1)] as float64)), 2)
  from unnest(split(trim(to_json_string(t), '{}'))) kv
  where split(kv, ':')[offset(1)] != 'null'
  ) as AVG_ESTIMATE
from `project.dataset.table` t

if applied to sample data in your question - output is

enter image description here

Below is slightly refactored version of above just to eliminate some redundency

select *, 
  (select round(avg(cast(value as float64)), 2)
  from unnest(split(trim(to_json_string(t), '{}'))) kv,
  unnest([split(kv, ':')[offset(1)]]) value
  where value != 'null'
  ) as AVG_ESTIMATE
from `project.dataset.table` t
user_52219440
Related