복붙노트

[SQL] 관계형 데이터베이스에서 계층 적 데이터를 저장하기위한 옵션은 무엇입니까? [닫은]

SQL

관계형 데이터베이스에서 계층 적 데이터를 저장하기위한 옵션은 무엇입니까? [닫은]

좋은 개요

일반적으로 당신이 또는 빠른 쓰기 시간 (인접리스트) (예를 들어, 중첩 된 세트) 빠르게 읽을 시간 사이에 결정을하고, 말하기. 일반적으로, 당신이 가장 아래 옵션의 조합으로 결국은 당신의 요구에 맞게. 다음은 몇 가지 깊이있는 독서를 제공합니다 :

옵션

나는의 일반 기능을 알고 사람 :

데이터베이스의 특정 노트

MySQL의

신탁

PostgreSQL의

SQL 서버

해결법

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

    1.내가 가장 좋아하는 대답은이 글의 첫 문장이 무엇을 제안 같습니다. 계층 구조를 유지하고 계층 구조를 조회 할 중첩 된 세트를 사용하는 인접성 목록을 사용합니다.

    내가 가장 좋아하는 대답은이 글의 첫 문장이 무엇을 제안 같습니다. 계층 구조를 유지하고 계층 구조를 조회 할 중첩 된 세트를 사용하는 인접성 목록을 사용합니다.

    문제 최대 때까지 지금 대부분의 사람들이로 알려진 극한의 RBAR 방법을 사용하기 때문에 중첩 된 설정에 Adjacecy 목록에서 coversion 방법은 무섭게 느린되었다는해온 변환을 수행하는 '스택을 누르십시오 "하고 비싼 방법이 고려되고있다 인접성 목록에 의해 유지 관리의 단순성과 중첩 된 세트의 최고 성능의 열반에 도달합니다. 더 말보다가, 특히 그 결과, 대부분의 사람들은 형편에게, 하나 또는 다른 정착을 가진 10 개 노드 정도를 끝낸다. 푸시 스택 방법을 사용하여 MLM'ers 작은 만 노드 계층 구조로 간주 될지에 대한 변환을 수행하기 위해 하루 종일 걸릴 수 있습니다.

    난 그냥 불가능 보인다 속도로 중첩 된 세트 인접성 목록을 변환하는 방법을 함께 올라오고 의해 Celko에게 경쟁의 비트를 줄 거라고 생각했다. 여기 내 I5 노트북에 푸시 스택 방식의 성능입니다.

    Duration for     1,000 Nodes = 00:00:00:870 
Duration for    10,000 Nodes = 00:01:01:783 (70 times slower instead of just 10)
Duration for   100,000 Nodes = 00:49:59:730 (3,446 times slower instead of just 100) 
Duration for 1,000,000 Nodes = 'Didn't even try this'

    그리고 여기 (괄호 안의 푸시 스택 방법) 새로운 방법의 기간입니다.

    Duration for     1,000 Nodes = 00:00:00:053 (compared to 00:00:00:870)
Duration for    10,000 Nodes = 00:00:00:323 (compared to 00:01:01:783)
Duration for   100,000 Nodes = 00:00:03:867 (compared to 00:49:59:730)
Duration for 1,000,000 Nodes = 00:00:54:283 (compared to something like 2 days!!!)

    네, 맞습니다. 100 만 개 노드 4 초 미만에 분 미만 10 만 노드에서 변환됩니다.

    당신은 새로운 방법에 대해 읽고 다음 URL에서 코드의 사본을 얻을 수 있습니다. http://www.sqlservercentral.com/articles/Hierarchy/94040/

    나는 또한 유사한 방법을 사용하여 "사전 집계"계층 구조를 개발했다. MLM'ers 및 재료의 청구서를 만드는 사람들은 특히이 문서에 관심이있을 것입니다. http://www.sqlservercentral.com/articles/T-SQL/94570/

    당신은 어느 기사를 살펴은 "토론에 참여하기"링크로 뛰어 있도록하여 정지를 할 경우 나 당신이 어떻게 생각하는지 알고있다.

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

    2.이 질문에 매우 부분적인 대답은,하지만, 난 여전히 유용 바랍니다.

    이 질문에 매우 부분적인 대답은,하지만, 난 여전히 유용 바랍니다.

    마이크로 소프트 SQL 서버 2008 개를 구현 계층 적 데이터를 관리하기위한 매우 유용한 두 가지 기능 :

    에서보세요 시작에 대한 MSDN에 켄트 Tegels에 의해 "2008 귀하의 데이터 계층 구조와 SQL 서버 모델". 또한 내 자신의 질문을 참조하십시오 : SQL Server 2008의 재귀 같은 테이블 쿼리

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

    3.이 디자인은 아직 언급되지 않았습니다 :

    이 디자인은 아직 언급되지 않았습니다 :

    이 한계를 가지고 있지만, 당신이 그들을 품을 수 있다면, 그것은 매우 간단하고 매우 효율적입니다. 풍모:

    여기에 예를 다음 - 새의 분류 학적 트리 계층 구조는 클래스 / 주문 / 가족 / 속 / 종 그래서 - 종은 가장 낮은 수준, 1 개 행 = 1 개 분류군이다 (리프 노드의 경우 종에있는 해당한다)

    CREATE TABLE `taxons` (
  `TaxonId` smallint(6) NOT NULL default '0',
  `ClassId` smallint(6) default NULL,
  `OrderId` smallint(6) default NULL,
  `FamilyId` smallint(6) default NULL,
  `GenusId` smallint(6) default NULL,
  `Name` varchar(150) NOT NULL default ''
);

    데이터의 예 :

    +---------+---------+---------+----------+---------+-------------------------------+
