基于条件关联BigQuery双表：多车型版本匹配需求及代码请求

核心思路 #

通过版本要求映射表（WITH子句定义）关联车辆信息表与更新记录表，实现可扩展的多条件版本校验，避免冗长的CASE WHEN嵌套，便于后续新增产品线规则。

代码示例 #

方案1：可扩展的版本规则映射（推荐） #

此方案将所有产品线的版本校验规则集中管理，新增规则只需添加UNION ALL条目，维护成本低：

WITH version_requirements AS (
  -- 定义各产品线规则：model + model_year → 目标版本 + 指定app版本
  SELECT 'xxx' AS model, 'My21' AS model_year, '3.2.1' AS target_version, '1.0' AS app_version UNION ALL
  SELECT 'xyz' AS model, 'My22' AS model_year, '5.6.0' AS target_version, '1.3' AS app_version UNION ALL
  SELECT 'abc' AS model, 'My23' AS model_year, '4.0.0' AS target_version, '1.5' AS app_version -- 新增规则直接追加
)
SELECT
  vi.VehicleNum,
  vi.model,
  vi.model_year,
  up.Version AS installed_version,
  up.time AS update_time,
  vr.app_version
FROM
  `your-project.your-dataset.Vehicle_Information` vi
-- 关联版本规则表，匹配对应产品线的校验要求
LEFT JOIN version_requirements vr
  ON vi.model = vr.model AND vi.model_year = vr.model_year
-- 关联更新表，筛选已安装目标版本的记录
LEFT JOIN `your-project.your-dataset.Updates` up
  ON vi.VehicleNum = up.vehicleNum AND up.Version = vr.target_version
-- 可选：仅保留已成功安装目标版本的车辆
-- WHERE up.Version IS NOT NULL

方案2：CASE WHEN直接定义规则（适合少量规则场景） #

若产品线规则较少，可直接用CASE WHEN实现：

SELECT
  vi.VehicleNum,
  vi.model,
  vi.model_year,
  up.Version AS installed_version,
  up.time AS update_time,
  CASE
    WHEN vi.model = 'xxx' AND vi.model_year = 'My21' AND up.Version = '3.2.1' THEN '1.0'
    WHEN vi.model = 'xyz' AND vi.model_year = 'My22' AND up.Version = '5.6.0' THEN '1.3'
    WHEN vi.model = 'abc' AND vi.model_year = 'My23' AND up.Version = '4.0.0' THEN '1.5'
    ELSE NULL -- 不符合规则的返回NULL
  END AS app_version
FROM
  `your-project.your-dataset.Vehicle_Information` vi
LEFT JOIN `your-project.your-dataset.Updates` up
  ON vi.VehicleNum = up.vehicleNum
-- 可选：仅保留符合规则的记录
WHERE
  (vi.model = 'xxx' AND vi.model_year = 'My21' AND up.Version = '3.2.1')
  OR (vi.model = 'xyz' AND vi.model_year = 'My22' AND up.Version = '5.6.0')
  OR (vi.model = 'abc' AND vi.model_year = 'My23' AND up.Version = '4.0.0')

关键注意事项 #

• 字段大小写匹配：两张表的车辆编号字段分别为VehicleNum和vehicleNum，BigQuery默认区分大小写，若数据中存在大小写不一致情况，需用LOWER(vi.VehicleNum) = LOWER(up.vehicleNum)做关联。
• 多版本记录处理：若同一车辆有多个版本更新记录，可通过GROUP BY vi.VehicleNum结合MAX(up.time)获取最新的更新时间。
• 空值处理：使用LEFT JOIN会保留所有车辆，未安装目标版本的车辆installed_version和update_time会返回NULL，可根据业务需求调整为自定义标记（如'未安装'）。

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