BigQuery基于多列关联两张表并生成指定结构表的查询需求

核心查询语句 #

下面的SQL会解决全外连接时公共列重复的问题，同时把无匹配的数值字段自动填充为0：

WITH joined_data AS (
  SELECT
    -- 用COALESCE取两边非空的公共列，避免生成重复列
    COALESCE(x.os_name, y.os_name) AS os_name,
    COALESCE(x.tracker_name, y.tracker_name) AS tracker_name,
    COALESCE(x.date, y.date) AS date,
    COALESCE(x.country, y.country) AS country,
    -- 无匹配时填充0，确保数值字段不会出现NULL
    IFNULL(x.install, 0) AS install,
    IFNULL(x.purchase, 0) AS purchase,
    IFNULL(y.y_install, 0) AS y_install,
    IFNULL(y.y_purchase, 0) AS y_purchase
  FROM
    `你的项目ID.数据集ID.table_x` x
  FULL OUTER JOIN
    `你的项目ID.数据集ID.table_y` y
  ON
    x.os_name = y.os_name
    AND x.tracker_name = y.tracker_name
    AND x.date = y.date
    AND x.country = y.country
)
-- 将结果写入目标表，可根据需求选择覆盖或追加模式
SELECT *
FROM joined_data

关键细节说明 #

• 公共列去重：COALESCE函数会优先取表x的列值，若x中无匹配数据则取y的列值，最终结果里只会保留一份公共列，不会出现os_name和os_name_1这类重复列。
• 空值填充：IFNULL函数会把无匹配时产生的NULL值替换为0，保证结果表的数值字段完整性。
• 路径替换：记得把SQL里的你的项目ID.数据集ID.table_x和你的项目ID.数据集ID.table_y替换成你实际的项目、数据集和表名。

定时写入配置（计划查询） #

要实现定时自动将结果写入目标表，用BigQuery的计划查询功能即可：

1. 打开BigQuery控制台，左侧导航栏点击「计划查询」→「创建计划查询」。
2. 将上述SQL粘贴到查询编辑器，然后点击上方「更多」→「查询设置」：
   • 在「目标」区域勾选「设置目标表」，填写目标表的项目、数据集和表名。
   • 选择写入模式：
     • 替换：每次运行覆盖目标表所有数据，适合全量同步场景。
     • 追加：将新数据添加到目标表末尾，适合增量同步场景。
3. 切换到「计划」标签，设置运行频率（如每天、每周，或自定义Cron表达式），指定运行时间和时区。
4. 保存计划查询，之后BigQuery会自动按设定时间执行查询并将结果写入目标表。

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