代码之家 › 专栏 › 技术社区 › Zsolt Botykai

oracle(10)sql:根据两个字段计算最终和

running-total sum oracle sql

Zsolt Botykai · 技术社区 · 16 年前

首先是免责声明:我在学校从未学过任何编程,只需要处理各种各样的sql问题。

现在我有两张桌子,第一张:

ACCNO BAL1 BAL2
11111   20   10

表2(当然有accno键)与“11111”相关的行:

DATENUM AMT
1       -5
2       -10
3       8
4       -23
5       100
6       -120
7       140

现在我必须使用以下规则找到新的bal1和bal2:

BAL1金额必须从BAL1减去或添加到BAL1,直到BAL1==0(和BAL2>0)
如果bal1达到0,那么bal1的余数(如果有的话)必须从bal2减去
如果bal2也达到0,则只应修改bal1。

因此,使用上述数据:

DATENUM AMT   BAL1 BAL2
0       0     20   10   /*starting record*/
1       -5    15   10   
2       -10   5    10
3       8     13   10
4       -23   0    0
5       100   100  0
6       -120  -20  0
7       140   120   0

我需要最后两个bal1和bal2。

如何使用(Oracle 10)SQL计算它们?

3 回复 | 直到 16 年前

Peter Lang 16 年前

我想我应该用pl/sql来实现这一点:

DECLARE
  v_bal1  table1.bal1%TYPE;
  v_bal2  table1.bal2%TYPE;
  v_accno table1.accno%TYPE;
BEGIN
  v_accno := 11111;
  SELECT bal1, bal2
  INTO v_bal1, v_bal2  
  FROM table1
  WHERE accno = v_accno;

  FOR c IN ( SELECT amt
             FROM table2
             WHERE accno = v_accno
             ORDER BY datenum )
  LOOP
    v_bal1 := v_bal1 + c.amt;
    IF( v_bal1 < 0 AND v_bal2 > 0 ) THEN
      v_bal2 := v_bal2 + v_bal1;  --# v_bal1 < 0, so "add" to v_bal2
      IF( v_bal2 < 0 ) THEN
        v_bal1 := v_bal1 + v_bal2; --# "remove" remainder
        v_bal2 := 0;
      ELSE
        v_bal1 := 0;
      END IF;
    END IF;
  END LOOP;
  dbms_output.put_line( v_bal1 || ', ' || v_bal2 );
END;

这个输出

120, 0

好像你的最后一行错了,补充道 40 而不是 140 .

APC 16 年前

如果只有一个平衡列,那么在sql中就很容易做到这一点。我们可以使用分析和()来生成amt的滚动总计,并将其应用于每行的bal1…

SQL> select accno
  2         , bal1
  3         , datenum
  4         , amt
  5         , rolling_amt
  6         , bal1 + rolling_amt as rolling_bal1
  7  from (
  8      select t1.accno
  9               , t2.datenum
 10               , t2.amt
 11               , t1.bal1
 12               , sum ( t2.amt) over
 13                         ( partition by t2.accno
 14                           order by t2.datenum rows unbounded preceding )
 15                                           as rolling_amt
 16      from t1 join t2 on (t2.accno = t1.accno)
 17      where t1.accno = 11111
 18      order by t2.datenum
 19  )
 20  /

     ACCNO       BAL1    DATENUM        AMT ROLLING_AMT ROLLING_BAL1
---------- ---------- ---------- ---------- ----------- ------------
     11111         20          1         -5          -5           15
     11111         20          2        -10         -15            5
     11111         20          3          8          -7           13
     11111         20          4        -23         -30          -10
     11111         20          5        100          70           90
     11111         20          6       -120         -50          -30
     11111         20          7        140          90          110

7 rows selected.

SQL>

然而,您的需求在两列之间转换,并在行之间传递一些算法,这要复杂得多。也许可以用model()子句来实现,但是想到这个总是让我的额头流血。

erikkallen 16 年前

除了使用光标的简单(=无聊)解决方案外,您还可以通过创建聚合函数(或者更确切地说,创建两个聚合函数,一个用于计算余额1,另一个用于计算余额2)来完成此操作。问题是,您只能对聚合函数使用一个参数,因此该参数必须是复合类型。在伪代码中(我已经很久没有使用Oracle了):

CREATE TYPE tuple_type(amt number, bal1 number, bal2 number);

CREATE FUNCTION calc_bal1(arg IN tuple_type) RETURN number AGGREGATE USING some_implementing_type;
CREATE FUNCTION calc_bal2(arg IN tuple_type) RETURN number AGGREGATE USING some_implementing_type;

然后可以使用分析函数查询它们。如果您只对每个账户的最终价值感兴趣,您可以:

SELECT t1.acct_no,
       calc_bal1(tuple_type(t2.amt, t1.bal1, t1.bal2)) OVER (PARTITION BY t1.acct_no ORDER BY t2.datenum),
       calc_bal2(tuple_type(t2.amt, t1.bal1, t1.bal2)) OVER (PARTITION BY t1.acct_no ORDER BY t2.datenum)
  FROM table1 t1
  JOIN (SELECT acct_no, datenum, amt FROM table2
        UNION ALL
        SELECT acct_no, 0, 0) t2
    ON t1.acct_no = t2.acct_no;
 WHERE t1.datenum = 0;

如果你想要每一个单曲转换,请:

SELECT t1.acct_no,
       calc_bal1(tuple_type(t2.amt, t1.bal1, t1.bal2))
                 OVER (PARTITION BY t1.acct_no
                 ORDER BY t2.datenum
                 ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW),
       calc_bal1(tuple_type(t2.amt, t1.bal1, t1.bal2))
                 OVER (PARTITION BY t1.acct_no
                 ORDER BY t2.datenum
                 ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW)
  FROM table1 t1
  JOIN (SELECT acct_no, datenum, amt FROM table2
        UNION ALL
        SELECT acct_no, 0, 0) t2
    ON t1.acct_no = t2.acct_no;

您还可以使用游标而不是聚合来完成此操作(很可能 可怕的 性能):

CREATE FUNCTION calc_bal1(c IN sys.ref_cursor, bal1 IN number, bal2 IN number) RETURN number AS ...;
CREATE FUNCTION calc_bal2(c IN sys.ref_cursor, bal1 IN number, bal2 IN number) RETURN number AS ...;

如果要所有行:

SELECT t1.acct_no,
       calc_bal1(CURSOR(SELECT amt FROM table2 x WHERE x.acct_no = t1.acct_no AND x.datenum <= t2.datenum ORDER BY x.datenum), t1.bal1, t1.bal2),
       calc_bal2(CURSOR(SELECT amt FROM table2 x WHERE t2.acct_no = t1.acct_no AND x.datenum <= t2.datenum ORDER BY t2.datenum), t1.bal1, t1.bal2)
  FROM table1 t1
  JOIN (SELECT acct_no, datenum, amt FROM table2
        UNION ALL
        SELECT acct_no, 0, 0) t2
    ON t1.acct_no = t2.acct_no;

如果只需要最终值:

SELECT t1.acct_no,
       calc_bal1(CURSOR(SELECT amt FROM table2 t2 WHERE t2.acct_no = t1.acct_no ORDER BY t2.datenum), t1.bal1, t1.bal2),
       calc_bal2(CURSOR(SELECT amt FROM table2 t2 WHERE t2.acct_no = t1.acct_no ORDER BY t2.datenum), t1.bal1, t1.bal2)
  FROM table1 t1;