联接表结果Google BigQuery
联接表结果Google BigQuery
提问于 2017-04-21 02:36:29
我有两个SQL查询：
SELECT subreddit, count(subreddit) as count
FROM [fh-bigquery:reddit_comments.all]
where author="***********" GROUP by subreddit ORDER BY count DESC;和
SELECT subreddit, count(subreddit) as count
FROM [redditcollaborativefiltering:aggregate_comments.reddit_posts_all]
where author="***********" GROUP by subreddit ORDER BY count DESC;我希望能够将这两个查询的结果合并到一个具有相同列的结果中，但是，计数是彼此相加的。有什么简单的方法吗？
回答 2
Stack Overflow用户
回答已采纳
发布于 2017-04-21 04:06:21
对于BigQuery遗留SQL (我看到您在示例中使用的)，可以在下面使用：
#legacySQL
SELECT subredit, SUM(cnt) as cnt
FROM (SELECT subreddit, COUNT(subreddit) as cnt
FROM [fh-bigquery:reddit_comments.all]
WHERE author = '***********'
GROUP BY subreddit
),
(SELECT subreddit, COUNT(subreddit) as cnt
FROM [redditcollaborativefiltering:aggregate_comments.reddit_posts_all]
WHERE author = '***********'
GROUP by subreddit
)
GROUP BY subreddit
ORDER BY cnt DESC 如您所见，Legacy SQL中的逗号用作UNION
以上可以进一步简化。
#legacySQL
SELECT subreddit, COUNT(subreddit) as cnt
FROM [fh-bigquery:reddit_comments.all],
[redditcollaborativefiltering:aggregate_comments.reddit_posts_all]
WHERE author = '***********'
GROUP BY subreddit
ORDER BY cnt DESC您可以阅读更多有关Comma as UNION ALL for BigQuery Legacy SQL的信息。
Stack Overflow用户
发布于 2017-04-21 02:46:02
您可以使用UNION ALL和另一个聚合：
SELECT subredit, SUM(cnt) as cnt
FROM ((SELECT subreddit, count(subreddit) as cnt
FROM [fh-bigquery:reddit_comments.all]
WHERE author = '***********'
GROUP BY subreddit
) UNION ALL
(SELECT subreddit, count(subreddit) as cnt
FROM [redditcollaborativefiltering:aggregate_comments.reddit_posts_all]
WHERE author = '***********'
GROUP by subreddit
)
) sc
GROUP BY subreddit
ORDER BY cnt DESC;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/43533006
复制相关文章
相似问题
腾讯云开发者
