如何使用Oracle分析函数优化现有SQL关联查询代码？

原查询通过自连接关联上月数据计算差值，当数据量较大时，自连接的关联逻辑会带来额外性能开销。利用Oracle的LAG分析函数可简化逻辑并提升查询效率，优化方案如下：

优化后SQL代码 #

SELECT 
    t.*,
    NVL(t.DEUDAPRESTAMO_PAGPER, 0) - NVL(t.prev_month_pagper, 0) AS SALE_CT
FROM (
    SELECT 
        td.*,
        -- 按维度分组，取上月的DEUDAPRESTAMO_PAGPER值
        LAG(td.DEUDAPRESTAMO_PAGPER) OVER (
            PARTITION BY td.CODLLAVE, td.financial_company, td.CODMONEY
            ORDER BY td.CODMES
        ) AS prev_month_pagper
    FROM tabledeuda td
    -- 仅加载目标月份及上月数据，缩小处理数据集
    WHERE td.CODMES IN (&CODMES, TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(&CODMES,'YYYYMM'),-1),'YYYYMM')))
) t
WHERE 
    t.CODMES = &CODMES
    AND NVL(t.DEUDAPRESTAMO_PAGPER, 0) > NVL(t.prev_month_pagper, 0);

优化说明 #

1. 替换自连接为分析函数：用LAG窗口函数在单表扫描中直接获取上月同维度（CODLLAVE、financial_company、CODMONEY）的目标字段值，避免自连接带来的表关联开销。
2. 提前过滤数据：子查询先筛选出目标月份和上月数据，减少后续处理的数据量。
3. 逻辑更简洁：通过窗口函数直接定义取数规则，无需维护复杂的关联条件。

额外性能建议 #

为表tabledeuda创建复合索引，进一步提升窗口函数执行效率：

CREATE INDEX idx_tabledeuda_dim_month ON tabledeuda (CODLLAVE, financial_company, CODMONEY, CODMES, DEUDAPRESTAMO_PAGPER);

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