多表多Period_ID关联查询效率优化方案咨询

核心优化思路 #

放弃针对每个历史月份重复左连接的方式，改用**窗口函数LAG()**一次性获取过去12个月的数据。这种方式只需对每张业务表扫描一次，替代原来12次扫描+连接的操作，大幅降低查询的IO开销和执行时间。

单表优化示例 #

以你的销售表为例，直接用LAG函数生成各月份的滞后数据：

WITH sales_processed AS (
    SELECT
        PeriodID,
        Sales,
        -- 获取前1个月数据
        LAG(Sales, 1) OVER(ORDER BY PeriodID) AS Sales_1mo_ago,
        -- 获取前2个月数据
        LAG(Sales, 2) OVER(ORDER BY PeriodID) AS Sales_2mo_ago,
        -- ... 依次类推到前12个月
        LAG(Sales, 12) OVER(ORDER BY PeriodID) AS Sales_12mo_ago
    FROM sales_table
)
-- 取最新月份的整合数据
SELECT
    Sales, Sales_1mo_ago, Sales_2mo_ago, ..., Sales_12mo_ago
FROM sales_processed
WHERE PeriodID = (SELECT MAX(PeriodID) FROM sales_table);

多表场景优化方案 #

针对6张业务表，不需要每张表做12次连接，而是：

• 对每张业务表单独用上述LAG方式生成包含自身及12个月滞后字段的CTE/视图
• 最后将所有处理后的表通过PeriodID做一次关联即可

示例代码框架：

-- 处理销售表
WITH sales_processed AS (
    SELECT
        PeriodID,
        Sales,
        LAG(Sales, 1) OVER(ORDER BY PeriodID) AS Sales_1mo_ago,
        ...,
        LAG(Sales, 12) OVER(ORDER BY PeriodID) AS Sales_12mo_ago
    FROM sales_table
),
-- 处理其他业务表（以订单表为例）
orders_processed AS (
    SELECT
        PeriodID,
        Orders,
        LAG(Orders, 1) OVER(ORDER BY PeriodID) AS Orders_1mo_ago,
        ...,
        LAG(Orders, 12) OVER(ORDER BY PeriodID) AS Orders_12mo_ago
    FROM orders_table
),
-- 同理处理剩下4张表...
other_table_processed AS (...)
-- 最终关联所有处理后的表
SELECT
    sp.Sales, sp.Sales_1mo_ago, ..., sp.Sales_12mo_ago,
    op.Orders, op.Orders_1mo_ago, ..., op.Orders_12mo_ago,
    otp.OtherMetric, otp.OtherMetric_1mo_ago, ...
FROM sales_processed sp
LEFT JOIN orders_processed op ON sp.PeriodID = op.PeriodID
LEFT JOIN other_table_processed otp ON sp.PeriodID = otp.PeriodID
WHERE sp.PeriodID = (SELECT MAX(PeriodID) FROM sales_table);

处理月份断档的补充方案 #

如果业务表存在月份缺失（比如某PeriodID不存在），先生成连续的月份维度表，再关联业务表补全数据，确保LAG函数能按实际月份取数：

-- 生成过去13个月的连续YYYYMM序列
WITH continuous_periods AS (
    SELECT
        FORMAT(DATEADD(month, -n, GETDATE()), 'yyyyMM') AS PeriodID
    FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS nums(n)
),
sales_processed AS (
    SELECT
        cp.PeriodID,
        s.Sales,
        LAG(s.Sales, 1) OVER(ORDER BY cp.PeriodID) AS Sales_1mo_ago,
        ...,
        LAG(s.Sales, 12) OVER(ORDER BY cp.PeriodID) AS Sales_12mo_ago
    FROM continuous_periods cp
    LEFT JOIN sales_table s ON cp.PeriodID = s.PeriodID
)
SELECT * FROM sales_processed WHERE PeriodID = (SELECT MAX(PeriodID) FROM continuous_periods);

效率提升关键点 #

• 减少连接次数：从72次左连接降到6次以内，避免多次连接带来的笛卡尔积风险和IO损耗
• 索引优化：确保PeriodID字段创建索引，窗口函数的排序操作会直接利用索引提升性能
• 逻辑简洁：后续调整滞后月份数（比如从12个月改到6个月），只需修改LAG函数的第二个参数，维护成本极低

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