У меня есть входные данные, которые состоят из ID, prev, current и next node (не отсортированы). Мне нужно найти путь между первой и последней страницей для каждого идентификатора, который охватывает все пройденные узлы. например: если мои входные данные: первый столбец - ID, второй столбец - prev_node, третий - текущий node, четвертый столбец следующий node. Prev_node будет пустым для стартового значения, а следующий node будет пуст для последнего значения
вход
id prev current next 1 a b c 1 a e f 1 a b g 1 a b o 1 b c d 1 b g h 1 b o p 1 c d a 1 c b g 1 d a e 1 e f e 1 e f f 1 f e f 1 f f f 1 f f a 1 f a b 1 g h i 1 h i j 1 h j i 1 i j i 1 i i k 1 i k l 1 j i i 1 k l m 1 l m n 1 l n a 1 m n a 1 n a b 1 o p q 1 p q r 1 q r s 1 r s t 1 s t u 1 t u v 1 u v w 1 v w x 1 w x 1 a b
вывод должен быть контуром текущего node like -
ID current 1 a 1 b 1 c 1 d 1 a 1 e 1 f 1 e 1 f 1 f 1 f 1 a 1 b 1 b 1 g 1 h 1 i 1 j 1 j 1 i 1 i 1 k 1 l 1 m 1 n 1 n 1 a 1 b 1 o 1 p 1 q 1 r 1 s 1 t 1 u 1 v 1 w 1 x
Будет много идентификаторов с похожими данными здесь, я показал только один ID (1). Также здесь я использовал алфавиты, которые на самом деле будут длиной 200-500 символов. Я пробовал SQL-подход с небольшой модификацией, он отлично работает, если идентификатор имеет 100 или меньше строк, но дает ошибку конкатенации строк для большего количества строк (даже после преобразования длинной строки в число). Кто-нибудь может предложить надежный подход, основанный на процедурах. Я пробовал некоторые, но он не работает более 300 строк для ID.
Ошибка, с которой я иногда сталкиваюсь с нижеприведенным кодом, - "результат конкатенации строк слишком длинный"
мой код
create or replace procedure pathing
as
type varr is table of varchar(4000);
visit varr;
t number;
--v varchar2(40);
fp varchar2(1000);
np varchar2(1000);
type stype is record(fp varchar2(1000),np varchar2(1000),t number);
type sinput is table of stype;
iarray sinput;
begin
select id
bulk collect into visit
from table_source
group by id
order by count(1) desc;
delete from table_final;
commit;
for k in visit.first .. visit.last loop
delete from table_temp;
commit;
insert into table_temp
select distinct prev_pg, page_id, next_pg, visit(k)
from table_source
where visit_id = visit(k)
order by prev_pg desc;
commit;
insert into table_final
WITH t_n AS (
SELECT prev_pg, page_id, next_pg, rownum n FROM table_temp
),
t_br AS (
SELECT
prev_pg,
page_id,
'<' || listagg(n, '|<') within GROUP(ORDER BY n) || '|' br,
COUNT(0) cnt
FROM
t_n
GROUP BY
prev_pg, page_id
),
t_mp AS (
SELECT
'|' || listagg(list) within GROUP(ORDER BY NULL) list
FROM (
SELECT REPLACE(br, '<') list FROM t_br WHERE cnt > 1
)
),
t_path(step, page_id, next_pg, used) AS (
SELECT 1, page_id, next_pg, ''
FROM t_n
WHERE prev_pg is null
UNION ALL
SELECT
step + 1,
t_br.page_id,
t_n.next_pg,
CASE
WHEN instr(list, '|' || n || '|') = 0
THEN used
ELSE used || n || '|'
END
FROM
t_mp,
t_path
JOIN t_br
ON next_pg = t_br.page_id AND t_path.page_id = prev_pg
JOIN t_n
ON n = regexp_substr(br, '^(<(' || used || '0)\|)*(<(\d+))?', 1, 1, '', 4)
) cycle step
SET is_cycle TO 'Y' DEFAULT 'N'
SELECT
page_id,
next_pg,
step,
visit(k)
FROM t_path
ORDER BY 1;
commit;
end loop;
end;
Объясняя мой пример далее: - Я хочу полный путь пути каждого идентификатора, в примере я взял ID 1 в качестве примера. Для ID 1 мы имеем набор текущего, предыдущего и следующего значений. Поэтому нам нужно найти путь, используя эти значения. Например, для id 1 путь начинается с 'a', потому что предыдущий столбец пуст. то мы видим, что следующее значение a равно b, т.е. текущий - это a, а следующий - b, поэтому мы ищем во всех строках id 1 для предваряющего значения как a и current value как b, в точке мы находим то же самое, что и следующий значение строки и повторить процесс. Например, здесь prev a, current b и next - c, поэтому мы снова ищем предыдущие b и текущие c и так далее, пока не получим полный путь до тех пор, пока мы не встретим следующее как null, поскольку это будет последним
Решение с помощью предложений иерархического запроса представляется сложным, но должно быть решение, все еще как альтернатива, использовать существующий код PL/SQL, но изменить поле VARCHAR на CLOB, чтобы избежать "результата конкатенации строк слишком долго".
вы можете использовать следующий запрос в своей процедуре для достижения своей цели
select id,current into v_your_output_collection from nodes
where id = :vId
start with prev_node is null
connect by
NOCYCLE
prior next_node= current and prior current = prev_node
который работает в Oracle 10g и