Bigquery：正则表达式提取后的 COUNT/GROUP BY

Question

我是 bigquery 的新手。我有一个表 MOVIES ，其架构如下：

[SchemaField('movieId', 'INTEGER', 'NULLABLE', None, ()),
 SchemaField('title', 'STRING', 'NULLABLE', None, ()),
 SchemaField('genres', 'STRING', 'NULLABLE', None, ())]

电影的标题如下所示： Pharaoh's Army (1995)

我想看看大多数电视剧是在哪一年拍摄的。我已经设法从标题中提取年份，并且只保留像这样的戏剧电影：

q4 = """
select 
  movies.title,
  regexp_extract(title, r'\((\d{4})\)') as year,
from assignment-344206.movie_lens_20M.MOVIES movies
where movies.genres = "Drama"
order by year desc
limit 5
"""
query_job_4 = client.query(q4)
query_job_4.to_dataframe()

现在我需要按年份分组，然后对每个组进行计数，以找到戏剧最多的年份。我尝试了这个：

q4 = """
select 
  movies.title,
  regexp_extract(title, r'\((\d{4})\)') as year,
  count(year) as nb_per_year
from assignment-344206.movie_lens_20M.MOVIES movies
where movies.genres = "Drama"
group by year
order by year desc
limit 5
"""

但我收到以下错误 BadRequest: 400 Unrecognized name:year at [5:9] 我不明白。如果没有 group by，名称 year 会按 order byyear desc 被识别，那么为什么它现在不能识别呢？

Answer 1

为了补充 Martin Weitzmann 的答案，如果您决定使用 WITH，下面是此类场景的代码/脚本实现。 with 可以用作最终查询的单个表达式中的临时表。有关 with 行为的更多详细信息，请访问 with 子句 页面。

查询：此查询显示 with 在您的案例中的用法。您可以在 BigQuery UI 上运行它。

with movies as (
    select 
        title,
        SAFE_CAST(regexp_extract(title, r'\((\d{4})\)') AS INT64) as year,
        genres
        from `projectid.datasetid.table`
)
select count(1) as movies_per_year, year
from movies
where genres = "Drama"
group by year
order by year desc

代码：将 QUERY 替换为上述查询

from google.cloud import bigquery

client = bigquery.Client()

query = """ QUERY """

query_job = client.query(query)
df = query_job.to_dataframe()
print(df.head())

输出

year | movier per year
2008 | 1
2003 | 1
2001 | 1
1994 | 2
1993 | 1
  ...

Answer 2

聚合器 COUNT() 无法知道 year，因为您只是在上面的行中将其创建为类别/组。 ORDER BY 在分组/聚合后执行，因此它可以知道字段year。

粗略的执行顺序是

1. FROM 输入表
2. JOIN 表记录
3. WHERE 字段过滤
4. GROUP BY 聚合字段
5. HAVING 聚合过滤器
6. WINDOW 函数
7. QUALIFY 窗口字段过滤
8. DISTINCT 结果字段
9. ORDER BY 结果字段
10. LIMIT 和 OFFSET 结果行
11. SELECT （输出/打印）输出字段

(像 UNION 这样的设置操作仅适用于查询结果 - 不确定在哪里列出它们 - 它们要么是第一个，要么是最后一个，具体取决于你如何看待它）

根据您的 year 中是否有 NULL 值，您可以尝试 COUNT(*) 或 COUNT(title) 甚至 < code>COUNT(regexp_extract(title, r'\((\d{4})\)'))

如果您不需要额外的正则表达式，但需要字段 year ，您可以在 CTE 中准备表格WITH 并在引用 CTE 的查询中进行分组。