SQL引用父表别名场景下如何将OUTER APPLY高效转换为LEFT JOIN

核心性能问题分析 #

你之前改写的LEFT JOIN耗时高，核心原因有两个：

• 关联条件里的子查询是逐行匹配，每条fact_table的记录都会触发一次dimension表的扫描，没有做预聚合过滤
• 原OUTER APPLY因为有TOP 1的剪枝逻辑，执行器大概率会走索引嵌套循环，你改写的写法反而触发了更多次全表扫描

高效改写方案 #

先对dimension表做预聚合，提前算出每个(product, country)分组下满足条件的最新记录，再和fact表做左关联，这样dimension表只需要扫描1次，性能提升非常明显：

WITH dim_ranked AS (
    SELECT 
        *,
        -- 按product、country分组，日期倒序排序，最新的记录排第一
        ROW_NUMBER() OVER (PARTITION BY product, country ORDER BY date DESC) AS rn
    FROM dimension WITH (NOLOCK)
    -- 提前过滤所有满足时间条件的维度记录，减少后续计算量
    WHERE FORMAT(DATEADD(MONTH, -3, date), 'yyyyMM') <= (SELECT MAX(month_in_the_year) FROM fact_table)
)
SELECT *
FROM fact_table h
LEFT JOIN dim_ranked a
    ON h.product = a.product
    AND h.country = a.country
    AND a.rn = 1; -- 只取每个分组最新的那条匹配记录

额外性能优化建议 #

• 给dimension表建联合索引 (product, country, date DESC)，可以完全避免窗口函数的排序操作，计算效率会提升数倍
• 尽量不要在WHERE条件里对date字段做函数计算，可以把判断逻辑改成对h.month_in_the_year做转换，避免索引失效：比如把FORMAT(DATEADD(MONTH, -3, dd.date), 'yyyyMM') <= h.month_in_the_year改成dd.date <= DATEADD(MONTH, -3, CAST(h.month_in_the_year + '01' AS DATE))，改造后date字段可以直接走索引

内容的提问来源于stack exchange，提问作者Random it guy