how to sum effectively, when rows should be in more groups Oracle

Question

I have some records (over 1 million) which are consist from GROUPS of companies, but some records belong to several groups and I need to get total sum of groups (main (column C216) and secondary(column LIST_OF_PARENT_CCN))

In column LIST_OF_PARENT_CCN is any number of group IDs with semicolon ",", you can see it in table RR_SRC_LEK_P5.

I created table with companies group and sums (RR_SRC_LEK_P5) and filled the sum through the cursor. This solution is working, but is slow and not effective.

table RR_SRC_LEK_P5 looks like:

C216        LIST_OF_PARENT_CCN ESS_SUM
00004146    4523401            0
00005354    5487228, 2103188   0
00006203                       0
00007524    2196702, 2103188   0
00009164    2163921, 4523401   0

table RR_SRC_LEK_P6 contains only total sums of PARENT_CCN

PARENT_CCN ESS_SUM
4523401    258418650,7711
5487228    2534044,19664
2103188    13482783356,0255
2196702    9936875,58
2163921    141329781,3349

result should be this, offcourse in column ESS_SUM is only one number

C216        LIST_OF_PARRENT_CCN ESS_SUM
00004146    4523401             258418650,7711
00005354    5487228, 2103188    2534044,19664 + 2534044,19664
00006203                        0
00007524    2196702, 2103188    9936875,58 + 13482783356,0255
00009164    2163921, 4523401    141329781,3349 + 4523401       

there is cursor, i used execute immediate

 CREATE OR REPLACE PROCEDURE CURSOR_TEST AS 
    c_sql sys_refcursor;
    c_C216 RR_SRC_LEK_P5.C216%TYPE;
    c_LIST_OF_PARENT_CCN RR_SRC_LEK_P5.LIST_OF_PARENT_CCN%TYPE;

    BEGIN

         OPEN c_sql for 'select C216, LIST_OF_PARENT_CCN from RR_SRC_LEK_P5 where LIST_OF_PARENT_CCN is not null';
            LOOP 
              FETCH c_sql into c_C216, c_LIST_OF_PARENT_CCN; 
                EXIT WHEN c_sql%notfound;

                execute immediate 'update RR_SRC_LEK_P5 a set
                                   ESS_SUM = (select sum(ESS_SUM) from RR_SRC_LEK_P6 where PARENT_CCN in (' || c_LIST_OF_PARENT_CCN || '))
                                   where a.C216 = ''' || c_C216 || '''';

            END LOOP; 
          CLOSE c_sql;
          commit;
    END CURSOR_TEST;

is there a better better way than use cursor?

Answer 1

You can use an aggregate function with xmlquery:

Table creation:

SQL> CREATE TABLE RR_SRC_LEK_P5  AS (
  2  SELECT '00004146' AS C216, '4523401' AS LIST_OF_PARENT_CCN, 0 AS ESS_SUM FROM DUAL UNION ALL
  3  SELECT '00005354',    '5487228, 2103188',   0 FROM DUAL UNION ALL
  4  SELECT '00006203',    NULL                  ,0 FROM DUAL UNION ALL
  5  SELECT '00007524',    '2196702, 2103188',   0 FROM DUAL UNION ALL
  6  SELECT '00009164',    '2163921, 4523401',   0 FROM DUAL);

Table created.

SQL> CREATE TABLE RR_SRC_LEK_P6 AS
  2  (SELECT 4523401 AS PARENT_CCN,    2584186507711 AS ESS_SUM  FROM DUAL UNION ALL
  3  SELECT 5487228,    253404419664    FROM DUAL UNION ALL
  4  SELECT 2103188,    134827833560255 FROM DUAL UNION ALL
  5  SELECT 2196702,    993687558       FROM DUAL UNION ALL
  6  SELECT 2163921,    1413297813349   FROM DUAL);

Table created.
SQL>

View of current data

SQL> select * from RR_SRC_LEK_P5;

C216     LIST_OF_PARENT_C    ESS_SUM
-------- ---------------- ----------
00004146 4523401                   0
00005354 5487228, 2103188          0
00006203                           0
00007524 2196702, 2103188          0
00009164 2163921, 4523401          0

SQL> select * from RR_SRC_LEK_P6;

PARENT_CCN                                 ESS_SUM
---------- ---------------------------------------
   4523401                           2584186507711
   5487228                            253404419664
   2103188                         134827833560255
   2196702                               993687558
   2163921                           1413297813349

SQL>

Query to update data

SQL>
SQL> MERGE INTO RR_SRC_LEK_P5 P5
  2  USING ( SELECT P5.C216,
  3                  XMLQUERY(
  4                    LISTAGG(P6.ESS_SUM, '+') WITHIN GROUP (ORDER BY NULL)
  5                    RETURNING CONTENT ).GETNUMBERVAL() AS ESS_NEW
  6          FROM RR_SRC_LEK_P5 P5
  7          JOIN RR_SRC_LEK_P6 P6
  8              ON ( INSTR(', ' || P5.LIST_OF_PARENT_CCN || ',',
  9                         ', ' || P6.PARENT_CCN || ',') > 0 )
 10          GROUP BY P5.C216) RES
 11  ON ( P5.C216 = RES.C216 )
 12  WHEN MATCHED THEN
 13  UPDATE SET P5.ESS_SUM = RES.ESS_NEW;

4 rows merged.
SQL>

Updated data

SQL> SELECT * FROM RR_SRC_LEK_P5;

C216     LIST_OF_PARENT_C                                 ESS_SUM
-------- ---------------- ---------------------------------------
00004146 4523401                                    2584186507711
00005354 5487228, 2103188                         135081237979919
00006203                                                        0
00007524 2196702, 2103188                         134828827247813
00009164 2163921, 4523401                           3997484321060

SQL>

DB<>Fiddle demo

Update

For your settings, you need to use following query:

MERGE INTO RR_SRC_LEK_P5 P5
    USING ( SELECT P5.C216,
                    XMLQUERY(
                      LISTAGG(
                      to_char(p6.ESS_SUM, 
                      'fm9999999999999999999999999999D099999999999999999',
                      'nls_numeric_characters = ''.,'''), '+') WITHIN GROUP (ORDER BY NULL)
                      RETURNING CONTENT ).GETNUMBERVAL() AS ESS_NEW
            FROM RR_SRC_LEK_P5 P5
            JOIN RR_SRC_LEK_P6 P6
                ON ( INSTR(', ' || P5.LIST_OF_PARENT_CCN || ',',
                          ', ' || P6.PARENT_CCN || ',') > 0 )
           GROUP BY P5.C216) RES
   ON ( P5.C216 = RES.C216 )
   WHEN MATCHED THEN
   UPDATE SET P5.ESS_SUM = RES.ESS_NEW;

Cheers!!