BigQuery中基于条件自连接表：将PICK日期统一应用到同ID行

需求说明：通过自连接，将同一ID下Workflow字段值为'PICK'对应的Day值，替换该ID所有行的原有Day字段。

原始表 #

ID | Workflow   | Day        |
1  | PICK       | 2022.11.01 |
1  | tew        | 2022.11.02 | 
1  | wte        | 2022.11.03 |
1  |            | 2022.11.04 |
1  |            | 2022.11.05 | 
2  | PICK       | 2022.11.06 |

期望结果表 #

ID | Workflow   | Day        |
1  | PICK       | 2022.11.01 |
1  | tew        | 2022.11.01 | 
1  | wte        | 2022.11.01 |
1  |            | 2022.11.01 |
1  |            | 2022.11.01 | 
2  | PICK       | 2022.11.06 |

自连接实现方案 #

通过自连接关联同ID的行，筛选出Workflow为'PICK'的记录，将其Day值覆盖到同ID所有行：

SELECT 
    t1.ID,
    t1.Workflow,
    t2.Day AS Day
FROM 
    your_table t1
INNER JOIN 
    your_table t2 
ON 
    t1.ID = t2.ID 
    AND t2.Workflow = 'PICK';

逻辑说明 #

• t1表遍历所有原始行，t2表仅筛选同ID下Workflow为'PICK'的记录
• 基于ID关联后，将t2的Day值赋值给t1对应行的Day字段，实现同ID下Day值统一

更高效的窗口函数方案（可选） #

若数据库支持窗口函数（如MySQL 8.0+、PostgreSQL、SQL Server等），可采用无需自连接的简洁写法：

SELECT 
    ID,
    Workflow,
    MAX(CASE WHEN Workflow = 'PICK' THEN Day END) OVER (PARTITION BY ID) AS Day
FROM 
    your_table;

逻辑说明 #

• 按ID分组（PARTITION BY ID），在每组内筛选出Workflow为'PICK'的Day值
• MAX()函数确保取到组内唯一的PICK对应Day值（假设每个ID仅一条PICK记录），并填充到组内所有行

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