在BigQuery中基于行条件为现有表新增列

步骤1：为表a新增age列 #

首先需要在原表中添加目标列，执行以下SQL语句（可根据实际需求调整字段类型，示例使用INT类型）：

ALTER TABLE a ADD COLUMN age INT;

步骤2：基于匹配字段更新age列值 #

根据需求，通过date、device、type、country四个字段匹配填充age值，以下分两种常见场景提供写法：

场景1：年龄数据来自临时查询结果（如你给出的示例数据）

如果年龄数据是固定的临时集合，可通过子查询构造临时表后关联更新：

MySQL 写法：

UPDATE a
JOIN (
    SELECT '2022-10-02' AS date, 'iOS' AS device, 'user' AS type, 'England' AS country, 49 AS age
    UNION ALL
    SELECT '2022-10-02' AS date, 'android' AS device, 'hit' AS type, 'US' AS country, 50 AS age
) AS temp_data
ON a.date = temp_data.date
AND a.device = temp_data.device
AND a.type = temp_data.type
AND a.country = temp_data.country
SET a.age = temp_data.age;

PostgreSQL 写法：

UPDATE a
SET age = temp_data.age
FROM (
    SELECT '2022-10-02'::DATE AS date, 'iOS' AS device, 'user' AS type, 'England' AS country, 49 AS age
    UNION ALL
    SELECT '2022-10-02'::DATE AS date, 'android' AS device, 'hit' AS type, 'US' AS country, 50 AS age
) AS temp_data
WHERE a.date = temp_data.date
AND a.device = temp_data.device
AND a.type = temp_data.type
AND a.country = temp_data.country;

场景2：年龄数据来自另一张已有表（如age_info表）

如果年龄数据已存储在结构为date, device, type, country, age的表中，直接关联更新即可：

MySQL 写法：

UPDATE a
JOIN age_info
ON a.date = age_info.date
AND a.device = age_info.device
AND a.type = age_info.type
AND a.country = age_info.country
SET a.age = age_info.age;

PostgreSQL 写法：

UPDATE a
SET age = age_info.age
FROM age_info
WHERE a.date = age_info.date
AND a.device = age_info.device
AND a.type = age_info.type
AND a.country = age_info.country;

执行完以上两步后，表a将新增age列并填充对应匹配值，无需创建新表。

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