mysql - 根据前一行中的列值和同一行中的其他列计算列值(oracle 11g db)

Question

我的需求可以在 MySQL DB 中用下面的 SQL 来演示:

CREATE TABLE `test` (
  `tran_date` datetime DEFAULT NULL,
  `qty1` int(11) DEFAULT '0',
  `qty2` int(11) DEFAULT '0'
) ;

insert into test values('2019-01-01 10:00:00',1,0);
insert into test values('2019-01-01 10:00:01',2,0);
insert into test values('2019-01-01 10:00:03',0,1);
insert into test values('2019-01-01 10:00:04',0,2);
insert into test values('2019-01-01 10:00:05',1,0);
insert into test values('2019-01-01 10:00:05',3,0);
insert into test values('2019-01-01 10:00:06',0,4);
insert into test values('2019-01-01 10:00:07',0,1);

select tran_date, qty1, qty2, case when qty1=0 then (@total := @total + qty2 ) when qty1<=@total then (@total := @total - qty1)
else  ( @total := 0 ) end  as  qty3
from test , (select @total := 0 ) as T1
order by tran_date;

+---------------------+------+------+------+
| tran_date           | qty1 | qty2 | qty3 |
+---------------------+------+------+------+
| 2019-01-01 10:00:00 |    1 |    0 |    0 |
| 2019-01-01 10:00:01 |    2 |    0 |    0 |
| 2019-01-01 10:00:03 |    0 |    1 |    1 |
| 2019-01-01 10:00:04 |    0 |    2 |    3 |
| 2019-01-01 10:00:05 |    1 |    0 |    2 |
| 2019-01-01 10:00:05 |    3 |    0 |    0 |
| 2019-01-01 10:00:06 |    0 |    4 |    4 |
| 2019-01-01 10:00:07 |    0 |    1 |    5 |
+---------------------+------+------+------+

qty3 列是我想要的，它应该根据前一行中的值以及同一行中 qty1 和 qty2 的值​​来计算。对于第一行，其值始终为 0。规则是:

1. if qty1=0, the qty3 = ${qty3_in_previous_row_value} + qty2

2. if qty1 <= ${qty3_in_previous_row_value}, the qty3 = ${qty3_in_previous_row_value} - qty1

3. else, qty3 = 0

以上SQL可以在MySQL DB中工作，我想在Oracle DB中找到一个SQL来完成相同的功能。非常感谢您的帮助!

Answer 1

您可以使用递归查询来解决此问题，类似于您的 MySQL 解决方案:

-- Your sample data:
WITH test(tran_date, qty1, qty2) as 
  (SELECT '2019-01-01 10:00:00',1,0 from dual union all
   select '2019-01-01 10:00:01',2,0 from dual union all
   select '2019-01-01 10:00:03',0,1 from dual union all
   select '2019-01-01 10:00:04',0,2 from dual union all
   select '2019-01-01 10:00:05',1,0 from dual union all
   select '2019-01-01 10:00:05',3,0 from dual union all
   select '2019-01-01 10:00:06',0,4 from dual union all
   select '2019-01-01 10:00:07',0,1 from dual)
-- Add an index to join actual with prevous row:
   , rn(tran_date, qty1, qty2, rn) AS
  (SELECT tran_date, qty1, qty2, ROWNUM rn FROM test ORDER BY tran_date)
-- Recursive query from here on:
  , rec(tran_date, qty1, qty2, qty3, rn) AS
  (SELECT tran_date, qty1, qty2, 0, rn FROM rn WHERE rn=1
   UNION ALL 
   SELECT rn.tran_date, rn.qty1, rn.qty2
        , CASE WHEN rn.qty1=0 THEN rec.qty3 + rn.qty2
               WHEN rn.qty1<=rec.qty3 THEN rec.qty3 - rn.qty1
               ELSE 0
          END
        , rn.rn
     FROM rec
     JOIN rn
     ON rn.rn = rec.rn + 1)
SELECT tran_date, qty1, qty2, qty3 FROM rec