需要根据 sql 中的匹配反转对记录进行分组

Question

我有一个棘手的场景来聚合数据。

我的源表中的数据如下。

CustomerId  Transaction Type    Transaction Amount
1               Payment              100
1               ReversePayment      -100
1               payment              100
1               ReversePayment      -100
1               Payment              100
1               Payment              100

要求如下：

• 如果付款作为与匹配金额的反向付款，请将这两个记录相加。
• 如果付款没有关联的反向付款，请将其视为孤立付款(不要相加(。

我希望输出是这样的。

CustomerId    Transaction Type                Transaction Amount
1                Payment,ReversePayment               0
1                payment,ReversePayment               0
1                payment                             100
1               Payment                              100

在这种情况下，

作为付款的第一条记录

• 具有关联的反向付款(第二条记录(，因此总和变为 0
• 付款的第三条记录具有关联的反向付款(第四条记录(，则总和变为 0
• 第五和第六个没有相关的反转。 不要对这些记录求和。

第二个例子：

源中的数据如下所示：

CustomerId Transaction Type    Transaction Amount
1              Payment              100
1              ReversePayment      -100
1              payment              300
1              ReversePayment      -300
1               Payment              400
1               Payment              500

预期输出

CustomerId      Transaction Type                Transaction Amount
1              Payment,ReversePayment               0
1              payment,ReversePayment               0
1              payment                             400
1              Payment                             500

第二个示例要求： -作为第一和第二记录(付款及其相关的反向付款得到 匹配(，将这两个记录相加，输出为 0。 - 作为第三和第四记录(付款及其相关的反向付款得到 匹配(，将这两个记录相加，输出为 0。 - 第五和第六个没有相关的逆转。不要对这些记录求和。

我在组中获得了解决方案，但数据并不总是保证具有"付款"的孤儿记录。有时它们是"付款"，有时它们是"反向付款"。有些人可以帮助我像下面这样获得 ouptut(使用排名或行号函数(，以便我可以使用 RRR 列进行分组。

CustomerId  Transaction Type    Transaction Amount         RRR
1              Payment              100                   1
1              ReversePayment      -100                   1
1              payment              100                   2
1              ReversePayment      -100                   2
1               Payment              100                   3
1               Payment              100                   4

CustomerId Transaction Type    Transaction Amount      RRR 
1              Payment              100                 1
1              ReversePayment      -100                 1
1              payment              300                 2
1              ReversePayment      -300                 2
1               Payment              400                3
1               Payment              500                4   

Answer 1

您可以枚举不同的类型，然后聚合：

select customerid,
listagg(ttype, ',') within group (order by ttype) as types,
sum(amount) as amount
from (select t.*,
row_number() over (partition by customerid, ttype, amount order by customerid) as seqnum
from t
) t
group by customerid, seqnum;

Answer 2

已编辑以包含第二个方案：

使用 rownum 强制执行固有排序(即交易按照您列出的顺序发生(，因为您的示例缺少交易 ID 或交易时间

SQL> select * from trans_data2;
CUSTOMER_ID TRANSACTION_TY TRANSACTION_AMOUNT
----------- -------------- ------------------
1 Payment                       100
1 ReversePayment               -100
1 payment                       300
1 ReversePayment               -300
1 Payment                       400
1 Payment                       500
6 rows selected.

SQL> select customer_id,
2      case
3          when upper(next_transaction) = 'REVERSEPAYMENT' then transaction_type||','||next_transaction
4          else transaction_type
5      end transaction_type,
6      case
7          when upper(next_transaction) = 'REVERSEPAYMENT' then transaction_amount + next_transaction_amount
8          else transaction_amount
9      end transaction_amount
10  from (
11      select customer_id, transaction_type, transaction_amount,
12      lead (transaction_type) over ( partition by customer_id order by transaction_id ) next_transaction,
13      nvl(lead (transaction_amount) over ( partition by customer_id order by transaction_id),0)  next_transaction_amount
14      from ( select rownum transaction_id, t.* from trans_data2 t )
15  ) where upper(transaction_type) = 'PAYMENT'
16  ;
CUSTOMER_ID TRANSACTION_TYPE              TRANSACTION_AMOUNT
----------- ----------------------------- ------------------
1 Payment,ReversePayment                         0
1 payment,ReversePayment                         0
1 Payment                                      400
1 Payment                                      500