An interesting challenge for Oracle analytic query

Question

I am pretty good at Oracle analytic functions, but this has guarded me. I will write it myself if there is an obvious solution :)

I have a table, JOURNAL, which writes Inserts, Updates and Deletes to another table.

The table in which he is a journal of BOND_PAYMENTS, which is the relationship between PAYMENTS AND BONDS; it stores the amount of money (AMOUNT) that was allocated to a specific bond (identified by BOND_NUMBER) from a specific payment (identified by PAYMENT_ID). In addition, it records which aspect of the bond was allocated (BOP_DOMAIN), which may be “BON”, “PET” or some other code. The BOND_PAYMENTS table has a surrogate key (BOP_ID).

Therefore, in my log table, there will usually be 1 or more entries for each BOP_ID - first INSert, followed by perhaps some UPDates, followed by, possibly, DELETE.

Here is the MAGAZINE table:

CREATE TABLE JOURNAL ( JN_DATE_TIME DATE NOT NULL, JN_OPERATION VARCHAR2(3) NOT NULL, BOP_ID NUMBER(9) NOT NULL, PAYMENT_ID NUMBER(9) NOT NULL, BOND_NUMBER VARCHAR2(20) NOT NULL, BOP_DOMAIN VARCHAR2(10) NOT NULL, AMOUNT NUMBER(14,2) NOT NULL ); 

Here are some sample data:

 INSERT INTO JOURNAL VALUES (TO_DATE('01/01/2010','DD/MM/YYYY'),'INS',1242043,1003700,'9995/10','BON',1800); INSERT INTO JOURNAL VALUES (TO_DATE('03/01/2010','DD/MM/YYYY'),'INS',1242046,1003700,'9998/10','BON',1700); INSERT INTO JOURNAL VALUES (TO_DATE('04/01/2010','DD/MM/YYYY'),'INS',1242048,1003700,'9999/10','BON',1800); INSERT INTO JOURNAL VALUES (TO_DATE('05/01/2010','DD/MM/YYYY'),'INS',1242052,1003700,'10003/10','BON',1600); INSERT INTO JOURNAL VALUES (TO_DATE('08/01/2010','DD/MM/YYYY'),'INS',1242058,1003700,'9998/10','BON',100); INSERT INTO JOURNAL VALUES (TO_DATE('09/01/2010','DD/MM/YYYY'),'UPD',1242058,1003700,'9998/10','PET',100); INSERT INTO JOURNAL VALUES (TO_DATE('01/01/2010','DD/MM/YYYY'),'INS',2242043,1003701,'8995/10','BON',1800); INSERT INTO JOURNAL VALUES (TO_DATE('02/01/2010','DD/MM/YYYY'),'INS',2242046,1003701,'8998/10','BON',1700); INSERT INTO JOURNAL VALUES (TO_DATE('03/01/2010','DD/MM/YYYY'),'INS',2242048,1003701,'8999/10','BON',1800); INSERT INTO JOURNAL VALUES (TO_DATE('04/01/2010','DD/MM/YYYY'),'INS',2242058,1003701,'8998/10','BON',100); INSERT INTO JOURNAL VALUES (TO_DATE('05/01/2010','DD/MM/YYYY'),'UPD',2242046,1003701,'8998/10','BON',1500); INSERT INTO JOURNAL VALUES (TO_DATE('06/01/2010','DD/MM/YYYY'),'INS',2242052,1003701,'9003/10','BON',1600); INSERT INTO JOURNAL VALUES (TO_DATE('07/01/2010','DD/MM/YYYY'),'UPD',2242058,1003701,'8998/10','PET',200); 

Now I need to extract the full data set from this log table, but in a slightly different format. The main requirement is that we do not want the log table to write BOP_DOMAIN anymore - this is simply not required.

I need to create a total history for each BOND_PAYMENT record. I cannot use the BOND_PAYMENT table because it shows only the last status of each record. I need to get this information from a journal.

I can’t just take SUM(amount) over(partition by payment_id, bond_number) , because a single BOP_ID can be updated several times; therefore, at any time, only the last amount recorded for this BOP_ID should be used.

Given the above data examples, here is an illustration that I expect to produce:

 SELECT jn_date_time, jn_operation, bop_id, payment_id, bond_number, bop_domain, amount, ? as running_total FROM JOURNAL ORDER BY jn_date_time; 

Here I reproduced on the left sample data for two sample payments. On the right, I have “Running Total,” which is the expected output. Next to it (in red) is understood the logic of how it calculates the current amount for each row.

“Running Total” is a snapshot, at the time of logging, of the total amount for this combination of PAYMENT_ID and BOND_NUMBER. Remember that a specific BOP_ID can be updated several times; the total should only account for the most recent entry for this BOP_ID.

Any solution that works will be acceptable, but I suspect that an analytic function (or a combination of analytic functions) will be the best way to solve this problem.

Answer 1

try it

 WITH inner AS (SELECT jn_date_time, jn_operation, bop_id, payment_id, bond_number, bop_domain, amount, amount - coalesce(lag(amount) over (partition by bop_id order by jn_date_time), 0) as delta_bop_amount FROM JOURNAL) SELECT inner.*, sum(delta_bop_amount) over (partition by payment_id, bond_number order by jn_date_time) as running_total FROM inner ORDER BY bond_number, payment_id 

This will return the same answer for your examples.

You need two passes - the analytic function in the internal query determines how much each record changes the total value for each BOP_ID. INS is a direct complement, UPD must subtract the latest value and add a new one.

The second pass then carries out the total bond / payment amount.

I assume that you wanted to consider a bond / payment as a natural key for the current amount and that there could be several BOP_IDs for any combination of bonds / payments.

Answer 2

 SELECT a.*, lag(amount,1) over (PARTITION BY bond_number ORDER BY payment_id,jn_date_time)recent_amount, amount + nvl(lag(amount,1) over (PARTITION BY bond_number ORDER BY payment_id,jn_date_time),0) running_total FROM JOURNAL a ORDER BY payment_id,jn_date_time 

This solution provides the exact answer that you expect on the above question, and this is also in one pass of the table :).

I just used the lag analytic function to get the last amount value for the bond_number / payment_id combination, and then added that the last amount value in the amount to get the current amount ... SIMPLE !!! .. aint it :)