| TaxonId | ClassId | OrderId | FamilyId | GenusId | Name                          |
+---------+---------+---------+----------+---------+-------------------------------+
|     254 |       0 |       0 |        0 |       0 | Aves                          |
|     255 |     254 |       0 |        0 |       0 | Gaviiformes                   |
|     256 |     254 |     255 |        0 |       0 | Gaviidae                      |
|     257 |     254 |     255 |      256 |       0 | Gavia                         |
|     258 |     254 |     255 |      256 |     257 | Gavia stellata                |
|     259 |     254 |     255 |      256 |     257 | Gavia arctica                 |
|     260 |     254 |     255 |      256 |     257 | Gavia immer                   |
|     261 |     254 |     255 |      256 |     257 | Gavia adamsii                 |
|     262 |     254 |       0 |        0 |       0 | Podicipediformes              |
|     263 |     254 |     262 |        0 |       0 | Podicipedidae                 |
|     264 |     254 |     262 |      263 |       0 | Tachybaptus                   |

    이 방법은 매우 쉬운 방법으로 필요한 모든 작업을 수행하기 때문에 내부 범주 트리에서 자신의 수준을 변경하지 않는 한 같은 중대하다.

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

    4.내가 쉽게 트리에 새 항목을 삽입 할 수 있기 때문에 나는 그것을 위해 갔다와도 꽤 빨리를 쿼리 (당신은 단지에 새 항목을 삽입 지점의 ID 필요).

    내가 쉽게 트리에 새 항목을 삽입 할 수 있기 때문에 나는 그것을 위해 갔다와도 꽤 빨리를 쿼리 (당신은 단지에 새 항목을 삽입 지점의 ID 필요).

    +-------------+----------------------+--------+-----+-----+
