Divide by couples of elements in a table-CodePudding

I have this table:

ELEMENTO            VALOR
-------------------------
ELEMENT1_SUFFIX1    2
ELEMENT1_SUFFIX2    4
ELEMENT2_SUFFIX1    5
ELEMENT2_SUFFIX2    15

I would like to generate new entries by dividing each couple of elements and suffixes. For each partition of element, I would like to divide suffix2/suffix1 and generate an entry with element_result. Like the below.

ELEMENTO            VALOR
-------------------------
ELEMENT1_SUFFIX1    2
ELEMENT1_SUFFIX2    4
ELEMENT2_SUFFIX1    5
ELEMENT2_SUFFIX2    15
ELEMENT1_RESULT     2
ELEMENT1_RESULT     3

The table can be generated with the below code.

with aux (elemento, valor) as
  ( select 'ELEMENT1_SUFFIX1', 2 from dual
  
  UNION ALL
  
  select 'ELEMENT1_SUFFIX2', 4 from dual
  
  UNION ALL
  
  select 'ELEMENT2_SUFFIX1', 5 from dual
  
  UNION ALL
  
  select 'ELEMENT2_SUFFIX2', 10 from dual
  )
select aux.* from aux;

This is what I've tried so far, but I am pretty sure there must be a better solution.

with aux (elemento, valor) as
  ( select 'ELEMENT1_SUFFIX1', 2 from dual
  
  UNION ALL
  
  select 'ELEMENT1_SUFFIX2', 4 from dual
  
  UNION ALL
  
  select 'ELEMENT2_SUFFIX1', 5 from dual
  
  UNION ALL
  
  select 'ELEMENT2_SUFFIX2', 15 from dual
  ),
  aux2 as
  (select aux.*,
    valor/max(
    case
      when regexp_substr(elemento, '[^_] ', 1, 2) = 'SUFFIX1'
      then valor
    end) over (partition by REGEXP_SUBSTR(elemento,'[^_] ',1,1)) new_value
  from aux
  )
select * from aux

union all

select REGEXP_SUBSTR(elemento,'[^_] ',1,1)
  || '_RESULT',
  new_value valor
from aux2
where regexp_substr(elemento, '[^_] ', 1, 2) = 'SUFFIX2';

CodePudding user response：

Yet another variation.

Sample data:

SQL> WITH
  2     aux (elemento, valor)
  3     AS
  4        (SELECT 'ELEMENT1_SUFFIX1', 2 FROM DUAL
  5         UNION ALL
  6         SELECT 'ELEMENT1_SUFFIX2', 4 FROM DUAL
  7         UNION ALL
  8         SELECT 'ELEMENT2_SUFFIX1', 5 FROM DUAL
  9         UNION ALL
 10         SELECT 'ELEMENT2_SUFFIX2', 15 FROM DUAL
 11        )

Query:

 12  -- existing rows
 13  select elemento, valor
 14    from aux
 15  union all
 16  -- calculated rows
 17  select
 18    substr(elemento, 1, instr(elemento, '_') - 1) ||'_RESULT' as elemento,
 19    max(case when substr(elemento, -1) = 2 then valor end) /
 20    max(case when substr(elemento, -1) = 1 then valor end) as valor
 21  from aux
 22  group by substr(elemento, 1, instr(elemento, '_') - 1);

ELEMENTO                     VALOR
----------------------- ----------
ELEMENT1_SUFFIX1                 2
ELEMENT1_SUFFIX2                 4
ELEMENT2_SUFFIX1                 5
ELEMENT2_SUFFIX2                15
ELEMENT1_RESULT                  2
ELEMENT2_RESULT                  3

6 rows selected.

SQL>

CodePudding user response：

We can use an aggregation/pivot approach along with a union here:

WITH cte AS (
    SELECT SUBSTR(ELEMENTO, 1, INSTR(ELEMENTO, '_') - 1) || '_RESULT' AS ELEMENTO,
           MAX(CASE WHEN SUBSTR(ELEMENTO, INSTR(ELEMENTO, '_')   1) = 'SUFFIX2'
                    THEN VALOR END) /
           MAX(CASE WHEN SUBSTR(ELEMENTO, INSTR(ELEMENTO, '_')   1) = 'SUFFIX1'
                    THEN VALOR END) AS VALOR
    FROM yourTable
    GROUP BY SUBSTR(ELEMENTO, 1, INSTR(ELEMENTO, '_') - 1)
)

SELECT ELEMENTO, VALOR
FROM
(
    SELECT ELEMENTO, VALOR, 1 AS pos FROM yourTable
    UNION ALL
    SELECT ELEMENTO, VALOR, 2 FROM cte
) t
ORDER BY pos, ELEMENTO;

CodePudding user response：

Try this:

with aux (elemento, valor) as
  ( select 'ELEMENT1_SUFFIX1', 2 from dual
  
  UNION ALL
  
  select 'ELEMENT1_SUFFIX2', 4 from dual
  
  UNION ALL
  
  select 'ELEMENT2_SUFFIX1', 5 from dual
  
  UNION ALL
  
  select 'ELEMENT2_SUFFIX2', 15 from dual
  ), rawdata (elemento, valor, group_id, row_id) AS
  (
select aux.*
      ,DENSE_RANK() OVER (ORDER BY SUBSTR(elemento, 1, INSTR(elemento, '_')-1))
      ,ROW_NUMBER() OVER (PARTITION BY SUBSTR(elemento, 1, INSTR(elemento, '_')-1) ORDER BY SUBSTR(elemento, INSTR(elemento, '_') 1))
from aux
    )
    SELECT *
    FROM aux
    
    UNION ALL
    
    SELECT CONCAT(CONCAT('ELEMENT', A.group_id),'_RESULT')
          ,B.valor / a.valor
    FROM rawdata A
    INNER JOIN rawdata B
       ON A.group_id = b.group_id
       AND A.row_id = b.row_id -1