代码之家 › 专栏 › 技术社区 › melatonin

在同一个查询中查询每日聚合和每月聚合?

google-bigquery sql

melatonin · 技术社区 · 7 年前

我想按subreddit和day计算每日唯一活跃用户的数量,然后按组和月份将这些数量汇总到每月唯一活跃用户上。单独执行每个操作非常简单,但当我尝试在一个组合查询中执行这些操作时,它会告诉我需要在第二级子查询中按日期、月份和日期分组,这将导致每月的唯一用户与每日的唯一作者相同(错误:表达式“date\u month\u day”不在分组依据列表[invalidQuery]中)。

以下是我到目前为止的疑问:

SELECT * FROM
          (
              SELECT *,
                 (daily_unique_authors/monthly_unique_authors) * 1.0 AS ratio,
                 ROW_NUMBER() OVER (PARTITION BY date_month_day ORDER BY ratio DESC) rank 
                 FROM 
                     (
                      SELECT subreddit,
                            date_month_day,
                            daily_unique_authors,
                            SUM(daily_unique_authors) AS monthly_unique_authors,
                            LEFT(date_month_day, 7) as date_month
                            FROM 
                                  (
                                    SELECT subreddit,
                                           LEFT(DATE(SEC_TO_TIMESTAMP(created_utc)), 10) as date_month_day,
                                           COUNT(UNIQUE(author)) as daily_unique_authors
                                    FROM TABLE_QUERY([fh-bigquery:reddit_comments], "table_id CONTAINS \'20\' AND LENGTH(table_id)<8")
                                    GROUP EACH BY subreddit, date_month_day
                                  )
                            GROUP EACH BY subreddit, date_month))

     WHERE rank <= 100
     ORDER BY date_month ASC

理想情况下,最终输出应该是:

subreddit date_month date_month_day daily_unique_users         monthly_unique_users ratio  

 1 google 2005-12    2005-12-29                       77                    600     0.128     
 2 google 2005-12    2005-12-31                       52                     600     0.866    
 3 google 2005-12    2005-12-28                       81                     600     0.135    
 4 google 2005-12    2005-12-27                       73                     600     0.121

1 回复 | 直到 7 年前

Mikhail Berlyant 7 年前

下面是BigQuery标准SQL

#standardSQL
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY date_month_day ORDER BY ratio DESC) rank 
  FROM (
    SELECT 
      daily.subreddit subreddit, 
      daily.date_month date_month, 
      date_month_day, 
      daily_unique_authors, 
      monthly_unique_authors,
      1.0 * daily_unique_authors / monthly_unique_authors AS ratio
    FROM (
      SELECT subreddit,
        DATE(TIMESTAMP_SECONDS(created_utc)) AS date_month_day,
        FORMAT_DATE('%Y-%m', DATE(TIMESTAMP_SECONDS(created_utc))) AS date_month,
        COUNT(DISTINCT author) AS daily_unique_authors
      FROM `fh-bigquery.reddit_comments.2018*`
      GROUP BY subreddit, date_month_day, date_month
    ) daily
    JOIN (
      SELECT subreddit,
        FORMAT_DATE('%Y-%m', DATE(TIMESTAMP_SECONDS(created_utc))) AS date_month,
        COUNT(DISTINCT author) AS monthly_unique_authors
      FROM `fh-bigquery.reddit_comments.2018*`
      GROUP BY subreddit, date_month
    ) monthly 
    ON daily.subreddit = monthly.subreddit
    AND daily.date_month = monthly.date_month
  )
)
WHERE rank <= 100
ORDER BY date_month

注:我尽量保留问题中的原始逻辑和结构,以便OP能够将答案与问题关联起来,并在需要时进行进一步调整:o)