如何在BigQuery中填充数据表内缺失的gender列数据？

我们此前很少接触DWH或SQL，不知如何操作，恳请帮助！我们需要填充BigQuery数据表中缺失的内容：现有一份每日业务主数据，共约20000行，其中gender列存在空白值，希望将其补全。我们有一张通过id与主表关联的员工花名册表，本想借助它来实现，但遇到了困难。

主数据示例 #

date       id   name gender task
2022-08-01 0001 Jack        projectA
2022-08-02 0001 Jack  male  projectA
2022-08-03 0001 Jack        projectA
2022-08-04 0001 Jack  male  projectB
2022-08-05 0001 Jack        projectB
2022-08-01 0002 Smith male  projectA
2022-08-02 0003 Smith       projectB
2022-08-03 0004 Smith male  projectB

员工花名册表示例 #

id   name     gender 
0001 Jack     male
0002 Smith    male
0003 Paul     male
0004 Naomi    Female

方式1：查询补全后的数据（不修改原表） #

通过LEFT JOIN关联两张表，并用COALESCE函数优先保留主表已有的gender值，空白时取花名册表的对应值：

SELECT
  m.date,
  m.id,
  m.name,
  COALESCE(m.gender, r.gender) AS gender,
  m.task
FROM
  `你的项目名.数据集名.主数据表` m
LEFT JOIN
  `你的项目名.数据集名.员工花名册表` r
ON
  m.id = r.id;

• LEFT JOIN：确保主表所有行都被保留，不会因花名册表无匹配id丢失数据
• COALESCE(a, b)：主表gender不为空时用主表值，否则用花名册表的gender值

方式2：直接更新原表的空白gender值 #

如果需要批量修改主表数据，用MERGE语句实现：

MERGE INTO `你的项目名.数据集名.主数据表` m
USING `你的项目名.数据集名.员工花名册表` r
ON m.id = r.id
WHEN MATCHED AND (m.gender IS NULL OR m.gender = '') THEN
  UPDATE SET m.gender = r.gender;

• 仅更新主表中gender为空或空字符串的行，匹配花名册表id完成填充

注意事项 #

1. 确认两张表的id字段类型一致（比如均为字符串），否则会出现关联匹配失败
2. 替换语句中的表路径为你实际的项目、数据集及表名称

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