在预言机查询中计算运行余额

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

使用 WHERE id= .... 条件时，运行余额正常工作，具有特定 ID。当我删除 WHERE id= .... 条件是出错。

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

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的顺序填写一列