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

Question

我是bigquery的新手。我有一个MOVIES表，具有以下模式：

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

电影的标题是这样的：法老的军队(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 by year 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。

粗略的执行顺序是afaik

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)，甚至COUNT(regexp_extract(title, r'((d{4}))'))

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