how to use an Oracle query in Mapping of ODI 12c

Question

I have a table 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);

I want to calculate total risk and save result in new table. So, I create a procedure in ODI and write this query in it:

 create table risk_odi as
 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)

The result table is like this:

My question is that I want to create the table in mapping; therefore, would you please guide me how I can run the above query in mapping, using Aggregate Component or Expression Component?

Any help is really appreciated.

Answer 1

Problem solved. I use this structure in mapping:

First, I use a filter to extract the last thirty days and save it to the thirtyday table. After that, I use aggregate to group data based on customer_id and calculate the minimum day in each group. Then joining aggregate result with thirtyday. The last aggregate is for calculating total_risk based on grouping customer_id. Finally, I save data to the last table which is the result.