如何使用滞后功能计算借方和贷方运行余额

Question

我的表格中有一些数据accounting .

     ID EDATE     DISCRIPTION      DR         CR     
    --- --------- ------------- ---------- ------  
     1 19-JAN-19 cash in           1000       0       
     2 19-JAN-19 cash out             0     200 
     3 19-JAN-19 cash in            500       0       
     4 19-JAN-19 cash out             0     200        
     5 19-JAN-19 cash out             0     200        
     6 19-JAN-19 cash out             0    1800

我想获得借方和贷方余额的运行余额，如下所示

     ID      EDATE    DISCRIPTION      DR  CR      BALANCE
    --- --------- -------------     ------ ------ ----------
     1 19-JAN-19 cash in           1000       0       1000dr
     2 19-JAN-19 cash out             0     200        800dr
     3 19-JAN-19 cash in            500       0       1300dr
     4 19-JAN-19 cash out             0     200       1100dr 
     5 19-JAN-19 cash out             0     200        900dr
     6 19-JAN-19 cash out             0    1800      (900)cr

我试图用LAG FUNCITON做到这一点，但我的代码失败了在下面

select id,edate,discription,dr,cr,
dr-lag(dr,1,0)
over(order by id) as balance 
from accounting;

我的输出是

ID   EDATE     DISCRIPTION             DR     CR    BALANCE
--- --------- -------------           ------- ----  -------
     1 19-JAN-19 cash in                    1000    0    1000
     2 19-JAN-19 cash out                      0  200   -1000
     3 19-JAN-19 cash in                     500    0     500
     4 19-JAN-19 cash out                      0  200    -500
     5 19-JAN-19 cash out                      0  200       0

Answer 1

您可以尝试使用sum窗口函数。

CREATE TABLE accounting(
   ID int,
   EDATE varchar(50),
   DISCRIPTION varchar(50),
   DR int,
   CR int
);

INSERT INTO accounting VALUES (1,'19-JAN-19','cash in',1000,0);       
INSERT INTO accounting VALUES (2,'19-JAN-19','cash out',0,200); 
INSERT INTO accounting VALUES (3,'19-JAN-19','cash in',500,0);       
INSERT INTO accounting VALUES (4,'19-JAN-19','cash out',0,200);        
INSERT INTO accounting VALUES (5,'19-JAN-19','cash out',0,200);        
INSERT INTO accounting VALUES (6,'19-JAN-19','cash out',0,1800);

查询 1：

select 
  id,
  edate,
  discription,
  dr,
  cr,
  sum(DR) over(order by id) - sum(CR) over(order by id)  as balance 
from accounting

结果：

| ID |     EDATE | DISCRIPTION |   DR |   CR | BALANCE |
|----|-----------|-------------|------|------|---------|
|  1 | 19-JAN-19 |     cash in | 1000 |    0 |    1000 |
|  2 | 19-JAN-19 |    cash out |    0 |  200 |     800 |
|  3 | 19-JAN-19 |     cash in |  500 |    0 |    1300 |
|  4 | 19-JAN-19 |    cash out |    0 |  200 |    1100 |
|  5 | 19-JAN-19 |    cash out |    0 |  200 |     900 |
|  6 | 19-JAN-19 |    cash out |    0 | 1800 |    -900 |

Answer 2

使用格式S并使用所需的代码replace标准 +/- 符号

如果数字列不可为空，则可以省略NVL。

聚合SUM函数通常与PARTITION BY一起使用，以区分帐户 - 请参阅查询中的注释。

with bal as (
select 
  id, cr, dr,
  sum(nvl(cr,0) - nvl(dr,0)) over (/* PARTITION BY account key */ ORDER BY id) as balance
from accounting)
select 
 id, dr db,cr,
 replace(replace(to_char(balance,'999,999.99S'),'+','CR'),'-','DB') balance
from bal 
order by id;
        ID         DB         CR BALANCE                                    
---------- ---------- ---------- ----------------
         1       1000          0   1,000.00DB                                 
         2          0        200     800.00DB                                 
         3        500          0   1,300.00DB                                 
         4          0        200   1,100.00DB                                 
         5          0        200     900.00DB                                 
         6          0       1800     900.00CR