Поочерёдное применение Replace к строке в иерархическом запросе

Question

Есть таблица table_1 следующего вида:

col_str	col_val	col_rep_val
AAA_BBB CCC ABC	AAA	111
AAA_BBB CCC ABC	BBB	222
AAA_BBB CCC ABC	CCC	333
DDDD AAAA OLOLO	DDDD	4444
DDDD AAAA OLOLO	AAAA	11

Я хочу сгруппировать эту таблицу по col_str так чтобы все значения col_val были заменены на значения col_rep_val. То есть в итоге получить выборку:

col_str	col_str_replaced
AAA_BBB CCC ABC	111_222 333 ABC
DDDD AAAA OLOLO	4444 11 OLOLO

Проблема в том, что мы не знаем сколько таких замен будет для каждой строки (порядок применения может быть любым). Я пробую использовать следующий рекурсивный запрос:

WITH tast_data AS (
  SELECT 'AAA_BBB CCC ABC' AS col_str, 'AAA' col_val, '111' col_rep_val FROM dual UNION ALL
  SELECT 'AAA_BBB CCC ABC' AS col_str, 'BBB' col_val, '222' col_rep_val FROM dual UNION ALL
  SELECT 'AAA_BBB CCC ABC' AS col_str, 'CCC' col_val, '333' col_rep_val FROM dual UNION ALL
  SELECT 'DDDD AAAA OLOLO' AS col_str, 'DDDD' col_val, '4444' col_rep_val FROM dual UNION ALL
  SELECT 'DDDD AAAA OLOLO' AS col_str, 'AAAA' col_val, '11' col_rep_val FROM dual
)
 SELECT col_str, col_val, col_rep_val, replace(col_str,col_val,col_rep_val) replaced_val, level
   FROM (SELECT tast_data.*
              , row_number() OVER (PARTITION BY col_str ORDER BY col_val) AS rn
           FROM tast_data
)
  START WITH rn = 1
CONNECT BY PRIOR col_str = col_str
       AND PRIOR sys_guid() IS NOT NULL
       AND level = rn;

План в том чтобы получить следующую выборку:

col_str	col_val	col_rep_val	replaced_val	level
AAA_BBB CCC ABC	AAA	111	111_BBB CCC ABC	1
AAA_BBB CCC ABC	BBB	222	111_222 CCC ABC	2
AAA_BBB CCC ABC	CCC	333	111_222 333 ABC	3
DDDD AAAA OLOLO	DDDD	4444	4444 AAAA OLOLO	1
DDDD AAAA OLOLO	AAAA	11	4444 11 OLOLO	2

Откуда вытянуть уже итоговое значение (с максимальным level). Но мой запрос вернет мне несколько иное:

col_str	col_val	col_rep_val	replaced_val	level
AAA_BBB CCC ABC	AAA	111	111_BBB CCC ABC	1
AAA_BBB CCC ABC	BBB	222	AAA_222 CCC ABC	2
AAA_BBB CCC ABC	CCC	333	AAA_BBB 333 ABC	3
DDDD AAAA OLOLO	DDDD	4444	4444 AAAA OLOLO	1
DDDD AAAA OLOLO	AAAA	11	DDDD 11 OLOLO	2

То есть каждый раз он будет реплейсить именно изначальную строку. Вопрос в том, как брать именно результат предыдущей замены при каждой последующей? Очевидно, невозможно использовать prior replaced_val вместо col_str, здесь replaced_val будет инвалидным указателем.

Answer 1

Схематично.

WITH 
cte1  AS (
    SELECT *, ROW_NUMBER() OVER () rn
    FROM patterns
    ),
cte2 AS (
    SELECT id, string, 1 rn 
    FROM strings
  UNION ALL
    SELECT id, REPLACE(cte2.string, cte1.pattern, cte1.replacement), cte2.rn + 1 
    FROM cte2
    JOIN cte1 ON cte1.rn = cte2.rn
    )
