SQL：行和列之间的金额差异

Question

我有一个表格，其中包含以下数据。

Table X             
Seq_no    A    Claim    Payment     Balance (to be calculated)
1        abc    100     10          90 (100-10)
2        abc            50          40 (90-50)
3        abc            20          20 (40-20)
1        xyz    150     10          140 (150-10)
1        qwe    200     10          190 (200-10)

我需要计算列余额。

我正在尝试以下查询： SQL >

Select
Seq_no, a, Claim, Payment, 
CASE 
    When Seq_no =1
    then (claim-Payment) 
    Else ( lag(Balance)- Payment over (order by Balance))
END as  Balance
from table X

然而，我遇到了一个错误。

ORA-00904: "Balance": invalid identifier
00904. 00000 -  "%s: invalid identifier"

我认为这是因为“Balance”不是一个现有的列名。

是否有正确的方法来实现结果？

更新： *我错过了一个重要部分。*

我拥有的数据格式如下：

Table X             
Seq_no    A    Claim    Payment    
1        abc    100     10         
2        abc    100     50         
3        abc    100     20         
1        xyz    150     10          
1        qwe    200     10          

我需要以下格式的结果。

Table X             
Seq_no    A    Claim    Payment     Balance (to be calculated)
1        abc    100     10          90 (100-10)
2        abc            50          40 (90-50)
3        abc            20          20 (40-20)
1        xyz    150     10          140 (150-10)
1        qwe    200     10          190 (200-10)

Seq_no计算已经完成，旨在使重复索赔的索赔列为空，这是我已经想出来的。

- jerry

---

你忘了添加其他的关于关系型数据库的标签，比如 postgresql、db2 等等。 - Lalit Kumar B

ORA错误明确表示您的数据库是Oracle。为什么您会标记MySQL和SQL Server？ - Lalit Kumar B

@Jerry - 请通过点击答案左侧的“打勾”符号接受最适合您目的的答案，以便关闭问题。干杯。 - Utsav

@LalitKumarB 对不起，我的错。 - jerry

Answer 1

在创建余额之前，您无法引用余额。

您可以使用“累加和”来实现您想要的目标。

请注意，我按A进行了分区，因为您想要每个A的另一个余额。

Select
    Seq_no, a, Claim, Payment, 
    sum(nvl(claim,0) - payment) over (partition by A order by seq_no) as Balance
from X;

结果：

SEQ_NO  A   CLAIM   PAYMENT BALANCE
1       abc 100     10      90
2       abc         50      40
3       abc         20      20
1       qwe 200     10      190
1       xyz 150     10      140

编辑：如果使用更新的数据集，只需将nvl函数替换为case when seq=1:

Select
    Seq_no, 
    a, 
    case when seq_no=1 then claim else 0 end as Claim, 
    Payment, 
    sum(case when seq_no=1 then claim else 0 end - payment) 
          over (partition by A order by seq_no) as Balance
from X;

- Florin Ghita

---

优秀的使用sum over partition by。+1 - Utsav

这非常有帮助，Florin。因为我拥有的数据已经是一个复杂查询的结果了。 - jerry

很不幸，我忘记提供我所拥有的确切数据了，索赔列并非所有A都没有空值。我已经对我的原始帖子进行了更新。 - jerry

选择 Seq_no，a，Claim，Payment， sum（nvl（claim，0）- nvl（payment，0））over（partition by A order by seq_no）- Claim *（seq_no-1）as Balance 来自X的; 帮助解决了问题。 - jerry

更容易了（带有示例数据 - 我不知道您的数据/表）。请看我的更新 :) - Florin Ghita

Answer 2

为了使用balance作为列标识符，您必须深入一级，即将现有查询作为子查询。 工作演示：

SQL> WITH sample_data AS(
  2  SELECT 1 Seq_no, 'abc' A, 100 Claim, 10 Payment FROM dual UNION ALL
  3  SELECT 2 Seq_no, 'abc' A, NULL Claim, 50 FROM dual UNION ALL
  4  SELECT 3 Seq_no, 'abc' A, NULL Claim, 20 FROM dual UNION ALL
  5  SELECT 1 Seq_no, 'xyz' A, 150 Claim, 10 FROM dual UNION ALL
  6  SELECT 1 Seq_no, 'qwe' A, 200 Claim, 10 FROM dual
  7  )
  8  -- end of sample_data mimicking real table
  9  SELECT seq_no, A, claim, payment,
 10    CASE
 11      WHEN lag(balance) OVER(PARTITION BY A ORDER BY seq_no) IS NULL
 12      THEN balance
 13      ELSE lag(balance) OVER(PARTITION BY A ORDER BY seq_no) - payment
 14    END balance
 15  FROM
 16    (SELECT seq_no, A, claim, payment,
 17      CASE
 18        WHEN seq_no = 1
 19        THEN claim     - payment
 20        ELSE lag(claim - payment) OVER(PARTITION BY A ORDER BY seq_no) - payment
 21      END balance
 22    FROM sample_data
 23    );

    SEQ_NO A        CLAIM    PAYMENT    BALANCE
---------- --- ---------- ---------- ----------
         1 abc        100         10         90
         2 abc                    50         40
         3 abc                    20         20
         1 qwe        200         10        190
         1 xyz        150         10        140

SQL>

