재귀 하위 쿼리 팩터링을 사용한 주기 감지
Oracle SQL은 고유한 CONNECT BY 구문을 사용하여 v2 이후 계층 쿼리를 수행할 수 있습니다.그들의 최신 11g 릴리스 2에서, 그들은 recursive with 절이라고도 알려진 재귀 하위 쿼리 팩터링을 추가했습니다.이것이 ANSI 표준이며, 제가 정확히 이해한 바로는 다른 RDBMS 공급업체에서도 이 표준을 구현했습니다.
connect-by와 recursive를 비교했을 때 사이클 검출을 사용했을 때 결과 세트가 다르다는 것을 알게 되었습니다.결과에 의한 연결은 저에게 더 직관적이기 때문에 Oracle 구현에 버그가 포함되어 있는지, 아니면 이것이 표준 ANSI 및 예상 동작인지 궁금합니다.따라서 MySQL, DB2, SQL Server 등의 다른 데이터베이스를 사용하여 재귀 쿼리를 확인할 수 있는지 문의드립니다.이러한 데이터베이스가 recursive with 물론 절을 지원한다면,
Oracle 11.2.0.1.0에서 작동하는 방법은 다음과 같습니다.
SQL> select *
2 from t
3 /
ID PARENT_ID
---------- ----------
1 2
2 1
2 rows selected.
CONNECT BY 구문을 사용한 쿼리:
SQL> select id
2 , parent_id
3 , connect_by_iscycle
4 from t
5 connect by nocycle parent_id = prior id
6 start with id = 1
7 /
ID PARENT_ID CONNECT_BY_ISCYCLE
---------- ---------- ------------------
1 2 0
2 1 1
2 rows selected.
제가 보기엔 직관적인 것 같은데요.그러나 새 ANSI 구문을 사용하면 행이 하나 더 반환됩니다.
SQL> with tr (id,parent_id) as
2 ( select id
3 , parent_id
4 from t
5 where id = 1
6 union all
7 select t.id
8 , t.parent_id
9 from t
10 join tr on t.parent_id = tr.id
11 ) cycle id set is_cycle to '1' default '0'
12 select id
13 , parent_id
14 , is_cycle
15 from tr
16 /
ID PARENT_ID I
---------- ---------- -
1 2 0
2 1 0
1 2 1
3 rows selected.
확인할 수 있는 스크립트는 다음과 같습니다.
create table t
( id number
, parent_id number
);
insert into t values (1, 2);
insert into t values (2, 1);
commit;
with tr (id,parent_id) as
( select id
, parent_id
from t
where id = 1
union all
select t.id
, t.parent_id
from t
join tr on t.parent_id = tr.id
) cycle id set is_cycle to '1' default '0'
select id
, parent_id
, is_cycle
from tr;
문서의 출처:
그
CONNECT_BY_ISCYCLE반환 사의칼수1이기도 한
그리고 그 위에:
행의 상위 행 중 하나가 사이클 열에 대해 동일한 값을 가질 경우 행이 사이클을 형성하는 것으로 간주됩니다.
행 제에서행예를 합니다.2그것의 조상이기도 한 아이를 가지고 있지만, 그것의id아직 반환되지 않았습니다.
다른말하면로,하면,CONNECT_BY_ISCYCLE(아직 반환되지 않은) 자녀를 확인하는 동안CYCLE현재 행(이미 반환됨)을 확인합니다.
CONNECT BY는 행 기반인 재귀적인 경우입니다.CTE는 설정 기반입니다.
라클 같과 다니습에 대한 하십시오.CYCLE에서는 "경고 행"을 언급합니다.그러나 일반적으로 재귀적으로 "조상 행"의 개념은 없습니다.CTE트리에서 완전히 결과를 도출할 수 있는 세트 기반 작업입니다.일반적으로 앵커 파트와 재귀 파트는 서로 다른 테이블을 사용할 수도 있습니다.
재귀적이므로CTE'는 보통 계층 트리를 만드는 데 사용됩니다.Oracle주기 검사를 추가하기로 결정했습니다. 기반 재귀적인 하만세기방로인으해식재방은식인적귀지반트.CTE가 작동합니다. 일반적으로 다음 단계에서 주기가 생성되는지 여부를 구분하는 것은 불가능합니다. "주기 행" 주기 조건에 대한 명확한 정의가 없으면 정의할 수 없기 때문입니다.
"다음" 단계를 수행하려면 전체 "현재" 세트를 사용할 수 있어야 하지만, 현재 세트의 각 행(사이클 열 포함)을 생성하려면 "다음" 작업 결과만 있으면 됩니다.
(예:CONNECT BY), 그러나 재귀 연산이 집합 전체에 정의된 경우에는 문제가 됩니다.
에 대해 Oracle 11SQL Server 재적현CTE를 숨기는 CONNECT BY그 뒤에 수많은 제한을 두어야 합니다(모든 집합 기반 작업을 효과적으로 금지).
PostgreSQL그러나 의 구현은 진정한 세트 기반입니다. 재귀 부분의 앵커 부분으로 모든 작업을 수행할 수 있습니다.그러나 사이클은 애초에 정의되지 않았기 때문에 사이클을 감지할 수 있는 수단이 없습니다.
처럼 아말린것처럼드씀까것럼▁as처.MySQL를) 구현하지 않습니다.CTE 구현하지 .HASH JOIN의 또는MERGE JOIN또한, 중첩된 루프만 있으므로 크게 놀라지 마십시오.)
아이러니하게도, 저는 제 블로그에서 다룰 바로 이 주제에 대한 편지를 오늘 받았습니다.
업데이트:
재적CTE에 있는SQL Server 않습니다.CONNECT BY자세한 의 이 하십시오.충격적인 자세한 내용은 블로그의 이 기사를 참조하십시오.
PostgreSQL은 WITH 스타일 계층 쿼리를 지원하지만 자동 주기 탐지 기능은 없습니다.즉, 직접 작성해야 하며 반환되는 행 수는 쿼리의 재귀 부분에서 조인 조건을 지정하는 방법에 따라 달라집니다.
두 예제 모두 ID(all_ids라고 함)가 루프를 탐지하는 경우 배열을 사용합니다.
WITH recursive tr (id, parent_id, all_ids, cycle) AS (
SELECT id, parent_id, ARRAY[id], false
FROM t
WHERE id = 1
UNION ALL
SELECT t.id, t.parent_id, all_ids || t.id, t.id = ANY(all_ids)
FROM t
JOIN tr ON t.parent_id = tr.id AND NOT cycle)
SELECT id, parent_id, cycle
FROM tr;
id | parent_id | cycle
----+-----------+-------
1 | 2 | f
2 | 1 | f
1 | 2 | t
WITH recursive tr (id, parent_id, all_ids, cycle) AS (
SELECT id, parent_id, ARRAY[id], false
FROM t
WHERE id = 1
UNION ALL
SELECT t.id, t.parent_id, all_ids || t.id, (EXISTS(SELECT 1 FROM t AS x WHERE x.id = t.parent_id))
FROM t
JOIN tr ON t.parent_id = tr.id
WHERE NOT t.id = ANY(all_ids))
SELECT id, parent_id, cycle
FROM tr;
id | parent_id | cycle
----+-----------+-------
1 | 2 | f
2 | 1 | t
AFAIK:
- MySQL은 재귀 CTE를 지원하지 않습니다.
- SQL Sever는 재귀 CTE에서 주기 탐지를 지원하지 않습니다.
의 연결 결과가 항상 직관적이지는 않을 수 있습니다.
아래 쿼리는 다음으로 시작하는 사이클을 감지하기 위한 다양한 접근 방식을 보여줍니다.id = 3사진의 그래프에 대한.
create table graph (id, id_parent) as
(select 2, 1 from dual
union all select 3, 1 from dual
union all select 4, 3 from dual
union all select 5, 4 from dual
union all select 3, 5 from dual)
SQL> select level lvl, graph.*, connect_by_iscycle cycle
2 from graph
3 start with id = 3
4 connect by nocycle prior id = id_parent;
LVL ID ID_PARENT CYCLE
---------- ---------- ---------- ----------
1 3 1 0
2 4 3 0
3 5 4 1
1 3 5 0
2 4 3 0
3 5 4 1
6 rows selected.
SQL> select level lvl, graph.*, connect_by_iscycle cycle
2 from graph
3 start with id = 3
4 connect by nocycle prior id = id_parent
5 and prior id_parent is not null;
LVL ID ID_PARENT CYCLE
---------- ---------- ---------- ----------
1 3 1 0
2 4 3 0
3 5 4 0
4 3 5 1
1 3 5 0
2 4 3 0
3 5 4 1
7 rows selected.
SQL> with t(id, id_parent) as
2 (select *
3 from graph
4 where id = 3
5 union all
6 select g.id, g.id_parent
7 from t
8 join graph g
9 on t.id = g.id_parent)
10 search depth first by id set ord
11 cycle id set cycle to 1 default 0
12 select * from t;
ID ID_PARENT ORD C
---------- ---------- ---------- -
3 1 1 0
4 3 2 0
5 4 3 0
3 5 4 1
3 5 5 0
4 3 6 0
5 4 7 0
3 5 8 1
8 rows selected.
가 함노인 경우id = 3에는 두 개의 부모가 있으므로 Oracle은 이 예에서 두 번의 주기를 거칩니다.
(1, 3) -> (3, 4) -> (4, 5) -> (5, 3)
그리고.
(5, 3) -> (3, 4) -> (4, 5)
첫 번째 쿼리 및 첫 번째 사이클의 결과에서 에지(5, 3)가 누락되었습니다.동시에 에지(5, 3)가 세 번째 쿼리와 두 번째 사이클의 결과에 두 번 나타납니다.
왜 그럴까요? Quassnoi에서 제공하는 답변에서 사이클 감지 로직에 대한 설명을 확인할 수 있습니다.쉬운 영어로 그것은 의미합니다.
connect by는 현재 행의 하위 ID가 지금까지 방문한 ID의 일부인 경우 주기를 감지합니다.
rec with는 현재 행의 ID가 지금까지 방문한 ID의 일부인 경우 사이클을 감지합니다.
스러워 보입니다.and prior id_parent is not null 경우에는 .
현재 행의 ID가 지금까지 방문한 부모 ID의 일부인 경우 주기를 감지합니다.
이 모든 조건은 아래 쿼리의 cnt1, cnt2, cnt3 열에 구현됩니다.
SQL> with t(id, id_parent, path_id, path_id_parent, cnt1, cnt2, cnt3) as
2 (select g.*,
3 cast('->' || g.id as varchar2(4000)),
4 cast('->' || g.id_parent as varchar2(4000)),
5 0,
6 0,
7 0
8 from graph g
9 where id = 3
10 union all
11 select g.id,
12 g.id_parent,
13 t.path_id || '->' || g.id,
14 t.path_id_parent || '->' || g.id_parent,
15 regexp_count(t.path_id || '->', '->' ||
16 (select id from graph c where c.id_parent = g.id) || '->'),
17 regexp_count(t.path_id || '->', '->' || g.id || '->'),
18 regexp_count(t.path_id_parent || '->', '->' || g.id || '->')
19 from t
20 join graph g
21 on t.id = g.id_parent
22 -- and t.cnt1 = 0
23 -- and t.cnt2 = 0
24 -- and t.cnt3 = 0
25 )
26 search depth first by id set ord
27 cycle id set cycle to 1 default 0
28 select * from t;
ID ID_PARENT PATH_ID PATH_ID_PARENT CNT1 CNT2 CNT3 ORD C
---------- ---------- --------------- --------------- ---- ---- ---- ---------- -
3 1 ->3 ->1 0 0 0 1 0
4 3 ->3->4 ->1->3 0 0 0 2 0
5 4 ->3->4->5 ->1->3->4 1 0 0 3 0
3 5 ->3->4->5->3 ->1->3->4->5 1 1 1 4 1
3 5 ->3 ->5 0 0 0 5 0
4 3 ->3->4 ->5->3 0 0 0 6 0
5 4 ->3->4->5 ->5->3->4 1 0 1 7 0
3 5 ->3->4->5->3 ->5->3->4->5 1 1 1 8 1
8 rows selected.
cnt1/cnt2/cnt3로 필터의 주석을 제거하고 "cycle id set cycle to 1 default 0"을 제거하면 쿼리가 위의 해당 쿼리로 결과를 반환합니다.즉, 보다 직관적인 사이클 감지 로직을 피하고 구현할 수 있습니다.
계층 이동 및 주기 탐지에 대한 자세한 내용은 Oracle SQL Reflosed(Oracle SQL 노출)에서 확인할 수 있습니다.
5는 MySQL Server를 좋아하지 with:
오류 1064(42000):SQL 구문에 오류가 있습니다. MySQL 서버 버전에 해당하는 설명서에서 1행에서 'with tr(id, parent_id)'를 (id = 1 union alls'로 선택)근처에서 사용할 올바른 구문을 확인하십시오.
WITH RECURSIVE s (master, slave, all_ids, cycle) AS
(
SELECT master, slave, ARRAY[master], false FROM binding WHERE master=3477
UNION ALL
SELECT d.master, d.slave, all_ids || d.master, d.slave = ANY(all_ids)
FROM
binding AS d
JOIN
s
ON (d.master = s.slave)
WHERE NOT d.master = ANY(all_ids)
)
SELECT *
FROM s;
는 이 가 더 합니다.d.slave = ANY(all_ids)
"따라서 질문은 MySQL, DB2, SQL Server 등 다른 데이터베이스를 사용하여 재귀 쿼리를 확인할 수 있는지 여부입니다."
MariaDB 10.5.2 이상의 지원 주기 감지:
CYCLE 절은 과도하거나 무한한 루프를 방지하여 CTE 주기 감지를 가능하게 하며, MariaDB는 완화된 비표준 문법을 지원합니다.
WITH RECURSIVE ... ( ... ) CYCLE <cycle column list> RESTRICT
예:
CREATE TABLE t(id INT, parent_id INT);
INSERT INTO t(id, parent_id) VALUES (1, NULL),(2,1),(3,2),(1,3);
WITH RECURSIVE cte AS (
SELECT id, parent_id, 0 lvl
FROM t WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.parent_id, lvl + 1 AS lvl
FROM cte c1
JOIN t ON c1.id = t.parent_id
)
CYCLE id, parent_id RESTRICT
SELECT * FROM cte ORDER BY lvl;
언급URL : https://stackoverflow.com/questions/1731889/cycle-detection-with-recursive-subquery-factoring
'programing' 카테고리의 다른 글
| 노드 JS에서 셀러리와 동등한 값 (0) | 2023.06.19 |
|---|---|
| Python의 열린 파일에서 경로 가져오기 (0) | 2023.06.19 |
| 루비에게 가장 좋고 쉬운 GUI 라이브러리는 무엇입니까? (0) | 2023.06.19 |
| JSON을 저장소/전송 형식으로 사용하는 데이터베이스 (0) | 2023.06.19 |
| Capistrano를 사용하여 배포할 때 Wordpress 및 플러그인을 업그레이드하는 방법은 무엇입니까? (0) | 2023.06.19 |