BigQuery语法错误排查：STRING与INT64类型不匹配问题

错误信息 #

No matching signature for operator = for argument types: STRING, INT64. Supported signature: ANY = ANY at [15:8]

错误原因 #

连接条件station_id = subquery.start_station_id中，两个字段的数据类型不兼容：

• citibike_stations表的station_id为STRING类型
• citibike_trips表的start_station_id为INT64类型

BigQuery不允许直接用等号比较不同数据类型的值，因此触发了类型不匹配的报错。

修正方案 #

将其中一个字段的类型转换为与另一个一致即可，推荐把INT64类型的start_station_id转为STRING类型（匹配station_id的类型），修正后的SQL代码如下：

SELECT
  station_id,
  name,
  number_of_rides AS number_of_rides_starting_at_station
FROM 
  (
    SELECT 
      start_station_id,
      COUNT(*) number_of_rides
    FROM bigquery-public-data.new_york_citibike.citibike_trips
    GROUP BY 
      start_station_id
  ) subquery
  INNER JOIN bigquery-public-data.new_york_citibike.citibike_stations 
    ON station_id = CAST(subquery.start_station_id AS STRING)
ORDER BY
  number_of_rides DESC

也可以选择反过来将station_id转为INT64类型：

ON CAST(station_id AS INT64) = subquery.start_station_id

两种方式均可解决类型不匹配问题，具体选择可根据实际数据存储情况决定。

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