如何在Oracle SQL中高效计算非累计预算值

需求说明 #

需要为每个月计算预算的非累计值（NC_Budget），计算规则如下：

• 1月的NC_BUDGET与当月Budget值相同；
• 后续月份的NC_BUDGET = 当月Budget值 - 上月Budget值。

现有表结构及初始数据：

YEAR MONTH ACCOUNT  A_DESCRIPTION O_DESCRIPTION BUDGET NC_BUDGET
2023    01  23  Salary_Employee_1   95684 - Jack's  10  0
2023    01  24  Salary_Employee_2   95684 - Jack's  20  0
2023    01  25  Salary_Employee_3   95684 - Jack's  30  0
2023    01  26  Salary_Employee_4   95684 - Jack's  40  0
2023    01  27  Salary_Employee_5   95684 - Jack's  50  0
2023    02  23  Salary_Employee_1   95684 - Jack's  60  0
2023    02  24  Salary_Employee_2   95684 - Jack's  70  0
2023    02  25  Salary_Employee_3   95684 - Jack's  80  0
2023    02  26  Salary_Employee_4   95684 - Jack's  90  0
2023    02  27  Salary_Employee_5   95684 - Jack's  100 0

目标NC_Budget结果：

YEAR MONTH ACCOUNT  A_DESCRIPTION O_DESCRIPTION BUDGET NC_BUDGET
2023    1   23  Salary_Employee_1   95684 - Jack's  10  10
2023    1   24  Salary_Employee_2   95684 - Jack's  20  20
2023    1   25  Salary_Employee_3   95684 - Jack's  30  30
2023    1   26  Salary_Employee_4   95684 - Jack's  40  40
2023    1   27  Salary_Employee_5   95684 - Jack's  50  50
2023    2   23  Salary_Employee_1   95684 - Jack's  60  50
2023    2   24  Salary_Employee_2   95684 - Jack's  70  50
2023    2   25  Salary_Employee_3   95684 - Jack's  80  50
2023    2   26  Salary_Employee_4   95684 - Jack's  90  50
2023    2   27  Salary_Employee_5   95684 - Jack's  100 50

表创建及数据插入代码：

CREATE TABLE BudgetTable (
    Year_Key NUMBER(4, 0) NOT NULL,
    Month_Key VARCHAR2(2) NOT NULL,
    Account_Code NUMBER NOT NULL,
    Account_Description VARCHAR2(255) NOT NULL,
    Organzation_Description VARCHAR2(255) NOT NULL,
    Budget_Cumulative NUMBER NOT NULL,
    NC_Budget NUMBER NOT NULL,
    PRIMARY KEY (Year_Key, Month_Key, Account_Code)
);

INSERT INTO BudgetTable (Year_Key, Month_Key, Account_Code, Account_Description, Organzation_Description, Budget_Cumulative, NC_Budget) VALUES (2023, '01', 23, 'Salary_Employee_1', '95684 - Jack''s', 10, 0);
INSERT INTO BudgetTable (Year_Key, Month_Key, Account_Code, Account_Description, Organzation_Description, Budget_Cumulative, NC_Budget) VALUES (2023, '01', 24, 'Salary_Employee_2', '95684 - Jack''s', 20, 0);
INSERT INTO BudgetTable (Year_Key, Month_Key, Account_Code, Account_Description, Organzation_Description, Budget_Cumulative, NC_Budget) VALUES (2023, '01', 25, 'Salary_Employee_3', '95684 - Jack''s', 30, 0);
INSERT INTO BudgetTable (Year_Key, Month_Key, Account_Code, Account_Description, Organzation_Description, Budget_Cumulative, NC_Budget) VALUES (2023, '01', 26, 'Salary_Employee_4', '95684 - Jack''s', 40, 0);
INSERT INTO BudgetTable (Year_Key, Month_Key, Account_Code, Account_Description, Organzation_Description, Budget_Cumulative, NC_Budget) VALUES (2023, '01', 27, 'Salary_Employee_5', '95684 - Jack''s', 50, 0);

