programing

간단한 SQL 코드로 마리아DB의 NOCYLE(오라클) 계층 쿼리를 사용하여 CONNECT BY

telebox 2023. 6. 11. 10:34
반응형

간단한 SQL 코드로 마리아DB의 NOCYLE(오라클) 계층 쿼리를 사용하여 CONNECT BY

고유한 nocycle 솔루션이 있는 계층적 쿼리가 제공됩니다.개선이 필요합니다.

루프가 있거나 없는 트리(Oidipus)를 가정합니다.표:

CREATE TABLE `person` (
  `ID` varchar(10) NOT NULL,
  `PARENT` varchar(10) NOT NULL,
  `TYPE` varchar(10) NOT NULL,
  `NAME` varchar(50) NOT NULL
)

TYPE 및 NAME 필드는 중요하지 않습니다.PARENT 필드에서 다른 사용자의 ID로 연결이 실현됩니다.

  1. 부모 찾기:
WITH recursive Parents(ID, SUMID, TYPE, PARENT, LEVEL) AS (
  SELECT ID, Concat(ID,"Z","                  ...") AS SUMID, TYPE, PARENT, 0 AS LEVEL FROM `person` WHERE ID = '1000000005'
  UNION ALL
  SELECT m.ID, CONCAT(m.ID,"Z",SUMID) AS SUMID , m.TYPE, m.PARENT, LEVEL + 1 FROM `person` as m INNER JOIN Parents t on m.ID = t.PARENT
  WHERE LEVEL < 6
  AND INSTR ( SUMID, m.ID) < 1
)
SELECT * FROM Parents;

NOCCYCLE을 확인하기 위해 추가 열 SUMID(연결된 "유효한" ID, 구분 기호="Z")가 사용됩니다(오라클 키워드 참조).Oidipus는 필드 ID에 한 번만 나타납니다.정상적으로 작동하지만 SUMID 초기 내용은 MAX LEVEL 곱하기 10 "String"으로 코딩되어야 합니다.

부분적으로만 작동하는 것:

  1. 모든 자식 찾기
WITH recursive Children(ID, SUMID, TYPE, PARENT, LEVEL) AS (
  SELECT ID, Concat(ID,"Z","                  ...") AS SUMID, TYPE, PARENT, 0 AS LEVEL FROM `person` WHERE ID = '1000000002'
  UNION ALL
  SELECT m.ID, CONCAT(m.ID,"Z",SUMID) AS SUMID , m.TYPE, m.PARENT, LEVEL + 1 FROM `person` as m INNER JOIN Children t on m.PARENT = t.ID
  WHERE LEVEL < 6
  AND INSTR ( SUMID, m.ID) < 1
)
SELECT * FROM Children;

어떤 사람이 5명의 자녀와 5*5 = 25명의 손자손녀를 둔 경우, SUMID는 충분히 길 수 없습니다.게다가, 모든 어린이들을 위한 대본은 매우 느리고 공연에 약합니다.간단한 MySQL에서 "모든 어린이 찾기"를 어떻게 구현할 수 있습니까?

트리 레코드 구조에 대한 계층적 쿼리를 구현하려고 했습니다."자녀 찾기" 쿼리는 느리고 효율적이지 않습니다.저는 개선 제안을 기대하고 있습니다.

INSERT INTO `person` (`ID`, `PARENT`, `TYPE`, `NAME`) VALUES
('1000000001', '1000000001', 'A', 'first'),
('1000000002', '1000000004', 'B', 'second'),
('1000000003', '1000000002', 'C', 'third'),
('1000000004', '1000000002', 'C', 'fourth'),
('1000000005', '1000000004', 'C', 'fifth'),
('1000000006', '1000000002', 'C', '6th'),
('1000000007', '1000000002', 'C', '7th'),
('1000000008', '1000000002', 'C', '8th'),
('1000000009', '1000000002', 'C', '9th'),
('1000000010', '1000000005', 'D', '10th'),
('1000000011', '1000000005', 'D', '11th'),
('1000000012', '1000000005', 'D', '12nd'),
('1000000013', '1000000005', 'D', '13rd');

