복붙노트

[SQL] 계층 구조 달성 부모 / 효과적이고 쉬운 방법으로 자식 관계

SQL

계층 구조 달성 부모 / 효과적이고 쉬운 방법으로 자식 관계

나는 테이블 등이있다

create table site
(
site_Id int(5),
parent_Id int(5),
site_desc varchar2(100)
);

필드의 의의 :

요구 사항은 내가 입력으로 사이트 ID를 가지고 있고, 내가 필요로하는 경우 모든 ID가 사이트 아래에 태그 것입니다. 예를 들면 :

                    A
                   / \
                  B   C
                / | \ /\
               D  E F G H
              /\
             I  J

모든 노드는 SITE_ID 있습니다.

표는이 같은 데이터를 포함 :

Site_id  | Parent_ID  |  site_desc
_________|____________|___________
 A       |   -1       |   
 B       |    A       |
 C       |    A       |
 D       |    B       |
 E       |    B       |
 F       |    B       |
 I       |    D       |
 J       |    D       |

......

A는 B 및 등등과 C의 부모이다.

B는 D, E, I, F, J를 가져 쿼리 요구 후 주어진 입력되면

그것은 현재 루프에서 여러 쿼리를 통해 달성,하지만 난 쿼리의 최소 번호에서 이것을 달성하기 위해 생각했다.

내가 현재하고있는 중이 야 것은 ::

부결하다

이 알고리즘은 다음과 같이 간다 :

Initially create a data set object which you will populate, by fetching data from the data base. 
Create a method which takes the parent id as parameter and returns its child nodes if present, and returns -1, if it doesnt have a child. 
Step1: Fetch all the rows, which doesn't have a parent(root) node. 
Step2: Iterate through this result. For example if prod1 and prod2 are the initial returned nodes, in the resultset. 
Iterating this RS we get prod1, and we insert a row in our DataSET obj. 
Then we send the id of prod1 to getCHILD method, to get its child, and then again we iterate the returned resultset, and again call the getCHILD method, till we dont get the lowest node.

내 데이터 모델의 제약 내에서 최선의 최적화 기술이 필요합니다. 당신이 어떤 제안이 있다면 답변 주시기 바랍니다. 무엇을 제안하십시오. 미리 감사드립니다.