| category_id | name                 | parent | lft | rgt |
+-------------+----------------------+--------+-----+-----+
|           1 | ELECTRONICS          |   NULL |   1 |  20 |
|           2 | TELEVISIONS          |      1 |   2 |   9 |
|           3 | TUBE                 |      2 |   3 |   4 |
|           4 | LCD                  |      2 |   5 |   6 |
|           5 | PLASMA               |      2 |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |      1 |  10 |  19 |
|           7 | MP3 PLAYERS          |      6 |  11 |  14 |
|           8 | FLASH                |      7 |  12 |  13 |
|           9 | CD PLAYERS           |      6 |  15 |  16 |
|          10 | 2 WAY RADIOS         |      6 |  17 |  18 |
+-------------+----------------------+--------+-----+-----+

    나는이를 선택 그 이유는, 액세스하고 빠른 삽입보다 나무를 조회 할 필요

    유일한 문제는 새 항목을 삽입 할 때 왼쪽과 오른쪽 열을 수정하는 것입니다. 물론 나는 그것을 위해 저장 프로 시저를 생성하고 그것을 내 경우는 드문 새로운 항목을 삽입 할 때마다이라고하지만 정말 빠릅니다. 나는 조 셀코의 책에서 아이디어 및 저장 프로 시저를 가지고와 내가 함께 온 방법 DBA SE 여기에 설명 https://dba.stackexchange.com/q/89051/41481

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

    5.데이터베이스가 배열을 지원하는 경우, 당신은 또한 계보 열을 구현하거나 부모 ID의 배열로 경로를 구체화 할 수 있습니다.

    데이터베이스가 배열을 지원하는 경우, 당신은 또한 계보 열을 구현하거나 부모 ID의 배열로 경로를 구체화 할 수 있습니다.

    특히 포스트 그레스 당신은 계층 구조를 조회하려면 set 연산자를 사용하고, GIN 인덱스와 우수한 성능을 얻을 수 있습니다. 이는 단일 쿼리에 아주 사소한 발견의 부모, 자녀, 그리고 깊이를합니다. 업데이트뿐만 아니라 꽤 관리 할 수 ​​있습니다.

    난 당신이 궁금하면 구체화 된 경로에 대한 배열을 사용하는 전체 기록을 가지고있다.

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

    6.이건 정말 스퀘어 페그, 둥근 구멍의 질문이다.

    이건 정말 스퀘어 페그, 둥근 구멍의 질문이다.

    관계형 데이터베이스와 SQL 당신이 가지고있는 유일한 망치 또는 사용하고자하는 경우, 지금까지 이렇게 게시 된 답변 적합. 그러나 이유는 계층 적 데이터를 처리 할 수 ​​있도록 설계 도구를 사용하지? 그래프 데이터베이스는 복잡한 계층 구조 데이터에 이상적입니다.

    그래프 데이터베이스 솔루션이 같은 문제를 해결할 수있는 용이성에 비해 관계형 모델에 그래프 / 계층 적 모델을 매핑하는 코드 / 쿼리 솔루션의 복잡성과 함께 관계형 모델의 비 효율성은 노력이 가치가 없습니다.

    공통의 계층 적 데이터 구조로 재료의 빌을 고려하십시오.

    class Component extends Vertex {
    long assetId;
    long partNumber;
    long material;
    long amount;
};

class PartOf extends Edge {
};

class AdjacentTo extends Edge {
};

    간단한 그래프 순회 알고리즘의 두 서브 - 어셈블리 사이의 최단 경로. 허용되는 경로는 기준에 따라 자격이 될 수 있습니다.

    유사성 두 어셈블리 사이의 유사성의 정도가 무엇입니까? 두 하위 트리의 교회법 및 노동 조합을 계산 모두 하위 트리에 대한 탐색을 수행합니다. 유사한 %는 노조로 나눈 교차로입니다.

    트랜 지 티브 클로저 : 하위 트리를 걸어 분야 (들)의 관심, 예를 요약 "얼마나 알루미늄은 서브 어셈블리에?"

    예, SQL과 관계형 데이터베이스의 문제를 해결할 수 있습니다. 그러나, 당신은 작업에 적합한 도구를 사용하고자하는 경우에 훨씬 더 접근이있다.

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

    7.내 계층 구조에 대한 폐쇄 테이블과 PostgreSQL을 사용하고 있습니다. 나는 전체 데이터베이스에 대해 하나의 보편적 인 저장 프로 시저를 가지고 :

    내 계층 구조에 대한 폐쇄 테이블과 PostgreSQL을 사용하고 있습니다. 나는 전체 데이터베이스에 대해 하나의 보편적 인 저장 프로 시저를 가지고 :

    CREATE FUNCTION nomen_tree() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$
DECLARE
  old_parent INTEGER;
  new_parent INTEGER;
  id_nom INTEGER;
  txt_name TEXT;
