BigQuery联表报错：STRING与INT64类型不匹配，求排查方案

报错提示No matching signature for operator = for argument types: STRING, INT64，核心原因是JOIN条件里的两个字段类型不兼容：

• bigquery-public-data.new_york.citibike_stations表中的station_id是STRING类型
• bigquery-public-data.new_york.citibike_trips表中的start_station_id是INT64类型

你只检查了station_id的类型，却忽略了子查询中start_station_id的整数类型，导致无法直接匹配。

两种可行的修改方案： #

方案一：将子查询中的start_station_id转换为STRING类型

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

方案二：在JOIN条件中将station_id转换为INT64类型

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

注意：方案二需要确保station_id字段全部由纯数字组成，否则转换INT64会报错。因此优先推荐方案一，因为start_station_id本身是整数，转换为STRING不会出现异常。

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