更新：如果您始终对claim使用非空值，则可以进行运行差分：

SQL> WITH sample_data AS(
  2    SELECT 1 Seq_no, 'abc' A, 100 Claim, 10 Payment FROM dual UNION ALL
  3    SELECT 2 Seq_no, 'abc' A, 100 Claim, 50 FROM dual UNION ALL
  4    SELECT 3 Seq_no, 'abc' A, 100 Claim, 20 FROM dual UNION ALL
  5    SELECT 4 Seq_no, 'abc' A, 100 Claim, 10 FROM dual UNION ALL
  6    SELECT 5 Seq_no, 'abc' A, 100 Claim, 10 FROM dual UNION ALL
  7    SELECT 1 Seq_no, 'xyz' A, 150 Claim, 10 FROM dual UNION ALL
  8    SELECT 1 Seq_no, 'qwe' A, 200 Claim, 10 FROM dual
  9    )
 10  -- end of sample_data mimicking real table
 11  SELECT Seq_no,
 12    a,
 13    Claim,
 14    Payment,
 15    CASE
 16      WHEN seq_no = 1
 17      THEN claim     - payment
 18      ELSE SUM(claim - payment) over (partition BY A order by seq_no) - claim*(seq_no-1)
 19    END balance
 20  FROM sample_data;

    SEQ_NO A        CLAIM    PAYMENT    BALANCE
---------- --- ---------- ---------- ----------
         1 abc        100         10         90
         2 abc        100         50         40
         3 abc        100         20         20
         4 abc        100         10         10
         5 abc        100         10          0
         1 qwe        200         10        190
         1 xyz        150         10        140

7 rows selected.

SQL>

- Lalit Kumar B

---

您真的很值得获得奥斯卡。 - Utsav

OP在当Seq_no=1时引发了一个太过复杂且不必要的关于空值和滞后等的讨论...请看我的回复，更为简单。 - Florin Ghita

非常抱歉提供了错误的数据。我已经更新了原始帖子。 Seq_no是从另一个子查询计算出来的，以使重复索赔金额为空。 - jerry

@Lalit 感谢您的回答。然而，如果存在序列号>3（4,5等）的数据，则上述逻辑将失败。 例如： WITH sample_data AS( SELECT 1 Seq_no, 'abc' A, 100 Claim, 10 Payment FROM dual UNION ALL SELECT 2 Seq_no, 'abc' A, NULL Claim, 50 FROM dual UNION ALL SELECT 3 Seq_no, 'abc' A, NULL Claim, 20 FROM dual UNION ALL SELECT 4 Seq_no, 'abc' A, Null Claim, 10 FROM dual UNION ALL SELECT 5 Seq_no, 'abc' A, Null Claim, 10 FROM dual UNION ALL SELECT 1 Seq_no, 'xyz' A, 150 Claim, 10 FROM dual UNION ALL SELECT 1 Seq_no, 'qwe' A, 200 Claim, 10 FROM dual ) - jerry

@jerry 好的，没问题。请查看更新后的答案。它适用于任何seq_no数量。 - Lalit Kumar B

@FlorinGhita 是的，没错。NULLs 可以像你展示的那样轻松处理，不需要使用 CASE。 - Lalit Kumar B

Answer 3

你说得对，错误是因为Balance不是你的表中的列。你需要添加该列。

更大的问题是，很遗憾，使用单个SQL语句计算累加总和的方式并不可行。SQL不适合这样的操作。可以在存储过程中使用游标来完成，但最终结果是过程性的而不是基于集合的，因此性能会很差。以下是在SQL Server中执行此操作的方法（作为示例，未经测试）：

DECLARE PaymentCursor CURSOR FOR SELECT A, Claim, Payment FROM X ORDER BY A, Seq_no
OPEN PaymentCursor

DECLARE @A VARCHAR(16)
DECLARE @Claim MONEY
DECLARE @Payment MONEY
DECLARE @Balance MONEY
DECLARE @PreviousA VARCHAR(16)

SET @PreviousA = ''

FETCH NEXT FROM PaymentCursor INTO @A, @Claim, @Payment
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @A <> @PreviousA
    BEGIN
        SET @Balance = 0
        SET @PreviousA = @A
    END

    SET @Balance = @Balance + @Claim - @Payment

    UPDATE X SET Balance = @Balance WHERE CURRENT OF PaymentCursor

    FETCH NEXT FROM PaymentCursor INTO @A, @Claim, @Payment
END

CLOSE PaymentCursor
DEALLOCATE PaymentCursor

如果您的应用程序确实需要知道与每个交易相关联的账户余额，更好的方法是在插入每行数据时计算余额。将插入逻辑封装在存储过程中：

CREATE PROCEDURE InsertAndComputeBalance
    @A VARCHAR(16),
    @Claim MONEY,
    @Payment MONEY
AS
    DECLARE @MAX_Seq_no INTEGER = (SELECT MAX(Seq_no) FROM X WHERE A = @A)

    INSERT INTO X
    SELECT @MAX_Seq_no + 1, @A, @Claim, @Payment, Balance + @Claim - @Payment FROM X
    WHERE A = @A AND Seq_no = @MAX_Seq_no

理论上，您可以从触发器中调用类似的逻辑，以便每次插入行时自动执行，但触发器可能会有问题，因此请非常小心地进行操作。

- Ron DeSantis