BigQuery，在BigQuery黑客新闻数据集中加入评论和故事

Question

请问在BigQuery上，HackerNews数据集中的comments和stories表是如何相关的？这看起来并不明显。

Answer 1

​

​

BigQuery标准SQL快速示例/入门

SELECT s.id, c.text
FROM `bigquery-public-data.hacker_news.stories` s
JOIN `bigquery-public-data.hacker_news.comments` c
ON s.id = c.parent
ORDER BY id
LIMIT 100  

您可能会考虑改用bigquery-public-data.hacker_news.full表，它是Hacker News中所有故事和评论的完整每日更新，并且该评论的层次结构是另一条评论的父级，等等

​

​

快速检查：

SELECT p.text parent, c.text child
FROM `bigquery-public-data.hacker_news.full` p
JOIN `bigquery-public-data.hacker_news.full` c
ON c.parent = p.id 
WHERE c.type = 'comment' AND p.type = 'comment' AND NOT p.text IS NULL
ORDER BY parent
LIMIT 55

Answer 2

它们在comments.parent = stories.id上连接

SELECT
  s.title, c.*
FROM
  `bigquery-public-data.hacker_news.comments` c,
  `bigquery-public-data.hacker_news.stories` s
WHERE
  c.parent = s.id