programing

SQL - 계층 저장 및 탐색 방법

bestprogram 2023. 4. 2. 12:00

SQL - 계층 저장 및 탐색 방법

데이터베이스에서 계층 정보를 모델링하고 검색하기 위해 사용하는 방법은 무엇입니까?

나는 수정된 사전 주문 트리 통과 알고리즘을 좋아한다.이 기술을 사용하면 트리에 대한 쿼리를 매우 쉽게 수행할 수 있습니다.

하지만 여기 Zend Framework(PHP) 기고자 웹페이지에서 복사한 주제에 대한 링크 목록이 있습니다(Posted by Laurent Melmoux가 2007년 6월 5일 15:52에 게시).

링크의 대부분은 언어에 의존하지 않습니다.

데이터베이스로 계층 구조를 나타내는 2가지 주요 표현과 알고리즘이 있습니다.

  • 수정된 사전 순서 트리 트래버설 알고리즘이라고도 하는 중첩된 집합
  • 인접 리스트 모델

여기에 잘 설명되어 있습니다.

다음은 제가 수집한 몇 가지 링크입니다.

인접 리스트 모델

중첩 집합

그래프

클래스:

중첩된 집합 DB 트리 Adodb

방문 모델 ADOdb

PEAR: DB_Nested세트

PEAR: 트리

트리가 없다

이 주제에 관한 최종적인 작품은 Joe Celko에 의해 쓰여졌으며, 그는 그 중 많은 부분을 "SQL의 스마트한 SQL의 나무와 계층"이라는 책으로 집필했습니다.

그는 유향 그래프라고 불리는 기술을 선호한다.이 주제에 관한 그의 작품 소개는 여기에서 찾을 수 있다.

SQL 데이터베이스에서 계층 구조를 표현하는 가장 좋은 방법은 무엇입니까?일반적인 휴대용 기술?

계층구조는 대부분 읽혀지지만 완전히 정적이지는 않다고 가정해 봅시다.가계도라고 칩시다.

실행하지 않는 방법은 다음과 같습니다.

create table person (
person_id integer autoincrement primary key,
name      varchar(255) not null,
dob       date,
mother    integer,
father    integer
);

그리고 다음과 같은 데이터를 삽입합니다.

person_id   name      dob       mother father  
1           Pops      1900/1/1   null   null  
2           Grandma   1903/2/4   null   null  
3           Dad       1925/4/2   2      1  
4           Uncle Kev 1927/3/3   2      1
5           Cuz Dave  1953/7/8   null   4
6           Billy     1954/8/1   null   3

대신 노드와 관계를 2개의 테이블로 분할합니다.

create table person (
person_id integer autoincrement primary key,
name      varchar(255) not null,
dob       date
);

create table ancestor (
ancestor_id   integer,
descendant_id integer,
distance      integer
);

데이터는 다음과 같이 생성됩니다.

person_id   name      dob       
1           Pops      1900/1/1  
2           Grandma   1903/2/4   
3           Dad       1925/4/2   
4           Uncle Kev 1927/3/3
5           Cuz Dave  1953/7/8   
6           Billy     1954/8/1   

ancestor_id  descendant_id  distance
1            1              0
2            2              0
3            3              0
4            4              0
5            5              0
6            6              0
1            3              1
2            3              1
1            4              1
2            4              1
1            5              2
2            5              2
4            5              1
1            6              2
2            6              2
3            6              1

이제 테이블에 다시 참여할 필요가 없는 임의 쿼리를 실행할 수 있습니다.이러한 쿼리는 노드와 같은 행에 상속 관계가 있는 경우에 발생합니다.

누가 조부모님을 모시고 있나요?

select * from person where person_id in 
    (select descendant_id from ancestor where distance=2);

모든 하위 항목:

select * from person where person_id in 
    (select descendant_id from ancestor 
    where ancestor_id=1 and distance>0);

삼촌이 누구예요? 

select decendant_id uncle from ancestor 
    where distance=1 and ancestor_id in 
    (select ancestor_id from ancestor 
        where distance=2 and not exists
        (select ancestor_id from ancestor 
        where distance=1 and ancestor_id=uncle)
    )

서브쿼리를 통해 테이블 자체를 결합하는 문제를 모두 회피할 수 있습니다.일반적으로 16개의 서브쿼리가 제한됩니다.

문제는 조상 테이블을 유지하는 것이 다소 어렵다는 것입니다. 저장 프로시저를 사용하는 것이 가장 좋습니다.

나는 조쉬의 의견에 동의하지 않을 수 없다.기업 조직과 같은 거대한 계층 구조를 사용하는 경우 어떻게 됩니까?입사/퇴사, 보고라인 변경 등..."거리"를 유지하는 것은 큰 문제이며 두 개의 데이터 테이블을 유지해야 합니다.

