sql - how to use Analytic functions instead of join with itself

Question

i want to find the credit/debit side of a transaction in a table like this.

with t1 as
(select 1 v, 11 t, 100 amnt from dual
 union
 select 1 v, 12 t, 200 amnt from dual
 union
 select 1 v, 13 t, -100 amnt from dual
 union
 select 1 v, 14 t, -200 amnt from dual
)
select * from t1

in this table, there is a transaction with 4 articles. 11 receives 100$ from 13 and 12 receives 200$ from 14. one solution is join t1 with itself. this is heavy when the records are very much, like 100 million records per day. the problem in joining this table with itself is heavy process time. i am looking for a solution with Analytic functions to find the sender or receiver of a article. Can you give a solution for reaching this goal.

Thanks.

question from:https://stackoverflow.com/questions/65879689/how-to-use-analytic-functions-instead-of-join-with-itself

Answer 1

If this sample T1 table represents data you really have, it is less than obvious that 11 receives 100$ from 13. Nothing but your words suggests that. Therefore, if you want to make it "obvious" and "faster", add another column which explicitly says that, makes a referential integrity constraint (possible within the same table; why not?), create additional table with pairs of "who received what from whom", or whatever else so that you wouldn't have to write queries which suffer from a wrong data model.