在Oracle查询中计算运行余额

Question

我有这样的数据

id    cp_id   amount_a       amount_b
CCP1  TTP01   10.000.000     2.000.000
CCP1  TTP02   10.000.000     3.000.000
CCP1  TTP03   10.000.000     1.000.000
CCP1  TTP04   10.000.000       500.000
CCP2  TTP05    5.000.000     1.000.000
CCP2  TTP06    5.000.000     2.000.000
CCP3  TTP07    1.000.000       500.000 

我希望结果数据增加一列“running_balance”，格式如下：

id       amount_a       amount_b      running_balance
CCP1   10.000.000     2.000.000     8.000.000
CCP1   10.000.000     3.000.000     5.000.000
CCP1   10.000.000     1.000.000     4.000.000
CCP1   10.000.000       500.000     3.500.000
CCP2    5.000.000     1.000.000     4.000.000
CCP2    5.000.000     2.000.000     2.000.000
CCP3    1.000.000       500.000       500.000 

我已经做了这样的查询

/* Formatted on 1/26/2017 2:50:06 PM (QP5 v5.115.810.9015) */
  SELECT   B.NO_KLAIM AS id,
           a.amount AS amount_a,
           B.AMOUNT AS amount_b,
           SUM (A.AMOUNT) OVER (ORDER BY B.AMOUNT ROWS UNBOUNDED PRECEDING)
              AS running_balance
    FROM      TRX_TITIPAN A
           JOIN
              TRX_KLAIM_TITIPAN B
           ON A.NO_RESI_TITIPAN = B.NO_RESI_TITIPAN
GROUP BY   B.NO_KLAIM, B.AMOUNT, a.amount

但是结果没有计算amount_a的金额，只是累加了amount_b的总和。

**更新： 我已经更新了我的查询。

 /* Formatted on 1/26/2017 2:50:06 PM (QP5 v5.115.810.9015) */
      SELECT   B.NO_KLAIM AS id,
               a.amount AS amount_a,
               B.AMOUNT AS amount_b,
               NVL (TITIP.AMOUNT, 0)
               - SUM (NVL (KLAIM.AMOUNT, 0))
                    OVER (PARTITION BY TITIP.AMOUNT
                          ORDER BY TITIP.NO_RESI_TITIPAN,
                                   KLAIM.NO_KLAIM,
                                   TITIP.AMOUNT,
                                   KLAIM.AMOUNT asc
                          ROWS UNBOUNDED PRECEDING) as running_balance
        FROM      TRX_TITIPAN A
               JOIN
                  TRX_KLAIM_TITIPAN B
               ON A.NO_RESI_TITIPAN = B.NO_RESI_TITIPAN
    GROUP BY   B.NO_KLAIM, B.AMOUNT, a.amount

当使用特定的id并且带有WHERE id= ....条件时，运行余额是正确的。但是当删除WHERE id= ....条件时，运行余额将出现错误。

- Ras Rass

---

running_balance 是如何定义的？ - Massimo Petrus

为了满足您的额外要求，您需要在 partition by 子句中包含 id。在分析函数中，partition by 的工作方式与聚合查询中的 group by 相同 - 它确定您希望分析函数跨越哪些行组进行工作。 - Boneist

Answer 1

我认为您需要的是：

我想您需要：

SELECT   B.NO_KLAIM AS id,
         a.amount AS amount_a,
         B.AMOUNT AS amount_b,
         a.amount - SUM (B.AMOUNT) 
                   OVER (partition by b.no_klaim ORDER BY B.cp_id ROWS UNBOUNDED PRECEDING)
              AS running_balance
FROM      TRX_TITIPAN A
           JOIN
              TRX_KLAIM_TITIPAN B
           ON A.NO_RESI_TITIPAN = B.NO_RESI_TITIPAN

- Boneist

---

原始数据包含一个额外的标识符cp_id，它似乎确定了提款的顺序。因此，在分析子句中应将其用作ORDER BY条件。 - APC

我从OP最初提出的查询开始，他们按照b.amount排序（尽管我没有注意到我和OP在排序中遗漏了DESC关键字）。 - Boneist

按照“amount desc”排序无法解释“id = 'CCP2'”的顺序，但按照“cp_id”排序可以。 - APC

Answer 2

如果您附加表格描述，那将会更容易。

根据您提供的示例数据，您需要：

select id, amout_a, amount_b, 
       amount_a - sum(amount_b) over (partition by id order by id, cp_id) as running_balance 
  from table;

尝试将其转换为您的表格，它会给出：

SELECT B.NO_KLAIM AS id,
       a.amount AS amount_a,
       B.AMOUNT AS amount_b,
       a.amount - SUM(B.AMOUNT) OVER (PARTITION BY B.NO_KLAIM ORDER BY B.NO_KLAIM/*, HERE PUT WHAT IS cp_id*/) AS running_balance
FROM      TRX_TITIPAN A
       JOIN
          TRX_KLAIM_TITIPAN B
       ON A.NO_RESI_TITIPAN = B.NO_RESI_TITIPAN

您只需要按照cp_id的顺序填写一个列即可。

- Kacper