基于复杂条件逻辑的BigQuery关联查询需求求助

需求核心逻辑 #

• 按Name1 > Name2 > Name3的优先级匹配两张表的员工姓名，仅保留首次匹配的行（避免重复）
• 匹配成功后需同时满足Client IDs和Territory完全一致
• 最终返回第一张表的Performance列值

假设表结构（可根据实际调整） #

• 表1：employee_performance
  • employee_name（员工姓名）
  • client_id（客户ID）
  • territory（区域）
  • performance（绩效值）
• 表2：employee_mappings
  • name1（优先级1姓名）
  • name2（优先级2姓名）
  • name3（优先级3姓名）
  • client_id（客户ID）
  • territory（区域）

解决方案SQL #

WITH ranked_matches AS (
  SELECT
    ep.employee_name,
    ep.performance,
    -- 标记匹配优先级：Name1为最高优先级1，Name2为2，Name3为3
    CASE
      WHEN ep.employee_name = em.name1 THEN 1
      WHEN ep.employee_name = em.name2 THEN 2
      WHEN ep.employee_name = em.name3 THEN 3
    END AS match_priority,
    -- 给每个员工按匹配优先级排序，确保只保留首次匹配的行
    ROW_NUMBER() OVER (
      PARTITION BY ep.employee_name
      ORDER BY CASE
        WHEN ep.employee_name = em.name1 THEN 1
        WHEN ep.employee_name = em.name2 THEN 2
        WHEN ep.employee_name = em.name3 THEN 3
      END ASC
    ) AS row_rank
  FROM `employee_performance` ep
  LEFT JOIN `employee_mappings` em
    ON ep.employee_name IN (em.name1, em.name2, em.name3)
    AND ep.client_id = em.client_id
    AND ep.territory = em.territory
)
SELECT
  employee_name,
  performance
FROM ranked_matches
WHERE row_rank = 1
  AND match_priority IS NOT NULL; -- 过滤未匹配到任何姓名的行

逻辑说明 #

1. 优先级匹配控制：通过CASE标记匹配优先级，再用ROW_NUMBER()按优先级升序排序，保证每个员工仅保留最高优先级的匹配记录
2. 关联条件前置：在JOIN阶段直接过滤Client IDs和Territory一致的行，减少后续计算量
3. 结果过滤：仅保留排名为1（首次匹配）且确实匹配到姓名的记录

示例验证 #

• Diana：匹配到Name1，且Client ID 887与Territory USA完全一致，会出现在最终结果中
• Ana和Tina：虽匹配到Name2，但Client IDs不匹配，在JOIN阶段会被排除，不会出现在结果中

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