如何在BigQuery SQL中关联一对多表生成邮编-温度映射数据

要完成邮编到对应县温度的映射，核心是通过两张表的县编码关联，将weather表的县温度匹配到zipcodes表的所有对应邮编上，具体实现如下：

核心思路 #

zipcodes表的county_code是weather表中state_id与county_id的拼接结果，因此我们可以在关联时，将weather的两个字段拼接成与county_code一致的格式，以此作为关联条件。

完整SQL语句 #

SELECT
  z.zip,
  w.temperature
FROM
  `你的项目ID.你的数据集ID.zipcodes` z
INNER JOIN
  `你的项目ID.你的数据集ID.weather` w
ON
  -- 拼接规则需与zipcodes表中county_code的生成规则完全一致
  z.county_code = CONCAT(w.state_id, w.county_id)

关键细节说明 #

• 替换项目和数据集ID：将语句中的你的项目ID.你的数据集ID替换为你实际的BigQuery项目和数据集名称。
• 处理拼接歧义：如果state_id和county_id的拼接可能产生歧义（比如state_id='CA'+county_id='12'与state_id='C'+county_id='A12'拼接结果相同），需确认zipcodes表的county_code是否使用了分隔符（比如连字符），若是则调整关联条件：

  z.county_code = CONCAT(w.state_id, '-', w.county_id)
  

• 关联类型选择：
  • 使用INNER JOIN：仅返回有对应温度数据的邮编记录。
  • 使用LEFT JOIN：保留所有邮编记录，无对应温度的邮编，temperature字段会显示为NULL。

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