INSERT INTO BudgetTable (Year_Key, Month_Key, Account_Code, Account_Description, Organzation_Description, Budget_Cumulative, NC_Budget) VALUES (2023, '02', 23, 'Salary_Employee_1', '95684 - Jack''s', 60, 0);
INSERT INTO BudgetTable (Year_Key, Month_Key, Account_Code, Account_Description, Organzation_Description, Budget_Cumulative, NC_Budget) VALUES (2023, '02', 24, 'Salary_Employee_2', '95684 - Jack''s', 70, 0);
INSERT INTO BudgetTable (Year_Key, Month_Key, Account_Code, Account_Description, Organzation_Description, Budget_Cumulative, NC_Budget) VALUES (2023, '02', 25, 'Salary_Employee_3', '95684 - Jack''s', 80, 0);
INSERT INTO BudgetTable (Year_Key, Month_Key, Account_Code, Account_Description, Organzation_Description, Budget_Cumulative, NC_Budget) VALUES (2023, '02', 26, 'Salary_Employee_4', '95684 - Jack''s', 90, 0);
INSERT INTO BudgetTable (Year_Key, Month_Key, Account_Code, Account_Description, Organzation_Description, Budget_Cumulative, NC_Budget) VALUES (2023, '02', 27, 'Salary_Employee_5', '95684 - Jack''s', 100, 0);

现有方案的问题 #

你当前使用的MERGE语句采用自连接方式，在大数据量下效率低下：

MERGE INTO BudgetTable BT
USING (
    SELECT
        b1.Year_Key,
        b1.Month_Key,
        b1.Account_Code,
        CASE
            WHEN TO_NUMBER(b1.Month_Key) = 1 THEN b1.Budget_Cumulative
            ELSE b1.Budget_Cumulative - COALESCE(b2.Budget_Cumulative, 0)
        END AS New_NC_Budget
    FROM BudgetTable b1
    LEFT JOIN BudgetTable b2 ON 
        b1.Account_Code = b2.Account_Code 
        AND b1.Year_Key = b2.Year_Key 
        AND TO_NUMBER(b1.Month_Key) = TO_NUMBER(b2.Month_Key) + 1
) t
ON (BT.Year_Key = t.Year_Key AND BT.Month_Key = t.Month_Key AND BT.Account_Code = t.Account_Code)
WHEN MATCHED THEN 
    UPDATE SET BT.NC_Budget = t.New_NC_Budget;

原因在于自连接需要扫描表两次，且每次转换Month_Key为数字会导致主键索引无法被有效利用，增加IO开销。

高效解决方案：使用LAG()窗口函数 #

Oracle的LAG()窗口函数可以在单次表扫描中获取同一分组内的上一行数据，避免自连接的性能损耗。

方案1：直接UPDATE语句 #

UPDATE BudgetTable BT
SET NC_Budget = 
    CASE 
        WHEN TO_NUMBER(BT.Month_Key) = 1 THEN BT.Budget_Cumulative
        ELSE BT.Budget_Cumulative - LAG(BT.Budget_Cumulative) OVER (PARTITION BY BT.Year_Key, BT.Account_Code ORDER BY TO_NUMBER(BT.Month_Key))
    END;
COMMIT;

方案2：MERGE结合窗口函数（适合仅更新部分数据场景） #

MERGE INTO BudgetTable BT
USING (
    SELECT 
        Year_Key,
        Month_Key,
        Account_Code,
        CASE 
            WHEN TO_NUMBER(Month_Key) = 1 THEN Budget_Cumulative
            ELSE Budget_Cumulative - LAG(Budget_Cumulative) OVER (PARTITION BY Year_Key, Account_Code ORDER BY TO_NUMBER(Month_Key))
        END AS New_NC_Budget
    FROM BudgetTable
) t
ON (BT.Year_Key = t.Year_Key AND BT.Month_Key = t.Month_Key AND BT.Account_Code = t.Account_Code)
WHEN MATCHED THEN UPDATE SET BT.NC_Budget = t.New_NC_Budget;
COMMIT;

额外优化建议 #

1. 修改Month_Key类型：将Month_Key从VARCHAR2(2)改为NUMBER，避免每次查询时的类型转换，让排序和索引利用更高效。
2. 创建函数索引：如果无法修改字段类型，创建基于TO_NUMBER(Month_Key)的函数索引，提升排序和过滤性能：

CREATE INDEX idx_budget_month_number ON BudgetTable(TO_NUMBER(Month_Key));

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