BigQuery已加括号的嵌套查询仍报括号缺失问题咨询

BigQuery执行嵌套SQL时抛出如下报错：

Each function argument is an expression, not a query; to use a query as an expression, the query must be wrapped with additional parentheses to make it a scalar subquery expression at [3:1]

原始执行代码如下：

select station_id, name, number_of_rides
    (
    select start_station_id, count(*) as number_of_rides
    from `bigquery-public-data.new_york_citibike.citibike_trips`
    group by start_station_id
    ) 
from `bigquery-public-data.new_york_citibike.citibike_trips` 
inner join `bigquery-public-data.new_york_citibike.citibike_stations`
on station_id=start_station_id
order by numer_of_rides desc

• 直接触发报错的语法问题：SELECT字段列表中number_of_rides后缺失逗号，SQL解析器会将后续括号包裹的子查询识别为传入number_of_rides函数的参数，而函数参数只支持表达式、不支持查询结构，因此抛出括号相关的错误提示。
• 逻辑写法不符合语法规则：即使补全逗号，写在SELECT子句位置的子查询会按站点分组返回多列（start_station_id、number_of_rides）、多行结果，不符合标量子查询的要求——只有返回单行单列单个值的子查询才能直接作为SELECT列表的字段使用，多列多行的查询结果不能直接放在该位置。
• 附加错误点：末尾排序字段将number_of_rides错写为numer_of_rides，即使修复前两个问题，该处也会触发字段不存在的报错；同时原写法直接以全量骑行记录表作为主表关联站点表，会返回和骑行记录数等量的重复站点数据，不符合统计每个站点骑行量的需求。

将站点骑行量的聚合逻辑提前作为派生表，再关联站点表获取站点名称即可正常运行，修正后代码：

select 
  s.station_id,
  s.name,
  t.number_of_rides
from (
  -- 先聚合计算每个起始站点的总骑行次数
  select
    start_station_id,
    count(*) as number_of_rides
  from `bigquery-public-data.new_york_citibike.citibike_trips`
  group by start_station_id
) t
-- 关联站点表匹配站点名称
inner join `bigquery-public-data.new_york_citibike.citibike_stations` s
  on s.station_id = t.start_station_id
order by t.number_of_rides desc

内容的提问来源于stack exchange，提问作者SATHWIK