SQL如何补全缺失期间的账户YTD记录（是否使用自连接？）

方案选型结论 #

你最初的维度交叉关联思路是对的，但不需要用全外连接（FULL JOIN），左连接即可。自连接（Self-Join）无法实现该需求：自连接基于源表现有记录做关联，源表本身缺失对应期间的行，无论怎么写关联条件都无法凭空生成不存在的期间维度值。

最优实现逻辑 #

性能最高的通用实现分3步，核心是用小体量的维度集做笛卡尔积生成全量组合，再关联事实表补值，避免对大事实表做全量排序、复杂遍历：

• 准备两个独立维度集合
  • 连续期间集：优先使用预建的公共期间维度表（数仓环境标配），临时查询可以用递归CTE生成指定范围的连续期间，不要从源事实表去重取期间，否则永远拿不到缺失的期间值
  • 全量账户集：优先使用预建的账户主数据表，没有的话可以从源事实表distinct取所有账户
• 两个维度集做CROSS JOIN，生成所有「期间+账户」的完整组合，这一步计算量极低，因为维度表的数据量通常远小于事实表
• 用完整组合左连接（LEFT JOIN）源事实表，关联条件同时匹配期间和账户，用COALESCE函数把未匹配到的YTD值设为0

代码示例 #

场景1：有预建维度表（数仓生产环境首选，性能最优） #

假设期间维度表为dim_period（存所有需要的连续期间值）、账户维度表为dim_account（存所有需要出数的账户），源财务事实表为finance_ytd：

SELECT
    p.period,
    a.account,
    COALESCE(f.ytd_value, 0) AS ytd_value
FROM dim_period p
CROSS JOIN dim_account a
LEFT JOIN finance_ytd f
    ON p.period = f.period
    AND a.account = f.account
-- 按需过滤期间范围，例如统计2022年Q1数据加 WHERE p.period BETWEEN 202201 AND 202203
;

场景2：无预建维度表（临时查询用） #

用递归CTE生成指定范围的连续期间，再和源表取到的去重账户交叉关联：

-- 生成指定范围的连续期间，以下示例生成2022年1-3月期间
WITH RECURSIVE continuous_period AS (
    SELECT 202201 AS period
    UNION ALL
    SELECT period + 1
    FROM continuous_period
    WHERE period < 202203 -- 若涉及跨年逻辑，可调整为日期加减后转YYYYMM格式的逻辑
),
all_account AS (
    SELECT DISTINCT account FROM finance_ytd
)
SELECT
    cp.period,
    aa.account,
    COALESCE(f.ytd_value, 0) AS ytd_value
FROM continuous_period cp
CROSS JOIN all_account aa
LEFT JOIN finance_ytd f
    ON cp.period = f.period
    AND aa.account = f.account
;

性能优化要点 #

• 禁止用FULL JOIN：交叉连接生成的维度组合已经是全量的，FULL JOIN会带来不必要的匹配开销，还可能带出脏数据
• 预建索引：给源事实表的(period, account)字段建联合索引，关联时可以直接走索引覆盖，无需回表，大数据量下性能提升可达数倍
• 优先用预建维度表：递归CTE适合小范围临时查询，生产环境用预建的、带统计信息的维度表，关联计算效率远高于临时生成维度集

样例执行效果 #

基于给出的源表数据，执行上述代码会返回符合预期的结果：

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