求帮忙排查这段BigQuery SQL代码无数据返回问题

给你几个具体的排查方向和修正方案：

1. 先验证子查询是否有数据 #

单独执行子查询，确认是否能返回结果：

SELECT
  CAST(start_station_id AS STRING)
FROM
  `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
  usertype = 'Subscriber'

如果这个查询也返回空，说明要么usertype的取值不符合实际数据（比如实际数据里是全大写SUBSCRIBER），要么该表中确实没有订阅用户的骑行记录。可以用下面的SQL确认usertype的实际取值：

SELECT DISTINCT usertype FROM `bigquery-public-data.new_york_citibike.citibike_trips`

2. 检查station_id的数据类型匹配问题 #

citibike_stations表的station_id是字符串类型，而citibike_trips表的start_station_id是整数类型。直接把整数转成字符串，可能和字符串类型的station_id格式不匹配（比如stations表的station_id带前导零，比如"0001"，而转成字符串的start_station_id是"1"，两者无法匹配）。

建议修改查询，把station_id转成整数后再匹配：

SELECT  
  station_id,
  name
FROM
  `bigquery-public-data.new_york_citibike.citibike_stations`
WHERE CAST(station_id AS INT64) IN (
  SELECT
    start_station_id
  FROM
    `bigquery-public-data.new_york_citibike.citibike_trips`
  WHERE
    usertype = 'Subscriber'   
)

3. 清理冗余的表别名 #

子查询里把citibike_trips别名为citibike_stations，和主表同名容易混淆，建议去掉或改成更清晰的别名，比如：

SELECT  
  station_id,
  name
FROM
  `bigquery-public-data.new_york_citibike.citibike_stations`
WHERE CAST(station_id AS INT64) IN (
  SELECT
    start_station_id
  FROM
    `bigquery-public-data.new_york_citibike.citibike_trips` AS trips
  WHERE
    trips.usertype = 'Subscriber'   
)

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