Computing medians of columns and storing them in BQ table

user_3385867 0
Viewed 56

I have a table in the form

COL_1 | COL_2 | .... | COL_N
------------------------------
1     | 4     | ...  | 5
------------------------------
4     | 8     | ...  | 4
------------------------------
5     | 9     | ...  | 9
------------------------------

The goal is to iterate over the N columns and create N additional columns that contain the medians of the N columns (same for all rows). Below is the result

COL_1 | COL_2 | .... | COL_N  | MCOL_1 | MCOL_2 | .... | MCOL_N
----------------------------------------------------------------
1     | 4     | ...  | 5      | 4      | 8      | .... | 4
----------------------------------------------------------------
4     | 8     | ...  | 4      | 4      | 8      | .... | 4
----------------------------------------------------------------
5     | 9     | ...  | 9      | 4      | 8      | .... | 4
----------------------------------------------------------------

I see a lot of examples iterating over rows but not much on columns. What is a good way to go about it considering the table in Bigquery is quite big, so ideally want to avoid joins?

google-bigquery sql
1 Answers

Consider below approach - works for any number on col_N columns

execute immediate (select '''
  select *, ''' || 
    string_agg('percentile_cont(' || col || ', 0.5) over() as m' || col )
  || ''' from `project.dataset.table`
  '''
  from (
    select split(kv, ':')[offset(0)] col
    from (
      select translate(to_json_string(t), '{}"', '') kvs
      from `project.dataset.table` t limit 1
    ), unnest(split(kvs)) kv
  )
)

If applied to dummy data as below

enter image description here

output is

enter image description here

user_52219440
Related