CODEWITHSUNDEEP
sqlgoogle-bigquery
Simon Breton
Simon Breton

Reputation: 2876

Filled partial empty column with value based on another column ID

I'm not sure my title is clear. Sorry about that. I have the following table :

ID           ref.    dummy_metrics dummy_dimensions
6091039909   A       123           foo
5914111836   B       456           bar
6538463041   C       789           foobar
6018474808   D       1010          bar
6091039909                         foo
6091039909                         foo
6538463041                         foobar
6538463041                         foobar
6538463041                         foobar

As you can see some part of my table are empty. I would like to make sur that the ref. column is always filled with something. And I want this value to be based on the ID<>ref. key-pair combination.

So here is my expected output :

ID           ref.    dummy_metrics dummy_dimensions
6091039909   A       123           foo
5914111836   B       456           bar
6538463041   C       789           foobar
6018474808   D       1010          bar
6091039909   A                     foo
6091039909   A                     foo
6538463041   C                     foobar
6538463041   C                     foobar
6538463041   C                     foobar

I don't want to have to build a pre-defined look-up table like I could do using the CASE formula. I want something dynamic for every ID<>ref. key-pair combination I can found in my table.

Upvotes: 0

Views: 43

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use correlated subquery: 

select ID, 
      coalesce(ref,  (select ref 
                      from table t1
                      where t1.ID = t.ID
                      )
              ) as ref, 
       dummy_metrics, dummy_dimensions
from table t;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Here is a pretty simple method:

select t.*,
       coalesce(t.ref,
                max(t.ref) over (partition by dummy_dimensions),
                max(t.ref) over ()
               ) as imputed_ref

This uses window functions to fill in the missing values. Note that there are two levels of imputation -- one based on dummy_dimensions and the other based on the whole table (in case the dimension ref values are all NULL).

Upvotes: 1

Related Questions