CODEWITHSUNDEEP
sqljoininner-joingoogle-bigqueryreddit
Kevbot
Kevbot

Reputation: 494

How to use BigQuery's union all with an inner join?

I am attempting to join all comment tables (shards of comments each month) to a posts table. Is there a way for me to perform a union all before the inner join? Details on the union all operator can be found in the documentation here. My query with only 1 of the comments table is as follows:

SELECT c.score, c.body, c.link_id, c.parent_id, p.created_utc, c.created_utc
FROM [fh-bigquery:reddit_comments.2016_01] AS c
INNER JOIN [fh-bigquery:reddit_posts.full_corpus_201512] AS p
ON c.parent_id = p.name
WHERE SUBSTR(c.parent_id, 1, 2) = 't3'
ORDER BY c.score DESC
LIMIT 10

Upvotes: 1

Views: 250

Answers (2)

Kevbot
Kevbot

Reputation: 494

As Mikhail Berlyant pointed out in his answer, modifying the query as such accomplished what I needed.

SELECT c.score, c.body, c.link_id, c.parent_id, p.created_utc, c.created_utc, (c.created_utc - p.created_utc) AS time_diff
FROM (
  SELECT * 
  FROM 
    [fh-bigquery:reddit_comments.2015_11],
    [fh-bigquery:reddit_comments.2015_12],
    [fh-bigquery:reddit_comments.2016_01],
) AS c
INNER JOIN [fh-bigquery:reddit_posts.full_corpus_201512] AS p
ON c.parent_id = p.name
WHERE SUBSTR(c.parent_id, 1, 2) = 't3'
ORDER BY c.score DESC
LIMIT 100

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Replace 

FROM [fh-bigquery:reddit_comments.2016_01] AS c

with 

FROM (
  SELECT score, body, link_id, parent_id, created_utc 
  FROM (TABLE_QUERY([fh-bigquery:reddit_comments], 
                    'REGEXP_MATCH(table_id, r"\d{4}_\d{2}")')) 
) AS c

Hope, this gives you idea
See more on Table wildcard functions and Regular expression functions

Upvotes: 1

Related Questions