해결법

  1. ==============================

    1.불행하게도, 당신은 데이터 모델을 변경할 수 없습니다, 당신은 MySQL은, 당신이 재귀 쿼리를해야 할 상황에있어 붙어를 사용하고 재귀 쿼리를 지원하지 않는 DBMS를 사용하는 경우.

    불행하게도, 당신은 데이터 모델을 변경할 수 없습니다, 당신은 MySQL은, 당신이 재귀 쿼리를해야 할 상황에있어 붙어를 사용하고 재귀 쿼리를 지원하지 않는 DBMS를 사용하는 경우.

    Quassnoi는 계층 적 데이터를 쿼리 기술을 보여주는, 블로그 기사의 흥미로운 시리즈를 썼다. 그의 해결책은 아주 똑똑하지만 매우 복잡하다. http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

    PostgreSQL은 당신이 보여 방식에 저장된 전체 트리를 가져올 수 있도록 지원 재귀 쿼리를 수행 또 다른 오픈 소스 RDBMS입니다. 당신은 데이터 모델을 변경할 수없는 경우에, 나는 당신이 다른 RDBMS로 전환 할 수 없습니다 가정 것입니다.

    훨씬 쉽게 임의의 깊이의 나무를 가져올 수있는 여러 가지 다른 데이터 모델이 있습니다 :

    데이터베이스 프로그래밍의 함정을 피하기 : 나는 SQL 및 PHP와 계층 적 데이터에 대한 프레젠테이션 모델과 내 책 SQL 안티 패턴에이 커버.

    그들은 인접성 (adjacency) 목록처럼 "PARENT_ID"를 저장하지만, 그들은 또한 "ROOT_ID"열을 저장 : 마지막으로, 나는 그들의 의견 계층 구조, 슬래시닷 (Slashdot)의 코드에 사용되는 본 적이 또 다른 해결책이있다. 주어진 트리의 모든 회원은 트리에서 가장 높은 조상 노드에 root_id 같은 값을 가지고 있습니다. 그런 다음 한 쿼리에서 전체 트리를 가져 오기 쉽다 :

    SELECT * FROM site WHERE root_id = 123;

    그런 다음 응용 프로그램은 모든 노드가 배열에 데이터베이스에서 백업, 당신은 메모리에 트리 데이터 구조에 노드를 삽입,이 배열을 통해 루프에 코드를 작성해야 가져옵니다. 당신이 많은 별도의 나무가 있다면 이것은 좋은 솔루션입니다, 각 나무는 상대적으로 적은 항목이 있습니다. 그것은 슬래시닷 (Slashdot)의 경우에 좋다.

  2. ==============================

    2.어제, 나는 정확히 설명한 문제와 관련이 질문에 대답했다 : 주어진 인접리스트의 아웃, 당신은 특정 부모의 모든 자식 노드를 얻으려면 - 아마 1 차원 배열에서 해당 쉽게 할 수 반복.

    어제, 나는 정확히 설명한 문제와 관련이 질문에 대답했다 : 주어진 인접리스트의 아웃, 당신은 특정 부모의 모든 자식 노드를 얻으려면 - 아마 1 차원 배열에서 해당 쉽게 할 수 반복.

    당신은 데이터베이스에 하나의 호출을 사용하여이 작업을 수행하지만, 일종의 캐치의있을 수 있습니다 당신은 테이블에서 모든 행을 반환해야합니다. MySQL은 그래서 대신에, 당신은 기본적으로 응용 프로그램 코드에서 선택을해야 할, 재귀 쿼리를 지원하지 않습니다.

    나는 위에 링크 된 것을 간단히 말해서 나는 내 대답을 유지하는거야,하지만 기본적으로는 같은의 형식 (PDOStatement-> fetchAll (PDO :: FETCH_ASSOC) 또는 다른 방법에서 아마) 결과 집합을 반환하는 경우 :

    Array
(
    [0] => Array
    (
        [site_id] => A
        [parent_id] => -1
        [site_desc] => testtext
    )
    [1] => Array
    (
        [site_id] => B
        [parent_id] => A
        [site_desc] => testtext
    )
    [2] => Array
    (
        [site_id] => C
        [parent_id] => A
        [site_desc] => testtext
    )
    [3] => Array
    (
        [site_id] => D
        [parent_id] => B
        [site_desc] => testtext
    )
    [4] => Array
    (
        [site_id] => E
        [parent_id] => B
        [site_desc] => testtext
    )
    [5] => Array
    (
        [site_id] => F
        [parent_id] => B
        [site_desc] => testtext
    )
    [6] => Array
    (
        [site_id] => I
        [parent_id] => D
        [site_desc] => testtext
    )
    [7] => Array
    (
        [site_id] => J
        [parent_id] => D
        [site_desc] => testtext
    )
)

    당신은 모든 어린이를 검색 할 수 있습니다 / 손자 / greatgrandchildren / SO-에 어떤 사이트 ID의 재귀 함수를 사용하여 (당신이 ID를 알고 제공)

    function fetch_recursive($src_arr, $id, $parentfound = false, $cats = array())
{
    foreach($src_arr as $row)
    {
        if((!$parentfound && $row['site_id'] == $id) || $row['parent_id'] == $id)
        {
            $rowdata = array();
            foreach($row as $k => $v)
                $rowdata[$k] = $v;
            $cats[] = $rowdata;
            if($row['parent_id'] == $id)
                $cats = array_merge($cats, fetch_recursive($src_arr, $row['site_id'], true));
        }
    }
    return $cats;
}

    예를 들어, 당신이 그렇게 같은 기능을 사용하면 사이트 ID D의 모든 어린이를 검색하고 싶었 말 :

    $nodelist = fetch_recursive($pdostmt->fetchAll(PDO::FETCH_ASSOC), 'D');
