BigQuery报Syntax error错误：查找共享单车最高频出发站SQL语法排查

错误原因 #

• SELECT子句中trips.start_station_id与COUNT(*)两个返回字段之间缺少英文逗号分隔，SQL解析器判定第一个字段定义结束后，后续的(符号不符合语法规则，直接触发你收到的报错提示。
• COUNT(*)未设置别名为trip_ct，即使修复逗号问题，后续ORDER BY trip_ct语句也会因为找不到对应字段触发二次报错。
• CTE临时表定义与后续主查询属于同一段SQL的两个组成部分，不可拆分执行，需要合并为同一语句提交。

修复后的完整可执行代码 #

WITH 
    longest_used_bike AS (
        SELECT 
            bikeid,
            SUM(duration_minutes) AS trip_duration
        FROM 
            bigquery-public-data.austin_bikeshare.bikeshare_trips
        GROUP BY 
            bikeid
        ORDER BY 
            trip_duration DESC 
        LIMIT 1
    )
-- 查找累计使用时长最长的单车最常出发的站点
SELECT 
    trips.start_station_id,
    COUNT(*) AS trip_ct
FROM 
    longest_used_bike AS longest
INNER JOIN 
    bigquery-public-data.austin_bikeshare.bikeshare_trips AS trips
    ON longest.bikeid = trips.bikeid
GROUP BY 
    trips.start_station_id
ORDER BY 
    trip_ct DESC 
LIMIT 1

内容的提问来源于stack exchange，提问作者José Albelo