결과:

MariaDB [devmysql]> WITH recursive Children(ID, SUMID, TYPE, PARENT, LEVEL) AS (
    ->   SELECT ID, Concat(ID,"Z","                  ") AS SUMID, TYPE, PARENT, 0 AS LEVEL FROM `person` WHERE ID = '1000000002'
    ->   UNION ALL
    ->   SELECT m.ID, CONCAT(m.ID,"Z",SUMID) AS SUMID , m.TYPE, m.PARENT, LEVEL + 1 FROM `person` as m INNER JOIN Children t on m.PARENT = t.ID
    ->   WHERE LEVEL < 6
    ->   AND INSTR ( SUMID, m.ID) < 1
    -> )
    -> SELECT * FROM Children;
+------------+-------------------------------+------+------------+-------+
| ID         | SUMID                         | TYPE | PARENT     | LEVEL |
+------------+-------------------------------+------+------------+-------+
| 1000000002 | 1000000002Z                   | B    | 1000000004 |     0 |
| 1000000003 | 1000000003Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000004 | 1000000004Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000006 | 1000000006Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000007 | 1000000007Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000008 | 1000000008Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000009 | 1000000009Z1000000002Z        | C    | 1000000002 |     1 |
| 1000000005 | 1000000005Z1000000004Z1000000 | C    | 1000000004 |     2 |
| 1000000010 | 1000000010Z1000000005Z1000000 | D    | 1000000005 |     3 |
| 1000000011 | 1000000011Z1000000005Z1000000 | D    | 1000000005 |     3 |
| 1000000012 | 1000000012Z1000000005Z1000000 | D    | 1000000005 |     3 |
| 1000000013 | 1000000013Z1000000005Z1000000 | D    | 1000000005 |     3 |
+------------+-------------------------------+------+------------+-------+
12 rows in set, 11 warnings (0.004 sec)

Oracle과 동등한 기능은 다음과 같습니다(아래 참조).
힌트
신탁으로

  • UNION ALL은 필수 사항. 그렇지 않으면 ORA-32040: recursive WITH 절은 UNION ALL 연산을 사용해야 합니다.
  • 매개 변수 목록은 필수 사항. 그렇지 않으면 ORA-32039: 재귀 WITH 절에 열 별칭 목록이 있어야 합니다.
  • recursive 생략 그렇지 않으면 systax 오류
  • LEVEL은 Oracle에서 키워드입니다. 즉, LEV를 대신 사용합니다.