print_r($nodelist);

    겠습니까 출력 :

    [0] => Array
(
    [site_id] => D
    [parent_id] => B
    [site_desc] => testtext
)
[1] => Array
(
    [site_id] => I
    [parent_id] => D
    [site_desc] => testtext
)
[2] => Array
(
    [site_id] => J
    [parent_id] => D
    [site_desc] => testtext
)

    우리가 그 자식, 손자와 함께 부모의 정보를 유지, 그래서 (그러나 깊은 중첩 간다)에서 확인할 수 있습니다.

  3. ==============================

    3.당신은 하나의 쿼리에서이 작업을 수행 할 수 있도록하려면, 중첩 된 세트 모델을 체크 아웃 : http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

    당신은 하나의 쿼리에서이 작업을 수행 할 수 있도록하려면, 중첩 된 세트 모델을 체크 아웃 : http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

    또 다른 대안은 연결 테이블의 모든 관계를 포함하는 것입니다. 모든 사이트 등 부모, 그 조부모, 링크를 할 것이다 그래서 모든 관계는 명시 적입니다. 그럼 그냥 모든 자손을 얻기 위해 그 연결 테이블을 쿼리합니다.

  4. ==============================

    4.폐쇄 테이블 : 우선, 나는 나무를 저장하는 비트 다른 방법을 추천 할 것입니다. 당신이 그것에 대해 더 많은 것을 알고 싶은 경우에, 당신은 SQL 안티 패턴 책은 매우 흥미 있었다.

    폐쇄 테이블 : 우선, 나는 나무를 저장하는 비트 다른 방법을 추천 할 것입니다. 당신이 그것에 대해 더 많은 것을 알고 싶은 경우에, 당신은 SQL 안티 패턴 책은 매우 흥미 있었다.

    즉 말했다. 내 의견으로는, 이러한 구조를 생성하는 가장 쉬운 방법은 이것이다 : http://jsbin.com/omexix/3/edit#javascript

    난 당신이 자바 스크립트 코드를 읽고 아무 ​​문제가 있기를 바랍니다. 자바 스크립트에서 분류되지 않은 객체의 생성이 너무 해킹 틱 보이지 않기 때문에 나는 그것을 사용. 이는 오브젝트 (또는 참조) 다차원 배열을 사용하여 중계없이 동일한 구현할 수 있지만, 다소 혼란 보인다.

    여기 알고리즘이하는 것입니다 :

    이것에 관한 것입니다. 모든 노드와, 오직 루트 노드 : 기본적으로 두 개의 목록을 생성합니다.

  5. ==============================

    5.당신은 폐쇄 테이블 패턴을 살펴 가지고 할 수 있습니다. 이 사이트의 정보를 발견했다. 지금까지 내가 본대로, 여기에, 또한 예를 들어,이 개념에 대한 여러 StackOverflow의 질문입니다 THER.

    당신은 폐쇄 테이블 패턴을 살펴 가지고 할 수 있습니다. 이 사이트의 정보를 발견했다. 지금까지 내가 본대로, 여기에, 또한 예를 들어,이 개념에 대한 여러 StackOverflow의 질문입니다 THER.

  6. ==============================

    6.사이트 테이블을 업데이트하지 않으면 종종 다음과 같은 전략을 사용할 수 있습니다 :

    사이트 테이블을 업데이트하지 않으면 종종 다음과 같은 전략을 사용할 수 있습니다 :

    create table site
