时间戳

首页 > 解决方案 > 需要根据sql中的匹配反转对记录进行分组

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

问题描述

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

我的源表中的数据如下。

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

在这种情况下，

第二个例子：

源码中的数据如下：

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

我在小组中得到了解决方案，但数据并不总是保证有孤立记录作为“付款”。有时它们是“付款”，有时它们是“反向付款”。可以帮助我获得如下所示的输出（使用排名或行号函数），以便我可以使用 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

标签： sqldatabaseoracleaggregaterow-number

解决方案

编辑以包括您的第二种情况：

使用 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

推荐阅读