需要根据匹配的冲销将记录分组 [英] need to group records based on matching reversals

问题描述

这个问题是我以前的帖子的延续.

This question is continuation to my earlier post.

需要基于以下条件中的匹配逆向对记录进行分组sql

通过交易表中的一个复杂示例，我会更加清楚.

I will be more clear with one complex example from my transaction table.

Row_Number  LOAN_ID TXN_ENTRY_API_NAME          TXN_AMT
        1   100     ReverseSpreadPayment        2250
        2   100     SpreadPayment               -2250
        3   100     ReverseSpreadPayment        2250
        4   100     SpreadPayment               -2250
        5   100     ReverseSpreadPayment        2250
        6   100     SpreadPayment               -2250
        7   100     ReverseSpreadPayment        2250
        8   100     ReverseSpreadPayment        2250
        9   100     SpreadPayment               1000

在上面的示例中

行号1和2是匹配的记录，因为bcoz付款具有关联的反向付款，因此将这两项相加. 行号3& 4是匹配的记录，因为bcoz付款具有关联的反向付款，因此将这两项相加. 行号5和6是匹配的记录，因为bcoz付款具有关联的反向付款，因此将这两项相加. 行号7没有关联的匹配付款金额，因此为ophan.保持原样. 行号8没有关联的匹配付款金额，因此为ophan.保持原样. 第9行没有相关的匹配反向付款金额，因此为ophan.保持原样.

rownumber 1&2 are matched records bcoz payment has an associated reverse payment, hence sum these two. rownumber 3&4 are matched records bcoz payment has an associated reverse payment, hence sum these two. rownumber 5&6 are matched records bcoz payment has an associated reverse payment, hence sum these two. rownumber 7 does not have an associated matching payment amount, hence is ophan. Keep it as it is. rownumber 8 does not have an associated matching payment amount, hence is ophan. Keep it as it is. rownumber 9 does not have an associated matching reverse payment amount, hence is ophan. Keep it as it is.

为了更好地理解您，我在此处添加了row_number列，其中在我的交易表中，我们没有任何row_number列.

To understand you better i just added row_number column here, where as in my transaction table we dont have any row_number column.

我的预期输出应如下表所示.

My expected output should be like below table.

                    LOAN_ID     TXN_ENTRY_API_NAME                 TXN_AMT
                        100     ReverseSpreadPayment,SpreadPayment  0
                        100     ReverseSpreadPayment,SpreadPayment  0
                        100     ReverseSpreadPayment,SpreadPayment  0
                        100     ReverseSpreadPayment                2250
                        100     ReverseSpreadPayment                2250
                        100     SpreadPayment                       1000

我期望生成如下所示的行号函数，以便我可以使用行号列进行分组.

What I am expecting to generate row number function like below so that i can group by using row number column.

行号是由我生成的排序列，仅此而已.

The Row number is an ordering column which is generated by me, thats it.

推荐答案

我认为这只是row_number()和聚合:

select listagg(row_number, ',') within group (order by row_number) as row_numbers,
       loan_id, txn_entry_api_name, sum(txn_amt) as txn_amt,
       listagg(txn_entry_api_name, ',') within group (order by row_number) as txn_entry_api_name
from (select t.*,
             row_number() over (partition by loan_id, txn_entry_api_name, abs(txn_amt) order by row_number) as seqnum
      from t
     ) t
group by seqnum;

这篇关于需要根据匹配的冲销将记录分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！