基于多列首个非空值实现BigQuery两表层级关联方案咨询

表结构示例（可根据实际字段调整） #

• Table A（组织层级表）：
  warehouse_id, district_id, city_id, state_id, country_id
  每条记录对应一个仓库，包含完整的上级层级ID/名称

• Table B（计划值表）：
  entity_level（枚举值：'WAREHOUSE', 'DISTRICT', 'CITY', 'STATE', 'COUNTRY'）,
  entity_id,
  plan_amount
  存储不同层级实体的计划金额，同一层级+实体可能存在多条记录（需先做聚合处理）

方案一：聚合后多左连接+COALESCE（适合Table B层级实体唯一场景） #

如果Table B中每个entity_level+entity_id仅存一条计划记录，可直接通过多左连接+COALESCE从最细到最粗层级取值。核心是先清洗Table B避免重复：

-- 先清洗Table B，确保每个层级实体的计划值唯一（按需选择聚合逻辑，比如求和/取最新）
WITH cleaned_b AS (
  SELECT
    entity_level,
    entity_id,
    SUM(plan_amount) AS plan_amount
  FROM `your_project.your_dataset.table_b`
  GROUP BY entity_level, entity_id
),
-- 生成最终Table C
table_c AS (
  SELECT
    a.*,
    COALESCE(
      wh.plan_amount,
      dist.plan_amount,
      city.plan_amount,
      state.plan_amount,
      cntry.plan_amount
    ) AS matched_plan_amount
  FROM `your_project.your_dataset.table_a` a
  LEFT JOIN cleaned_b wh
    ON a.warehouse_id = wh.entity_id AND wh.entity_level = 'WAREHOUSE'
  LEFT JOIN cleaned_b dist
    ON a.district_id = dist.entity_id AND dist.entity_level = 'DISTRICT'
  LEFT JOIN cleaned_b city
    ON a.city_id = city.entity_id AND city.entity_level = 'CITY'
  LEFT JOIN cleaned_b state
    ON a.state_id = state.entity_id AND state.entity_level = 'STATE'
  LEFT JOIN cleaned_b cntry
    ON a.country_id = cntry.entity_id AND cntry.entity_level = 'COUNTRY'
)
SELECT * FROM table_c

方案二：UNION ALL关联+窗口函数筛选（通用无重复方案） #

如果Table B存在同层级同实体多记录，或需要更稳妥的优先级控制，推荐用此方法：先生成仓库与所有层级计划值的匹配关系，再按优先级排序取第一条。

WITH cleaned_b AS (
  SELECT
    entity_level,
    entity_id,
    SUM(plan_amount) AS plan_amount
  FROM `your_project.your_dataset.table_b`
  GROUP BY entity_level, entity_id
),
-- 生成所有可能的匹配关系
warehouse_all_matches AS (
  SELECT
    a.*,
    b.plan_amount,
    -- 定义优先级：数字越小，层级越细优先级越高
    CASE b.entity_level
      WHEN 'WAREHOUSE' THEN 1
      WHEN 'DISTRICT' THEN 2
      WHEN 'CITY' THEN 3
      WHEN 'STATE' THEN 4
      WHEN 'COUNTRY' THEN 5
    END AS priority
  FROM `your_project.your_dataset.table_a` a
  LEFT JOIN cleaned_b b
    ON (b.entity_level = 'WAREHOUSE' AND a.warehouse_id = b.entity_id)
    OR (b.entity_level = 'DISTRICT' AND a.district_id = b.entity_id)
    OR (b.entity_level = 'CITY' AND a.city_id = b.entity_id)
    OR (b.entity_level = 'STATE' AND a.state_id = b.entity_id)
    OR (b.entity_level = 'COUNTRY' AND a.country_id = b.entity_id)
),
-- 按仓库分组，取优先级最高的记录
ranked_matches AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY warehouse_id
      ORDER BY priority ASC
    ) AS rn
  FROM warehouse_all_matches
)
SELECT
  warehouse_id,
  district_id,
  city_id,
  state_id,
  country_id,
  plan_amount AS matched_plan_amount
FROM ranked_matches
WHERE rn = 1

关键注意事项 #

• Table B清洗：必须确保同一层级+实体的计划值唯一，否则会导致匹配结果异常，聚合逻辑需根据业务需求选择（求和、取最大值等）。
• 优先级调整：可根据实际业务逻辑修改CASE语句中的优先级数字，保证最细层级的优先级最高。
• NULL处理：若仓库无任何层级的计划值，matched_plan_amount会返回NULL，可通过IFNULL(matched_plan_amount, 0)替换为默认值。

内容的提问来源于stack exchange，提问作者Echo-Victor58