(
site_Id int(5),
parent_Id int(5),
site_desc varchar2(100),
parents_path varchar(X)
);

    parents_path는 루트로부터 선택된 노드의 경로와 같습니다. 예를 들어, J는 잎이 있어야 대해 | A | B | D |.

    장점 : - 당신은 결과를 얻기 위해 단일 쿼리가 필요합니다;

    단점 : - 업데이트 중 더 쿼리 (하지만 당신은 현명하게 업데이트 할 수 있습니다)

    희망이 도움이

  7. ==============================

    7.다른 사람은 이미 테이블에 약간의 수정과 함께이 작업을 수행하는 방법을 제안 하였다 구조.

    다른 사람은 이미 테이블에 약간의 수정과 함께이 작업을 수행하는 방법을 제안 하였다 구조.

    (이가 가장 좋은 것입니다 경우에도) 구조를 수정하지 않으려면, 당신은 할 수있다 이 같은 :

    일반적으로 안전하게 할당되면 ID를 변경하지 않는 것이 가정 할 수있다; IDS는 경우 즉, 노드 C는 노드 B에 따라 이동하지 않고, 주위 셔플 하지마, 그것은 것 진정한 자식 노드는 부모에 비해 항상 높은 ID를 가지고 있고, 정렬이 위의 모든 부모가 자녀 전에 인출 얻을 수 있음을 보장합니다.

    이러한 그래서 가설은 다음과 같습니다 :

    - we prefer not to change the table layout
- we never change the IDs once assigned
- we never reorder the tree, moving IDs around

    따라서 메모리에 트리를 생성 할 수있게된다 (심지어 쿼리를 감소 자체)가 사이트 ID> = B를 첨가.

    첫 번째 노드 B의 될 것입니다 통해 제공하고 트리에 투입 될 것입니다.

    이후의 모든 노드는 반드시있다 그들의 PARENT_ID 번째 노드에 저장 될 수있다 전에로드.

    이것은 (직접 부모 노드를 수정) 파이썬에서 아주 잘 갈 것입니다.

    요청은 다음과 같이 PHP에 대답 할 수있다 "B의 모든 자손을 얻으십시오"

    $nodes  = array( $parent_id );

$cursor = SQLQuery("SELECT * FROM site WHERE Site_ID > ? "
        .  "ORDER BY Parent_ID, Site_Id ;", $parent_id);

while ($tuple = SQLFetchTuple($cursor))
    if (in_array($tuple['Parent_ID'], $nodes))
        $nodes[] = $tuple['Site_Id'];
SQLFree($cursor);

// The first node is the global parent, and may be array_shift'ed away
    // if desired.

    또 다른 방법 아주 무력

    또 다른 가능성은 다른 재귀 적 관계은 "의 자손"을 저장하는 것입니다 표:

        TRUNCATE descendants;
    INSERT INTO descendants ( node, of ) VALUES ( -1, NULL );

    INSERT INTO descendants SELECT SiteId, ParentId FROM site JOIN
           descendants ON ( site.ParentId = descendants.of );

    삽입 된 행의 수는 제로 (또는 총 수와 동일 할 때까지 반복 인서트 하위 행 증가 중지; 테이블 크기를 조회하는) 대부분의 DB를 매우 빠릅니다.

    이 시점에서 모두 한 수준의 관계를 저장 한 것입니다. 지금:

    INSERT IGNORE INTO descendants SELECT s1.node, s2.of FROM
           descendants AS s1 JOIN descendants AS s2 ON (s1.of = s2.node);

    자손 정지까지 증가 인서트의 동일 다시 ... (그 수를 필요 수준의 최대 수). 의 총 수는 수준의 두 배 수있을 것입니다 합류했다.

    이제 노드 (16)의 모든 자손을 얻으려면, 당신은 단순히 쿼리

    SELECT node FROM descendants WHERE of = 16;
  8. ==============================

    8.당신은 이것에 대한 저장 프로 시저를 만들 수 있습니다.

    당신은 이것에 대한 저장 프로 시저를 만들 수 있습니다.

    여기에 mysql을 내 구현

    DROP PROCEDURE IF EXISTS SearchTree;
DELIMITER go

