BigQuery一对多左连接：如何处理ANY、EU等多映射区域值

当然可以通过条件逻辑搞定这个需求，核心是在LEFT JOIN的ON子句里写匹配规则，同时还能处理优先级问题，避免重复匹配。

先明确映射规则 #

首先得把业务上的映射关系理清楚：

• ANY：匹配表A中所有国家代码
• EU：匹配指定的欧盟国家，比如你给出的示例里，对应FR、DE、UK（如果业务上UK已不属于EU，直接从列表里删掉就行）
• 具体国家代码（比如US、UK）：精确匹配表A的对应值

基础实现：保留所有匹配项 #

如果不需要去重，只是要把所有符合条件的表B记录和表A关联，直接在ON里写多条件判断就行：

SELECT 
  a.item_id,
  a.country_code,
  b.country_region
FROM 
  `your-project.dataset.table_a` a
LEFT JOIN 
  `your-project.dataset.table_b` b
ON 
  a.item_id = b.item_id
  AND (
    -- 精确匹配具体国家
    b.country_region = a.country_code
    -- 匹配EU区域内的国家
    OR (b.country_region = 'EU' AND a.country_code IN ('FR', 'DE', 'UK'))
    -- 匹配全局ANY
    OR b.country_region = 'ANY'
  )

进阶实现：优先匹配最具体的记录 #

如果同一个item_id在表B里同时存在具体国家、EU、ANY的记录，通常我们希望优先取最精确的匹配（具体国家 > EU > ANY），这时候用窗口函数过滤就行：

WITH joined_data AS (
  SELECT 
    a.item_id,
    a.country_code,
    b.country_region,
    -- 给匹配规则设优先级：精确匹配最高，然后是EU，最后是ANY
    ROW_NUMBER() OVER (
      PARTITION BY a.item_id, a.country_code 
      ORDER BY 
        CASE 
          WHEN b.country_region = a.country_code THEN 1
          WHEN b.country_region = 'EU' THEN 2
          WHEN b.country_region = 'ANY' THEN 3
          ELSE 4 -- 其他不匹配的情况，排最后
        END
    ) AS rn
  FROM 
    `your-project.dataset.table_a` a
  LEFT JOIN 
    `your-project.dataset.table_b` b
  ON 
    a.item_id = b.item_id
    AND (
      b.country_region = a.country_code
      OR (b.country_region = 'EU' AND a.country_code IN ('FR', 'DE', 'UK'))
      OR b.country_region = 'ANY'
    )
)
SELECT 
  item_id,
  country_code,
  country_region
FROM joined_data
WHERE rn = 1 -- 只保留优先级最高的匹配项

注意事项 #

• EU的国家列表完全可以根据你的业务需求调整，比如新增ES、IT这些欧盟国家
• 如果表B里有多个同优先级的记录（比如同一个item_id有两条EU的记录），可以在ORDER BY里再加字段（比如b.created_at DESC）来取最新的那条
• 左连接会保留表A的所有记录，哪怕表B里没有任何匹配的项，这时候country_region会显示为NULL

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