SQL> select * from test;
ID AMOUNT TYPE
---------- ---------- ----------
1 100 in
2 100 in
3 50 out
4 70 out
5 300 in
test是随便建立的一個執行個體表,其中id列唯一辨別每次事務處理,amount列辨別每次存取金額的數量,type代表了是存(in)還是取(out)。
SQL> select id,amount,case when type='in' then 'Purchase' else 'payment' end type,
2 sum(case when type='in' then amount else -amount end)
3 over(order by id) as balance
4 from test
5 ;
ID AMOUNT TYPE BALANCE
---------- ---------- -------- ----------
1 100 Purchase 100
2 100 Purchase 200
3 50 payment 150
4 70 payment 80
5 300 Purchase 380
balance列記錄了每次存取操作以後的賬戶餘額。