SELECT *
FROM cte2
WHERE rn > ( SELECT MAX(rn)
             FROM cte1
             )

Answer 2

Подробно описал подготовку к рекурсии и описание самой рекурсии.

WITH test_data AS (
SELECT 
  --добавим аналог ПК, чтобы было понятно в каком порядке применять паттерны
  ROW_NUMBER()OVER(ORDER BY 1) AS id, 
  T.* 
FROM(
  SELECT 'AAA_BBB CCC ABC' AS col_str, 'AAA' col_val, '111' col_rep_val FROM dual UNION ALL
  SELECT 'AAA_BBB CCC ABC' AS col_str, 'BBB' col_val, '222' col_rep_val FROM dual UNION ALL
  SELECT 'AAA_BBB CCC ABC' AS col_str, 'CCC' col_val, '333' col_rep_val FROM dual UNION ALL
  SELECT 'DDDD AAAA OLOLO' AS col_str, 'DDDD' col_val, '4444' col_rep_val FROM dual UNION ALL
  SELECT 'DDDD AAAA OLOLO' AS col_str, 'AAAA' col_val, '11' col_rep_val FROM dual
)T
)

,CTE AS(
SELECT 
  --номер обрабатываемой строки(группы)
  DENSE_RANK()OVER(ORDER BY COL_STR)M,
  --номер паттерна в группе (он же шаг в рекурсии)
  ROW_NUMBER()OVER(PARTITION BY COL_STR ORDER BY id)step,
  --количество паттернов в группе
  COUNT(*)OVER(PARTITION BY COL_STR)lvl,
  td.*
FROM test_data td
)

/*

имеем такой предварительный результат перед рекурсией

M   STEP    LVL ID  COL_STR COL_VAL COL_REP_VAL
1   1   3   1   AAA_BBB CCC ABC AAA 111
1   2   3   2   AAA_BBB CCC ABC BBB 222
1   3   3   3   AAA_BBB CCC ABC CCC 333
2   1   2   4   DDDD AAAA OLOLO DDDD    4444
2   2   2   5   DDDD AAAA OLOLO AAAA    11

*/

--Собственно рекурсия

,RCTE (M, step, lvl, col_str, col_str_after_replace) AS(
  
  SELECT M, step, lvl, col_str, REPLACE(col_str, col_val, col_rep_val)curr_col_str
  --в каждой группе берём первый паттерн
  FROM CTE WHERE step = 1
  
  UNION ALL
  
  SELECT CTE.M, CTE.step, CTE.lvl, CTE.col_str, REPLACE(col_str_after_replace, col_val, col_rep_val)col_str_after_replace
  --берём следующий паттерн замены для каждой группы
  FROM RCTE JOIN CTE ON RCTE.step+1 = CTE.step AND RCTE.M = CTE.M
  
)

SELECT *
FROM RCTE 
WHERE step = lvl

/*
И итоговый результат:

M   STEP    LVL COL_STR COL_STR_AFTER_REPLACE
2   2   2   DDDD AAAA OLOLO 4444 11 OLOLO
1   3   3   AAA_BBB CCC ABC 111_222 333 ABC

*/

Answer 3

Еще один вариант рекурсии:

with r(lvl,col_str,col_str_mod) as(
select  0 lvl,col_str as col_str
   ,replace(col_str,trim(col_val),trim(col_rep_val)) col_str_mod
from  test
  union all
select r.lvl+1 lvl,r.col_str
    ,replace(r.col_str_mod,trim(tr.col_val),trim(tr.col_rep_val)) col_str_mod
from r inner join test tr on r.col_str=tr.col_str
     and  instr(r.col_str_mod,trim(tr.col_val))>0
)
select *
from(
select r.* 
  ,row_number()over(partition by col_str order by lvl ) as n 
  ,count(*)over(partition by col_str) as cnt
from r
  )
where n=cnt

БЛОГ НА HUSL

Поочерёдное применение Replace к строке в иерархическом запросе

Ответы (3 шт):