BigQuery中的reddit数据集-查询flair？

Question

我知道BigQuery中有一堆编辑评论和故事--这是由pushshift.io的Jason收集的。

如何查询此数据集以获得减法编辑的flair列表？

这是我的基本查询：

SELECT link_flair_text 
FROM `fh-bigquery.reddit_posts.2019_08` 
WHERE subreddit  = 'AmItheAsshole'

Answer 1

让我们构建这个可视化：

​

​

• https://twitter.com/felipehoffa/status/1223278090958209025

这样的查询就可以了--这个查询将得到/r/AmITheAsshole上的帖子数，按照大多数典型的判断顺序排列：

SELECT link_flair_text, COUNT(*) c 
FROM `fh-bigquery.reddit_posts.2019_08` 
WHERE subreddit  = 'AmItheAsshole'
GROUP BY 1
ORDER BY 2 DESC 
LIMIT 1000

​

​

要查询几个月的数据，可以在*上使用FROM

SELECT link_flair_text, COUNT(*) c 
FROM `fh-bigquery.reddit_posts.2019_*` 
WHERE subreddit  = 'AmItheAsshole'
GROUP BY 1
ORDER BY 2 DESC 
LIMIT 1000

​

​

并观察每月百分比的变化情况：

SELECT link_flair_text, ARRAY_AGG(STRUCT(month, ROUND(100*c/total_month,1) AS perc, c) ORDER BY month) AS arr
FROM (
  SELECT *, SUM(c) OVER(PARTITION BY month) total_month
  FROM (
    SELECT link_flair_text, DATE(TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(created_utc), MONTH)) month, COUNT(*) c
    FROM `fh-bigquery.reddit_posts.2019_*` 
    WHERE subreddit  = 'AmItheAsshole'
    AND link_flair_text IS NOT NULL
    GROUP BY 1,2 
    HAVING c > 100
  )
)
GROUP BY 1
ORDER BY SUM(c) DESC

​

​

以及上面所示的viz的SQL：

CREATE OR REPLACE TABLE `temp.assholes`
AS
SELECT *, c/total_month AS perc
FROM (
  SELECT *, SUM(c) OVER(PARTITION BY month) total_month, SUM(c) OVER(PARTITION BY link_flair_text) total_flair
  FROM (
    SELECT LOWER(link_flair_text) link_flair_text, DATE(TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(created_utc), MONTH)) month, COUNT(*) c
    FROM `fh-bigquery.reddit_posts.201*` 
    WHERE subreddit = 'AmItheAsshole'
    AND link_flair_text IS NOT NULL
    AND _table_suffix >= '8_03'
    GROUP BY 1,2 
  )
)
WHERE c/total_month > 0.01
AND total_flair > 1000
ORDER BY month

如何在2016年前带来数据？

几乎相同的查询，但它包含了一个不同的表，其中包含了2016年以前的所有历史内容：

WITH data AS (
  SELECT * FROM `fh-bigquery.reddit_posts.201*` WHERE _table_suffix >= '5_12'
  UNION ALL  
  SELECT * FROM `fh-bigquery.reddit_posts.full_corpus_201512`  
)

SELECT *, c/total_month AS perc
FROM (
  SELECT *, SUM(c) OVER(PARTITION BY month) total_month, SUM(c) OVER(PARTITION BY link_flair_text) total_flair
  FROM (
    SELECT LOWER(link_flair_text) link_flair_text, DATE(TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(created_utc), MONTH)) month, COUNT(*) c
    FROM data
    WHERE subreddit  = 'AmItheAsshole'
    AND link_flair_text IS NOT NULL
    GROUP BY 1,2 
  )
)
WHERE c/total_month > 0.01
AND total_flair > 1000
ORDER BY month