如何使用BigQuery MERGE语句实现目标表对应日期删除后插入源表数据

你遇到的两个问题分别是连续日期区间误删、WHEN子句不支持关联子查询，下面是可直接在BigQuery运行的兼容写法，完全匹配你的需求：

MERGE `你的项目.你的数据集.t` AS t
USING (
  -- 拆分删除、插入两个操作分支
  SELECT 
    'DELETE' AS op_type,
    day,
    -- 补充t表除day外所有字段的NULL占位，字段顺序、类型和t表完全对齐即可
    CAST(NULL AS INT64) AS col1,
    CAST(NULL AS STRING) AS col2,
    CAST(NULL AS FLOAT64) AS col3
  FROM (SELECT DISTINCT day FROM s)

  UNION ALL

  -- 插入分支直接取s全量数据
  SELECT 
    'INSERT' AS op_type,
    *
  FROM s
) AS s_op
-- 仅匹配删除操作的天
ON t.day = s_op.day AND s_op.op_type = 'DELETE'
-- 匹配到的天直接删除目标表所有对应行
WHEN MATCHED THEN DELETE
-- 未匹配到的插入分支数据全部写入目标表
WHEN NOT MATCHED BY TARGET AND s_op.op_type = 'INSERT' THEN INSERT ROW

写法说明 #

1. 内层s_op数据集拆分了两个操作逻辑：第一部分取出s中所有出现过的唯一日期，标记为删除操作；第二部分是s的全量待插入数据，标记为插入操作，完全覆盖你的需求逻辑。
2. ON匹配条件仅对删除分支生效，不会出现非连续日期误删的问题，也不需要在WHEN子句写关联子查询，符合BigQuery的语法限制。
3. 如果你的表字段较多不想写占位符，也可以先查一次表结构拼接字段，或者用指定字段插入的写法替换INSERT ROW，示例如下：

WHEN NOT MATCHED BY TARGET AND s_op.op_type = 'INSERT' 
THEN INSERT (day, col1, col2, col3) VALUES (s_op.day, s_op.col1, s_op.col2, s_op.col3)

避坑提示 #

不要用简化的ON t.day = s.day写法，该写法仅在s每个day仅对应1行数据时能正常运行，当s单天有多行数据时会出现目标表同天数据删不干净的问题。

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