如何在Oracle SQL中计算非累积值

6rvt4ljy 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(111)

我想计算每个月预算的非累积值（用NC_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

1.要计算01月的NC_BUDGET，请执行以下操作：NC_BUDGET与预算值相同。
1.要计算02月的NC_BUDGET，请执行以下操作：NC_BUDGET =（02月的）预算-（01月的）预算
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 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;

oracle

来源：https://stackoverflow.com/questions/76973598/how-to-calculate-non-cumulative-values-in-oracle-sql

2条答案

按热度按时间

0dxa2lsx1#

最好不要在表中存储NC_Budget。相反，只需在查询时计算它：

SELECT Year_Key
       Month_Key,
       Account_Code,
       Account_Description,
       Organzation_Description,
       Budget_Cumulative,
       Budget_Cumulative - NVL(LAG(Budget_Cumulative) OVER (PARTITION BY account_code,year_key ORDER BY month_key),0) nc_budget
  FROM BudgetTable

如果你想把它存储在表中（* 不 * 推荐，因为它违反了规范化规则，并可能在将来产生不一致的数据），你可以很容易地把它插入到MERGE语句的USING子句中来更新表。但是，你最好不要存储它，这样你就只有一个版本的真相，而不是两个。

赞(0）回复(0）举报 2023-10-16

toiithl62#

不要更新表（或者在表中存储NC_BUDGET，因为它是一个派生列），创建一个视图：

CREATE VIEW BudgetView (
    Year_Key,
    Month_Key,
    Account_Code,
    Account_Description,
    Organzation_Description,
    Budget_Cumulative,
    NC_Budget
) AS
SELECT b.*,
       Budget_Cumulative
       - COALESCE(
           MAX(Budget_Cumulative) OVER (
             PARTITION BY account_code
             ORDER BY TO_DATE(year_key || '-' || month_key || '-01', 'YYYY-MM-DD')
             RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '1' MONTH PRECEDING
           ),
           0
         )
FROM   BudgetTable b

其中，对于示例数据（没有NC_BUDGET列）：

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,
    PRIMARY KEY (Year_Key, Month_Key, Account_Code)
);

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

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

然后，视图包含：
| 年份_键|MONTH_KEY|账户代码|产品描述|组织结构_描述|预算_预算|NC_预算|
| --|--|--|--|--|--|--|
| 2023 | 01 | 23 |工资_1| 95684 -杰克的| 10 | 10 |
| 2023 | 02 | 23 |工资_1| 95684 -杰克的| 60 | 50 |
| 2023 | 01 | 24 |工资_2| 95684 -杰克的| 20 | 20 |
| 2023 | 02 | 24 |工资_2| 95684 -杰克的| 70 | 50 |
| 2023 | 01 | 25 |薪酬_3| 95684 -杰克的| 30 | 30 |
| 2023 | 02 | 25 |薪酬_3| 95684 -杰克的| 80 | 50 |
| 2023 | 01 | 26 |薪酬_4| 95684 -杰克的| 40 | 40 |
| 2023 | 02 | 26 |薪酬_4| 95684 -杰克的| 90 | 50 |
| 2023 | 01 | 27 |薪酬_5| 95684 -杰克的| 50 | 50 |
| 2023 | 02 | 27 |薪酬_5| 95684 -杰克的| 100 | 50 |
如果你必须将它存储在表中（不这样做，它可能会失去同步），那么你可以使用ROWID伪列相关的MERGE语句：

MERGE INTO budgettable dst
USING (
  SELECT Budget_Cumulative
         - COALESCE(
             MAX(Budget_Cumulative) OVER (
               PARTITION BY account_code
               ORDER BY TO_DATE(year_key || '-' || month_key || '-01', 'YYYY-MM-DD')
               RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '1' MONTH PRECEDING
             ),
             0
           ) AS new_nc_budget
  FROM   BudgetTable
) src
ON (src.ROWID = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET nc_budget = src.new_nc_budget;

fiddle

赞(0）回复(0）举报 2023-10-16

我来回答

如何在Oracle SQL中计算非累积值

2条答案

相关问题

热门标签

最新问答