使用SQL从贷方和运行余额中有条件地借记

Question

我在表cryptotransactionledger中有如下数据

tbody> <<tr>2

id	transaction_type	amount	totalcoins
1	1	bitcoin-credit	30	30
	2	ethereum-credit	20	50

我会忘记"交易类型";最后的结果，只关注硬币本身。您的数据模型相当奇怪，因为您没有为硬币类型设置单独的列。但是您可以从transaction_type列中提取它。

另外，您的评论建议totalcoins应该是列中的最后一个值。为"bitcoin"那应该是50，不是35。

在任何情况下，都可以使用聚合。我建议:

select regexp_substr(transaction_type, '^[^-]+') as coin,
sum(amount) as amount,
max(totalcoins) keep (dense_rank first oder by id desc) as totalcoins
from cryptotransactionledger tl
group by regexp_substr(transaction_type, '^[^-]+');

这是一个db<>小提琴

假设您有ledger和coin_types表:

create table ledger (id varchar2(3) primary key, 
coin_type varchar2(2), 
amount number(5)
);
create table coin_types (id varchar2(2) primary key, 
alias varchar2(100));

这些dml语句代表您的"最终数据":

insert into coin_types values (1, 'bitcoin');
insert into coin_types values (2, 'etherium');
insert into ledger values (1, 1, 30);
insert into ledger values (2, 2, 20);
insert into ledger values (3, 1, -10);
insert into ledger values (4, 2, -5);
insert into ledger values (5, 1, 15);
insert into ledger values (6, 2, 10);
insert into ledger values (7, 2, -5);

你的第一步应该是汇总金额，按你的硬币类型分组:

select l.coin_type, 
c.alias,
sum(l.amount) amount_coin
from ledger l, coin_types c
where l.coin_type = c.id
group by l.coin_type, c.alias

然后你可以使用这个select来获取你的"totalcoins";作为一个累积和，按你的coin_type排序(参见oracle文档):

select coin_type, 
alias, 
amount_coin, 
sum(amount_coin) over (order by coin_type) totalcoins 
from(
select l.coin_type, 
c.alias,
sum(l.amount) amount_coin
from ledger l, coin_types c
where l.coin_type = c.id
group by l.coin_type, c.alias
);

这是一个工作SQL-Fiddle: SQL-Fiddle

我将创建一个tran类型的表

create table tran_types (
id int primary key,
coin_Name varchar2(100),
op_Name varchar2(100)
);
insert into tran_types  
select 1, 'bitcoin', 'credit' from dual union all
select 2, 'etherium','credit' from dual union all
select 3, 'bitcoin', 'debit'  from dual union all
select 4, 'etherium','debit'  from dual;

这样，您可以分组事务以获得总数，并根据需要可视化总行标题

select t.id transaction_typeid, t.coin_Name || '-' || t.op_Name transaction_type, 
s.amount, s.total_coins
from (
select t.coin_Name, sum(amount) amount
, sum(sum(amount)) over(order by t.coin_Name) total_coins
from ledger r
join tran_types t on r.tran_type = t.id
group by t.coin_Name
) s
join tran_types t on t.op_name = 'credit' and t.coin_Name = s.coin_Name
order by t.coin_Name;

感谢所有提出建议和不同解决方案的人。我终于使用下面的查询，为我提供了预期的结果。

WITH cte1
AS (SELECT a.*,
CASE
WHEN ( transaction_typeid = 1
OR transaction_typeid = 3 ) THEN 0
ELSE 1
END AS category
FROM   cryptotransactionledger a
ORDER  BY id),
cte2
AS (SELECT CASE
WHEN ( category = 0 ) THEN 'bitcoin-credit'
ELSE 'etherium-credit'
END         AS transaction_type,
Sum(amount) AS amount
FROM   cte1 o
GROUP  BY category),
cte3
AS (SELECT Row_number()
OVER (ORDER BY c.transaction_type) AS id,
c.*
FROM   cte2 c) SELECT f.*,
Sum(amount) OVER(ORDER BY f.id) AS total_coins FROM cte3 f;

链接到DB Fiddle