Combine three Big Query tables

Question

I have pulled data from Google Analytics into several Big Query tables. Google Analytics limits the numbers of dimensions to 7 and the number of metrics to 10.

I have three tables, each with the same 7 dimensions. I have a total of 30 metrics (10 in each table).

How do I combine these tables into one table? JOIN or UNION doesn't seem to be the correct way of doing this, as all of the 'dimensions' have the same names, and I don't want to append additional row; basically just add 20 additional columns to the first table based on the dimension 'keys'?

Some (simplified) sample data and desired results would help. Do the tables overlap? How do you combine the metrics when they do? — Gordon Linoff
@J.Ayo Thanks for your question. If you're satisfied with the answer - it would be great if you'll mark any answer as correct. If you still need some answer improvements - feel free to ask. — Stas Buzuluk

Stas Buzuluk Stas Buzuluk · Accepted Answer · 2019-12-20T13:32:27

If I understood you correct you have the following:

Table 1 (only 3 samples here, instead of 10):

SELECT 'metric_name_1' as metric_name, 
       'val_1' as dim_1, 
       'val_2' as dim_2, 
       'val_3' as dim_3,
       'val_4' as dim_4, 
       'val_5' as dim_5, 
       'val_6' as dim_6, 
       'val_7' as dim_7
 UNION ALL 
 SELECT 'metric_name_2', 
        'val_8', 
        'val_9',
        'val_10', 
        'val_11', 
        'val_12', 
        'val_13', 
        'val_14'
 UNION ALL 
 SELECT 'metric_name_3',
        'val_15',
        'val_16', 
        'val_17', 
        'val_18', 
        'val_19',
        'val_20',
        'val_21'

First table screenshot

And the second table:

SELECT 'metric_name_4' as metric_name, 
       'val_22' as dim_1, 
       'val_23' as dim_2, 
       'val_24' as dim_3,
       'val_25' as dim_4, 
       'val_26' as dim_5, 
       'val_27' as dim_6, 
       'val_28' as dim_7
UNION ALL 
SELECT 'metric_name_5', 
       'val_29', 
       'val_30',
       'val_31', 
       'val_32', 
       'val_33', 
       'val_34', 
       'val_35'
UNION ALL 
SELECT 'metric_name_6',
       'val_36',
       'val_37', 
       'val_38', 
       'val_39', 
       'val_40',
       'val_41',
       'val_42'

Second table screenshot

And from this, you want to receive a table like this:

Resulting table

It's not too trivial and may be achieved in two steps:

resulting_join as (select first_table,second_table  from first_table join second_table on True)
SELECT * EXCEPT (pos, pos_1)
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY first_table.metric_name) AS pos, 
            ROW_NUMBER() OVER (PARTITION BY second_table.metric_name) AS pos_1,
  FROM resulting_join
)
WHERE pos = pos_1

I think that it's much better to just use UNION ALL in such cases and add new rows. Hope it helped - if you'll need some further explanation - I'll be glad to respond.

Combine three Big Query tables

2 Answers