BEGIN
-- TG_ARGV[0] = name of table with entities with PARENT-CHILD relationships (TBL_ORIG)
-- TG_ARGV[1] = name of helper table with ANCESTOR, CHILD, DEPTH information (TBL_TREE)
-- TG_ARGV[2] = name of the field in TBL_ORIG which is used for the PARENT-CHILD relationship (FLD_PARENT)
    IF TG_OP = 'INSERT' THEN
    EXECUTE 'INSERT INTO ' || TG_ARGV[1] || ' (child_id,ancestor_id,depth) 
        SELECT $1.id,$1.id,0 UNION ALL
      SELECT $1.id,ancestor_id,depth+1 FROM ' || TG_ARGV[1] || ' WHERE child_id=$1.' || TG_ARGV[2] USING NEW;
    ELSE                                                           
    -- EXECUTE does not support conditional statements inside
    EXECUTE 'SELECT $1.' || TG_ARGV[2] || ',$2.' || TG_ARGV[2] INTO old_parent,new_parent USING OLD,NEW;
    IF COALESCE(old_parent,0) <> COALESCE(new_parent,0) THEN
      EXECUTE '
      -- prevent cycles in the tree
      UPDATE ' || TG_ARGV[0] || ' SET ' || TG_ARGV[2] || ' = $1.' || TG_ARGV[2]
        || ' WHERE id=$2.' || TG_ARGV[2] || ' AND EXISTS(SELECT 1 FROM '
        || TG_ARGV[1] || ' WHERE child_id=$2.' || TG_ARGV[2] || ' AND ancestor_id=$2.id);
      -- first remove edges between all old parents of node and its descendants
      DELETE FROM ' || TG_ARGV[1] || ' WHERE child_id IN
        (SELECT child_id FROM ' || TG_ARGV[1] || ' WHERE ancestor_id = $1.id)
        AND ancestor_id IN
        (SELECT ancestor_id FROM ' || TG_ARGV[1] || ' WHERE child_id = $1.id AND ancestor_id <> $1.id);
      -- then add edges for all new parents ...
      INSERT INTO ' || TG_ARGV[1] || ' (child_id,ancestor_id,depth) 
        SELECT child_id,ancestor_id,d_c+d_a FROM
        (SELECT child_id,depth AS d_c FROM ' || TG_ARGV[1] || ' WHERE ancestor_id=$2.id) AS child
        CROSS JOIN
        (SELECT ancestor_id,depth+1 AS d_a FROM ' || TG_ARGV[1] || ' WHERE child_id=$2.' 
        || TG_ARGV[2] || ') AS parent;' USING OLD, NEW;
    END IF;
  END IF;
  RETURN NULL;
END;
$_$;

    그럼 난 계층 구조를 가지고 각 테이블에 대해, 나는 트리거를 작성

    CREATE TRIGGER nomenclature_tree_tr AFTER INSERT OR UPDATE ON nomenclature FOR EACH ROW EXECUTE PROCEDURE nomen_tree('my_db.nomenclature', 'my_db.nom_helper', 'parent_id');

    계층 구조를 기존의에서 폐쇄 테이블을 채우기 위해 나는이 저장 프로 시저를 사용합니다 :

    CREATE FUNCTION rebuild_tree(tbl_base text, tbl_closure text, fld_parent text) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
    EXECUTE 'TRUNCATE ' || tbl_closure || ';
    INSERT INTO ' || tbl_closure || ' (child_id,ancestor_id,depth) 
        WITH RECURSIVE tree AS
      (
        SELECT id AS child_id,id AS ancestor_id,0 AS depth FROM ' || tbl_base || '
        UNION ALL 
        SELECT t.id,ancestor_id,depth+1 FROM ' || tbl_base || ' AS t
        JOIN tree ON child_id = ' || fld_parent || '
      )
      SELECT * FROM tree;';
END;
$$;

    ANCESTOR_ID, DESCENDANT_ID, DEPTH - 폐쇄 테이블은 3 열이 정의됩니다. 이 같은 조상 후손을위한 가치와 깊이에 대한 0의 값을 갖는 레코드를 저장에 (그리고 나는 심지어 통보) 할 수 있습니다. 이 계층 구조의 검색을 위해 쿼리를 단순화합니다. 그리고 그들은 실제로 매우 간단합니다 :

    -- get all descendants
SELECT tbl_orig.*,depth FROM tbl_closure LEFT JOIN tbl_orig ON descendant_id = tbl_orig.id WHERE ancestor_id = XXX AND depth <> 0;
-- get only direct descendants
SELECT tbl_orig.* FROM tbl_closure LEFT JOIN tbl_orig ON descendant_id = tbl_orig.id WHERE ancestor_id = XXX AND depth = 1;
-- get all ancestors
SELECT tbl_orig.* FROM tbl_closure LEFT JOIN tbl_orig ON ancestor_id = tbl_orig.id WHERE descendant_id = XXX AND depth <> 0;
-- find the deepest level of children
SELECT MAX(depth) FROM tbl_closure WHERE ancestor_id = XXX;

    8. from https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] MySQL의에서 ROW_NUMBER ()  (0) 2020.03.05
[SQL] 왜 우리는 항상 SQL 문에서 매개 변수를 사용하여 선호합니까?  (0) 2020.03.05
[SQL] SQL Server의 '피봇'를 사용하여 열을 변환 행  (0) 2020.03.05
[SQL] GROUP BY에서 LIMIT를 사용하면 그룹 별 N 결과를 얻으려면?  (0) 2020.03.05
[SQL] 어떻게 FULL OUTER MySQL의에 가입 할까?  (0) 2020.03.05
,

티스토리툴바