WITH Children (ID, SUMID, LEVEL) 
AS
(
  SELECT
    m.ID,
    ',' || CAST(m.ID AS VARCHAR(120) || ','  AS SUMID,
    0 AS LEV
  FROM
    person  AS m
  WHERE
    ID = '1000000002'
  UNION ALL
  SELECT
    m.ID,
    t.SUMID || m.ID || ','  AS SUMID,
    LEV + 1 AS LEV
  FROM
    person AS m
  INNER JOIN
    Children AS t
      ON m.PARENT = t.ID
  WHERE
      t.LEV < 10
    AND INSTR(t.SUMID, CONCAT(',', m.ID, ',')) < 1
)
SELECT * FROM Children;

쿼리가 CA를 전달했습니다.600밀리초 안에 6만 개의 레코드가 있습니다.
쿼리는 양방향으로 MINUS를 가진 오라클 상대에 의해 연결에 대해 테스트되었습니다.

select ID
FROM person 
  START WITH ID = '1000000002'
  CONNECT BY NOCYCLE PRIOR ID = PARENT
MINUS
SELECT ID FROM (
WITH Children (ID, SUMID, LEVEL) 
AS
(
  SELECT
    m.ID,
    ',' || CAST(m.ID AS VARCHAR(120) || ','  AS SUMID,
    0 AS LEV
  FROM
    person  AS m
  WHERE
    ID = '1000000002'
  UNION ALL
  SELECT
    m.ID,
    t.SUMID || m.ID || ','  AS SUMID,
    LEV + 1 AS LEV
  FROM
    person AS m
  INNER JOIN
    Children AS t
      ON m.PARENT = t.ID
  WHERE
      t.LEV < 10
    AND INSTR(t.SUMID, CONCAT(',', m.ID, ',')) < 1
)
SELECT * FROM Children);

레코드를 배달하지 않습니다.
쿼리가 1초 만에 Oracle에서 성공적으로 테스트되었습니다.나로서는 믿을 수 없는 빠른.
"Naja", Connectby는 자동으로 다음과 같은 몇 가지 기능을 제공합니다.

  • CONNECT_BY_ISCYCLE
  • CONNECT_BY_ISLEAF
  • 레벨
    [참조: 오라클 문서][1]https://docs.oracle.com/cd/B12037_01/server.101/b10759/pseudocolumns001.htm#i1009434 ]

LEVEL과 SUMID를 출력할 필요가 없는 한 UNION ALL 대신 UNION을 사용하여 루프가 평가되지 않도록 할 수 있습니다.

CREATE TABLE `person` (
  `ID` varchar(10) NOT NULL,
  `PARENT` varchar(10) NOT NULL,
  `TYPE` varchar(10) NOT NULL,
  `NAME` varchar(50) NOT NULL
)

INSERT INTO `person` (`ID`, `PARENT`, `TYPE`, `NAME`) VALUES
('1000000001', '1000000001', 'A', 'first'),
('1000000002', '1000000004', 'B', 'second'),
('1000000003', '1000000002', 'C', 'third'),
('1000000004', '1000000002', 'C', 'fourth'),
('1000000005', '1000000004', 'C', 'fifth'),
('1000000006', '1000000002', 'C', '6th'),
('1000000007', '1000000002', 'C', '7th'),
('1000000008', '1000000002', 'C', '8th'),
('1000000009', '1000000002', 'C', '9th'),
('1000000010', '1000000005', 'D', '10th'),
('1000000011', '1000000005', 'D', '11th'),
('1000000012', '1000000005', 'D', '12nd'),
('1000000013', '1000000005', 'D', '13rd')
;

Records: 13  Duplicates: 0  Warnings: 0

WITH
  RECURSIVE
    Children
AS
(
  SELECT * FROM `person` WHERE ID = '1000000002'
  UNION
  SELECT m.* FROM `person` as m INNER JOIN Children t on m.PARENT = t.ID
)
SELECT * FROM Children
아이디 부모 유형 이름.
1000000002 1000000004 B 둘째
1000000003 1000000002 C 셋째
1000000004 1000000002 C 제4의
1000000006 1000000002 C 6번째
1000000007 1000000002 C 7번째
1000000008 1000000002 C 8번째
1000000009 1000000002 C 9번째
1000000005 1000000004 C 다섯 번째의
1000000010 1000000005 D 10번째
1000000011 1000000005 D 11일
1000000012 1000000005 D 12번째
1000000013 1000000005 D 13위

만지작거리다

언급URL : https://stackoverflow.com/questions/75192297/connect-by-with-nocycle-oracle-hierarhical-query-in-mariadb-with-simple-sql

반응형

'programing' 카테고리의 다른 글

포크 이후 하위 프로세스 디버깅(팔로우포크 모드 하위 구성)  (0) 2023.06.11
data.frame에서 단일 열의 이름을 변경하는 방법은 무엇입니까?  (0) 2023.06.11
C#을 사용하여 메서드를 매개 변수로 전달  (0) 2023.06.11
Angular 2 NgForm에서 터치된 이벤트를 관찰하는 방법은 무엇입니까?  (0) 2023.06.11
판다의 다른 값을 기준으로 한 값 변경  (0) 2023.06.11

'programing'의 다른글

  • 현재글간단한 SQL 코드로 마리아DB의 NOCYLE(오라클) 계층 쿼리를 사용하여 CONNECT BY

관련글

댓글

티스토리툴바