oracle-databaseplsqloracle-data-integrator

how to loop through each row of every group (while doing "group by") in Oracle table

I have a table like this:

enter image description here

I want to group by the table base on "customer_id" column and calculate "Day-day[0]" column. "Day-day[0]" is "Day" field in every group and "day[0]" is first row of the day in the group. At the same time, I have to calculate total risk which is in following:

This is the table after grouping by:

enter image description here

This is total risk formula:

enter image description here

In fact, I have to loop through each row of every group to calculate total risk.

My sample table is like this:

  CREATE TABLE risk_test
    (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
    customer_id  varchar2 (40BYTE),
    risk number,
    day VARCHAR2(50 BYTE))

  insert into risk_test values(1,102,15,1);
  insert into risk_test values(2,102,16,1); 
  insert into risk_test values(3,104,11,1);  
  insert into risk_test values(4,102,17,2);
  insert into risk_test values(5,102,10,2);
  insert into risk_test values(6,102,13,3);
  insert into risk_test values(7,104,14,2);
  insert into risk_test values(8,104,13,2);
  insert into risk_test values(9,104,17,1);
  insert into risk_test values(10,104,16,2);

The sample answer is like this:

enter image description here

Would you please guide me how I can do this scenario in Oracle database?

Any help is really appreciated.

Solution

  • Using the sample data that was provided, I believe this query should calculate the risks properly:

    Query

      SELECT o.*,
         ROUND (
               SUM (day_minus_day0 * risk) OVER (PARTITION BY customer_id)
             / SUM (day_minus_day0) OVER (PARTITION BY customer_id),
             5)    AS total_risk
    FROM (SELECT rt.*, (rt.day - MIN (rt.day) OVER (PARTITION BY customer_id)) + 1 AS day_minus_day0
            FROM risk_test rt) o
ORDER BY customer_id, TO_NUMBER (day), TO_NUMBER (id);

    Result

       ID    CUSTOMER_ID    RISK    DAY    DAY_MINUS_DAY0    TOTAL_RISK
_____ ______________ _______ ______ _________________ _____________
1     102                 15 1                      1      13.77778
2     102                 16 1                      1      13.77778
4     102                 17 2                      2      13.77778
5     102                 10 2                      2      13.77778
6     102                 13 3                      3      13.77778
3     104                 11 1                      1         14.25
9     104                 17 1                      1         14.25
7     104                 14 2                      2         14.25
8     104                 13 2                      2         14.25
10    104                 16 2                      2         14.25