CREATE PROCEDURE SearchTree( IN root CHAR(1) )
BEGIN
  DECLARE rows SMALLINT DEFAULT 0;
  DROP TABLE IF EXISTS reached;
  CREATE TABLE reached (
    site_Id CHAR(1) PRIMARY KEY
  ) ENGINE=HEAP;
  INSERT INTO reached VALUES (root);
  SET rows = ROW_COUNT();
  WHILE rows > 0 DO
    INSERT IGNORE INTO reached 
      SELECT DISTINCT s.site_Id 
      FROM site AS s 
      INNER JOIN reached AS r ON s.parent_Id = r.site_Id;
    SET rows = ROW_COUNT();
    DELETE FROM reached 
      WHERE site_Id = root;
  END WHILE;
  SELECT * FROM reached;
  DROP TABLE reached;
END;
go
DELIMITER ;
CALL SearchTree('B');

    이는 예상 된 결과를 반환합니다.

  9. ==============================

    9.여기에 귀하의 의견을 바탕으로 나는 수백 개의 응용 프로그램이 그것을 사용하는 (그리고 다른 뭔가로 대체하는 경우 중단됩니다) 때문에 기존 데이터 모델을 변경 내키지 가정입니다.

    여기에 귀하의 의견을 바탕으로 나는 수백 개의 응용 프로그램이 그것을 사용하는 (그리고 다른 뭔가로 대체하는 경우 중단됩니다) 때문에 기존 데이터 모델을 변경 내키지 가정입니다.

    문제의 뿌리는 우리가 루트 사이트를 발견 재귀 때까지 부모의 부모를 조회 할 수 있도록 모든 사이트에 대해, 우리는, 그것의 직접 부모를 알고 있다는 것입니다.

    이 사이트는 중첩 될 수있는에 깊이 / 레벨 제한 멀리 할 수 ​​있다면, 당신은 당신을 위해 모든 작업을 수행하고 아마 그 부팅하기를 더디하지 않습니다 하나 개의 큰 쿼리를 작성할 수 있습니다. 발사 쿼리의 대부분의 오버 헤드가 매우 빠르게 할 수 있습니다 MySQL의 연결, 네트워크 대역폭 등을 설정에서 온다.

    우리가 원하지 않아도, 여러 쿼리 곱 모든 오버 헤드를 발사. 는 SELECT *을하고 우리가 그것을하지 않도록 다음, 네트워크 오버 헤드를 극대화, 당신은 때마다 모든 데이터를 가져옵니다 애플리케이션 로직 수단에 계산.

    트리의 깊이에 제한이 허용하는 경우, 당신은 모든 작업을 수행하고 당신이 필요로하는 정확한 결과 집합을 반환 하나 개의 거대한 쿼리에 여러 개의 쿼리를 결합 할 수 있습니다. 예를 들어 I는 데이터를 사용하지만, C 등을 A, B로 교체 1, 2, 3 (검색 열로서 INT이다).

    (사이트 ID = 1) 루트 노드의 모든 직계 자식을 얻으려면이 작업을 수행 :

    select site_id from site where parent_id = 1

    루트 노드의 손자를 얻으려면 다음을 수행하십시오

    select grandchild.site_id 
from site grandchild, site child 
where grandchild.parent_id = child.site_id 
and child.parent_id = 1

    루트 노드의 증손자를 얻을하려면 다음을 수행하십시오

    select greatgrandchild.site_id 
from site greatgrandchild, site grandchild, site child 
where greatgrandchild.parent_id = grandchild.site_id 
and grandchild.parent_id = child.site_id 
and child.parent_id = 1

    다만 다음과 같이 하나 개의 큰 쿼리로 위의 쿼리를 결합, 루트 노드의 모든 자손을 얻으려면 :

    select site_id
