CODEWITHSUNDEEP
sqlgoogle-bigquery
HKE
HKE

Reputation: 473

Cross multiplying two tables that have same schema using BigQuery

There are two tables(Table A and Table B) with same schema

I want to multiply TableA.ColumnB*TableB.ColumnB and so on. I can do it by joining the two tables and later on by multiplying each column as shown below

select TableA,
      (TableA.ColumnB*TableB.ColumnB) as Column B,
      (TableA.ColumnC*TableB.ColumnC) as Column C
from Table A
  join Table B on TableA.ColumnA = TableB.ColumnA

As there are many columns that need to multiplied, I am looking for some easy way to do using Big Query. Something like TableA * TableB(so that same columns on both the table gets multiplied)

Thanks

Upvotes: 0

Views: 357

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Is there any simple way like Table A* Table B?
Not as of what I know for BigQuery

My recommendation would be to build utility query that will create the proper query for you with all 100s columns involved
Of course this can be easily done with any tool, but if you want to stay within BigQuery - below is the option for BigQuery Standard SQL

It builds that part of your query where you need to enlist all

(TableA.ColumnX*TableB.ColumnXB) as Column X,

#standardSQL
SELECT 
  CONCAT(
    'SELECT a.ColumnA AS ColumnA, \n',
    STRING_AGG(CONCAT(
      '\ta.', SPLIT(kv_a, ':')[SAFE_OFFSET(0)], ' * ', 
      'b.', SPLIT(kv_b, ':')[SAFE_OFFSET(0)], 
      ' AS ', SPLIT(kv_a, ':')[SAFE_OFFSET(0)]), 
      ' , \n'), ' \n',
    'FROM TableA a JOIN TableB b ON a.ColumnA = b.ColumnA'
  ) AS query_string  
FROM (
  SELECT 
    1 AS grp,
    SPLIT(REGEXP_REPLACE(TO_JSON_STRING(a), '["{}]', '')) AS kvs_a,
    SPLIT(REGEXP_REPLACE(TO_JSON_STRING(b), '["{}]', '')) AS kvs_b
  FROM (SELECT * FROM TableA LIMIT 1) a
  JOIN TableB b
  ON a.ColumnA = b.ColumnA
  LIMIT 1
) 
CROSS JOIN UNNEST(kvs_a) kv_a WITH OFFSET pos_a
CROSS JOIN UNNEST(kvs_b) kv_b WITH OFFSET pos_b
WHERE pos_a = pos_b AND pos_a > 0
GROUP BY grp

if your "environment" respects \n and \t - you will get below as a result (assuming three columns in tables as in your question - but it will work exactly same for 100 columns) 

query_string
------------
SELECT a.ColumnA AS ColumnA,   
  a.ColumnB * b.ColumnB AS ColumnB ,   
  a.ColumnC * b.ColumnC AS ColumnC   
FROM TableA a JOIN TableB b ON a.ColumnA = b.ColumnA

So, now you can copy result of utility query and run it as your final query

As you might noticed - this approach is based on columns positions, but if you have same columns names in both tables - you can simplify utility query by removing join - to something like below 

#standardSQL
SELECT 
  CONCAT(
    'SELECT a.ColumnA AS ColumnA, \n',
    STRING_AGG(CONCAT(
      '\ta.', SPLIT(kv_a, ':')[SAFE_OFFSET(0)], ' * ', 
      'b.', SPLIT(kv_a, ':')[SAFE_OFFSET(0)], 
      ' AS ', SPLIT(kv_a, ':')[SAFE_OFFSET(0)]), 
      ' , \n'), ' \n',
    'FROM TableA a JOIN TableB b ON a.ColumnA = b.ColumnA'
  ) AS query_string  
FROM (
  SELECT 
    1 AS grp,
    SPLIT(REGEXP_REPLACE(TO_JSON_STRING(a), '["{}]', '')) AS kvs_a
  FROM (SELECT * FROM TableA LIMIT 1) a
) 
CROSS JOIN UNNEST(kvs_a) kv_a WITH OFFSET pos_a
WHERE pos_a > 0
GROUP BY grp

Upvotes: 1

Related Questions