需要基于SQL中的匹配逆向对记录进行分组 [英] Need to group records based on matching reversal in sql

问题描述

我有一个棘手的场景来汇总数据.

I have a tricky scenario to aggregate the data.

我的源表中的数据如下.

Data in my source table is as follows.

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. -第五和第六没有相关的冲销.不要汇总这些记录.

Second example requirement: -As first and second records (payment and its associated reverse payment got matched) ,sum these two records, output is 0. - As third and fourth records (payment and its associated reverse payment got matched), sum these two records, output is 0. - Fifth and sixth does not have associated reversals. don't sum these records.

我在小组中找到了解决方案，但不能总是保证数据具有孤立记录作为付款".有时它们是付款"，有时它们是逆向付款".可以帮助我获得如下所示的结果(使用rank或rownumber函数)，以便我可以通过使用RRR列进行分组.

I got solutions in group, but data is not always guaranteed to have orphan records as 'payments'. Some times they are 'Payments' and some times they are 'ReversePayments'. Can some help me get ouptut like the below (using rank or rownumber functions ) so that i can group by using RRR column.

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   

推荐答案

已编辑，其中包括第二种方案:

Edited to include your second scenario:

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

Using rownum to enforce inherent ordering (i.e. transactions happened in the order you've listed ), since your example is missing a transaction id or transaction time

    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

这篇关于需要基于SQL中的匹配逆向对记录进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！