使用谷歌BigQuery的逗号作为UNION ALL与IN子句

Question

我试图执行以下查询：

SELECT 
    author, link_id, COUNT(link_id) as cnt 
FROM 
    [fh-bigquery:reddit_comments.2015_12], 
    [fh-bigquery:reddit_comments.2015_11] 
WHERE link_id IN (
    SELECT posts.name 
    FROM [fh-bigquery:reddit_posts.full_corpus_201512] AS posts 
    WHERE posts.subreddit = 'politics' 
    ORDER BY posts.created_utc DESC 
    LIMIT 300 
) 
GROUP BY author, link_id 
ORDER BY author 

我在执行时收到此错误信息：JOIN（包括半连接）和UNION ALL（逗号，日期范围）可能不会合并到一个SELECT语句中。将UNION ALL移至内部查询或将JOIN移至外部查询。

删除其中一个comments表格可以正常工作，但我似乎无法弄清楚BigQuery的Comma as UNION ALL的工作原理。我试图将联合移动到内部查询，但我仍然得到相同的错误。

Answer 1

错误是我误解将UNION ALL移动到内部查询。解决错误，我不得不把这两个表放在一个基本的select * from ...。工作查询如下：

SELECT 
    author, link_id, COUNT(link_id) as cnt 
FROM (
    SELECT * 
    FROM 
    [fh-bigquery:reddit_comments.2015_12], 
    [fh-bigquery:reddit_comments.2015_11] 
) 
WHERE link_id IN (
    SELECT posts.name 
    FROM [fh-bigquery:reddit_posts.full_corpus_201512] AS posts 
    WHERE posts.subreddit = 'politics' 
    ORDER BY posts.created_utc DESC 
    LIMIT 300 
) 
GROUP BY author, link_id 
ORDER BY author