from site
where site_id in (
    select site_id 
    from site 
    where parent_id = 1
)
or site_id in (
    select grandchild.site_id 
    from site grandchild, site child 
    where grandchild.parent_id = child.site_id 
    and child.parent_id = 1
)
or site_id in (
    select greatgrandchild.site_id 
    from site greatgrandchild, site grandchild, site child 
    where greatgrandchild.parent_id = grandchild.site_id 
    and grandchild.parent_id = child.site_id 
    and child.parent_id = 1
)

    나는이 작동하는 방법을 볼 생각합니다. 각 추가 수준의 경우, 많은 수준 멀리 당신이 후손을 검색하는 사이트이며, '나 사이트 ID ()'추가와 함께 슈퍼 쿼리에 해당 쿼리를 추가 할 노드를 찾는 쿼리를 만들 ...

    당신은 단지 세 가지 수준, 볼 수있는 자,이 이미 큰 쿼리가됩니다. 당신이 지원이 필요한 경우,이 쿼리는 거대한 될 것입니다, 10 레벨을 말하고 모든 OR의 및 IN의는하지만, 여전히 아마 빨리 다음 될 것입니다 단지 모든 것을 얻거나 여러 쿼리를 사용하여 ... 그것을 느려집니다있다. 당신이 가능한 수준의 임의의 금액을 지원해야하는 경우이 쿼리는 당신을 도울 수있는 것보다. 그것은 무한히 커질해야합니다. 이 경우 남아는 더 나은 방법을 사용하는 모든 것을 ...

    즉 말하고,이 붙여 코딩을 시작 복사하기 전에, 같은 큰 쿼리를 방지 임의의 깊이를 지원하고 이전 버전과의 호환성을 깨지 않고 방법이있다. 이 데이터 모델의 변경을 필요로하지 않습니다하지만이 데이터 모델을 사용하여 다른 프로그램을 해치지 않을 것입니다 작은 하나입니다. 짧은에 있습니다 ...

    더 나은 방법

    루트에 각 노드에서 모든 방법을 전체 경로를 인코딩하는 그의 대답에 언급 ravnur 같은 것을 사용하여 여분의 열 parent_paths 추가

    동적으로 삽입, 업데이트 및 삭제에 대한 트리거를 사용하여 해당 열을 채 웁니다. 이제 중복 데이터를 유지하고 있습니다. 그것은 다른 프로그램을 해치지 않을 것입니다하지만 당신에 대한 상당한 성능 이점을 제공 할 수 있습니다. 있는지 확인 트리거는 방탄 있습니다 (즉, 아마 가장 어려운 부분) 여분의 열에서 데이터는 항상 테이블에 일반 데이터 동기화에 있어야으로하지만

    직접 재귀없이 그 사이트 ID와 사이트의 모든 자손을 얻을 수있는 parent_paths 열에서 어떤 장소에서 사이트 ID의 occurance에 그 모습을 보여 주었다 ravnur 것과 같은 짧고 달콤한 쿼리를 사용합니다.

  10. ==============================

    10.나는 또한 재귀 적 방법 쿼리 관계에 자신을 물었고, 나의 뇌는이 솔루션을 (생성 :

    나는 또한 재귀 적 방법 쿼리 관계에 자신을 물었고, 나의 뇌는이 솔루션을 (생성 :

    SELECT * FROM
(
    SELECT t2.* FROM table t1, table t2 where t2.parent = t1.id OR t2.parent 0 GROUP BY t2.id, t2.parent
) as all_relations
WHERE all_relations.parent >= '_the_id_'

# if you dont want a subtree use only the inner select

    나는 확실하지 100 %,하지만 난 한 ID가 자동 증가로 생각하고 아이가 자신의 부모 (즉되어야하는 일반적인 경우), 다음이 해결책이 될 수를 작은 ID를 가지고하지?

    11. from https://stackoverflow.com/questions/11064913/achieve-hierarchy-parent-child-relationship-in-an-effective-and-easy-way by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 의 차이는 왼쪽 조인과 오른쪽 SQL 서버에 참여  (0) 2020.03.19
[SQL] 부모와 자식 테이블에 데이터 삽입 - T-SQL  (0) 2020.03.19
[SQL] 나는 완전 외부 쓰기 어떻게 액세스 쿼리에 가입  (0) 2020.03.19
[SQL] 어떻게 SQL 플러스에서 실행하는 SQL 스크립트에서 앰퍼샌드를 무시합니까?  (0) 2020.03.19
[SQL] 데이터베이스 나 시스템 파일에서 이미지를 저장? [닫은]  (0) 2020.03.19
,

티스토리툴바