이 쿼리(SQL Server 2005 이상)에서는 임의의 사용자의 완전한 행을 표시하고 계층에서 해당 사용자의 위치를 계산하며 사용자 정보의 단일 테이블만 필요합니다.수정하여 하위 관계를 찾을 수 있습니다.

--Create table of dummy data
create table #person (
personID integer IDENTITY(1,1) NOT NULL,
name      varchar(255) not null,
dob       date,
father    integer
);

INSERT INTO #person(name,dob,father)Values('Pops','1900/1/1',NULL);  
INSERT INTO #person(name,dob,father)Values('Grandma','1903/2/4',null);
INSERT INTO #person(name,dob,father)Values('Dad','1925/4/2',1);
INSERT INTO #person(name,dob,father)Values('Uncle Kev','1927/3/3',1);
INSERT INTO #person(name,dob,father)Values('Cuz Dave','1953/7/8',4);
INSERT INTO #person(name,dob,father)Values('Billy','1954/8/1',3);

DECLARE @OldestPerson INT; 
SET @OldestPerson = 1; -- Set this value to the ID of the oldest person in the family

WITH PersonHierarchy (personID,Name,dob,father, HierarchyLevel) AS
(
   SELECT
      personID
      ,Name
      ,dob
      ,father,
      1 as HierarchyLevel
   FROM #person
   WHERE personID = @OldestPerson

   UNION ALL

   SELECT
    e.personID,
      e.Name,
      e.dob,
      e.father,
      eh.HierarchyLevel + 1 AS HierarchyLevel
   FROM #person e
      INNER JOIN PersonHierarchy eh ON
         e.father = eh.personID
)

SELECT *
FROM PersonHierarchy
ORDER BY HierarchyLevel, father;

DROP TABLE #person;

참고로 SQL Server 2008은 새로운 계층 구조 도입이러한 상황에 대응하는 ID 데이터 타입."트리"에서 행의 위치를 수평 및 수직으로 제어할 수 있습니다.

Oracle: 선택... 시작... 연결 기준

Oracle은 SELECT로 확장되어 트리 기반 검색이 용이합니다.SQL Server에도 비슷한 확장 기능이 있습니까?

이 쿼리는 부모 및 자식 열에 중첩 관계가 저장된 테이블을 통과합니다.

select * from my_table
    start with parent = :TOP
    connect by prior child = parent;

http://www.adp-gmbh.ch/ora/sql/connect_by.html

저는 조쉬와 마크 해리슨이 사용한 테크닉을 혼합한 것을 선호합니다.

이 테이블의 PK가 person_id [, mother_id]인 경우, 2개의 테이블(하나는 Person_id, parent_id [, mother_id])에 의해 노드별로 1개의 부모만 있는 단순한 트리가 생성됩니다(이 경우는 타당하지만, 다른 경우에는 계정과 같이 되지 않습니다).

이 hiarchy 테이블은 재귀적 프로시저 또는 SELECT와 같은 문장으로 DB가 지원하는 경우 변환될 수 있습니다.BY PRIVER (오라클)

계층 데이터의 최대 깊이를 알고 있는 경우 계층 수준별로 열 집합이 있는 단일 테이블을 사용하는 것이 좋습니다.

[fleXive]의 트리 컴포넌트를 구현하고 MySQL 문서에서 tharkun이 언급한 네스트된 세트 트리 모델 접근방식을 사용했을 때도 동일한 문제가 있었습니다.

속도(극적) 향상과 더불어 분산 접근 방식을 사용했습니다. 즉, 상위 수준의 오른쪽 경계에 최대 Long 값을 사용했기 때문에 모든 왼쪽 및 오른쪽 값을 재계산하지 않고도 노드를 삽입 및 이동할 수 있습니다.왼쪽과 오른쪽의 값은 노드의 범위를 3으로 나누어 계산하고 내부 3을 새 노드의 경계로 사용합니다.

Java 코드의 예를 다음에 나타냅니다.

SQL Server 2005를 사용하는 경우 계층 데이터를 가져오는 방법에 대해 설명합니다.

Common Table Expressions(CTE; 공통 테이블 표현)를 사용하기 시작하면 친구가 될 수 있습니다.

언급URL : https://stackoverflow.com/questions/38801/sql-how-to-store-and-navigate-hierarchies

'programing' 카테고리의 다른 글

두 필드 비교 시 MongoDb 쿼리 조건  (0) 2023.04.02
html에서 제출을 클릭하고 php 코드를 실행합니다.  (0) 2023.04.02
TypeScript에서 함수의 반환 유형을 선언하는 방법  (0) 2023.04.02
React 테스트 라이브러리에서 React 18의 ReactDOM.render에 대한 콘솔 오류가 표시됩니다.  (0) 2023.04.02
재료 UI 및 그리드 시스템  (0) 2023.04.02

'programing'의 다른글

  • 현재글SQL - 계층 저장 및 탐색 방법

관련글

댓글

티스토리툴바