CODEWITHSUNDEEP
google-bigquery
Canovice
Canovice

Reputation: 10163

BigQuery remove duplicate column names when joining on FULL JOIN + USING

I have two BigQuery tables that have exactly the same column names, that need to be joined together, while preserving the correct names for the stats. In my actual example, my tables are named offensive and defensive, and they contain offensive and defensive basketball statistics for teams. The column names in both tables are:

team, game, fga, fgm, ast, blk, stk

I want to join the columns on the columns team and game. I found the following example, which looks perfect for my use case, however one thing I do not like is that the example doens't remove the duplicate joined-on columns (company, vendor, transaction in the example). After joining, I don't want 2 of team and game in my table. I tried:

SELECT offensive, defensive EXCEPT (team, game)
FROM offensive FULL JOIN defensive
USING (team, game)

...and am receiving the following Syntax error: Unexpected keyword EXCEPT at [40:39] error. Any thoughts on how to remove the duplicate joined-on columns would be helpful.

Upvotes: 2

Views: 2719

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172964

Below is for BigQuery Standard SQL 

#standardSQL
SELECT team, game, 
  (SELECT AS STRUCT offensive.*  EXCEPT (team, game)) AS offensive,
  (SELECT AS STRUCT defensive.*  EXCEPT (team, game)) AS defensive
FROM offensive FULL JOIN defensive
USING (team, game)

this will result in below schema 

team game offensive.fga offensive.fgm offensive.ast offensive.blk offensive.stk defensive.fga defensive.fgm defensive.ast defensive.blk defensive.stk

something like 

[
  {
    "team": "1",
    "game": "1",
    "offensive": {
      "fga": "1",
      "fgm": "2",
      "ast": "3",
      "blk": "4",
      "stk": "5"
    },
    "defensive": {
      "fga": "21",
      "fgm": "22",
      "ast": "23",
      "blk": "24",
      "stk": "25"
    }
  }
]

Upvotes: 3

Related Questions