Google BigQuery中SQL查询补全关联表缺失字段求助

问题分析 #

你的原查询通过FULL OUTER JOIN绑定日期和账号进行关联，但只有当日期与账号完全匹配时，实际表的行才能获取预算表的AccountType和AccountGroup字段。要让所有行（无论日期是否匹配）都带上对应账号的维度信息，应该基于Account字段单独关联账号维度数据，而非绑定日期条件。

解决方案 #

先从预算表中提取账号-维度的映射关系（确保每个账号只保留唯一的类型/组信息），再分别将实际表、预算表与该映射表关联，最后合并结果：

WITH account_dim AS (
    -- 提取预算表中账号对应的维度信息，去重避免重复行
    SELECT DISTINCT
        Account,
        AccountType,
        AccountGroup
    FROM tblBudget
)
-- 处理实际表数据，关联账号维度
SELECT
    a.Date,
    a.Account,
    a.Balance AS Outcome,
    NULL AS Budgeted,
    ad.AccountType,
    ad.AccountGroup
FROM tblActual a
LEFT JOIN account_dim ad ON a.Account = ad.Account

UNION ALL

-- 处理预算表数据，关联账号维度
SELECT
    b.Date,
    b.Account,
    NULL AS Outcome,
    b.Balance AS Budgeted,
    ad.AccountType,
    ad.AccountGroup
FROM tblBudget b
LEFT JOIN account_dim ad ON b.Account = ad.Account

ORDER BY Date DESC;

结果说明 #

• 实际表的行：通过Account字段关联预算表的维度映射，即使日期不匹配也能拿到AccountType和AccountGroup
• 预算表的行：直接关联自身提取的维度映射，完整保留原有维度信息
• UNION ALL确保实际和预算数据都被保留，不会因匹配关系丢失行

补充处理（可选） #

如果预算表中存在同一账号对应不同AccountType/AccountGroup的情况，需要先清理数据（比如保留最新日期的维度值），可修改account_dim的CTE：

WITH account_dim AS (
    SELECT
        Account,
        AccountType,
        AccountGroup
    FROM (
        SELECT
            Account,
            AccountType,
            AccountGroup,
            ROW_NUMBER() OVER (PARTITION BY Account ORDER BY Date DESC) AS rn
        FROM tblBudget
    )
    WHERE rn = 1
)

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