[63일차] 계층형 쿼리 / 계층형 쿼리 응용 / WITH 절 / 분석함수 와 window 함수

20221024(월)

• 목차 
  - 계층형 쿼리
  - 계층형 쿼리 응용
  - WITH 절
  - 분석함수 와 window 함수

 

---

< 계층형 쿼리 >

• BOM(Bill Of Material) 이란?
  ➡ 하나의 완제품이 만들어지는데 필요한 구성품목들의 내역 혹은 조합도를 말함.
       ex) 컴퓨터 구성도(BOM)

    SELECT expr1, expr2, ...
      FROM 테이블
     WHERE 조건
     START WITH[최상위 조건]
    CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건];

• 데이터가 부모/자식/부모/자식 순으로 출력된다.

---

• LEVEL 의사컬럼
  - DB 단에서 미리 탭여백을 줘서 넘길 것인지
  - 들여쓰기는 JS, Spring, DB에서 다 할 수 있다. 어디에서 할 건지 정할 수 있음.
  
  
• 레벨에 따라 들여쓰기가 달라진다. 

 

---

• 루트노드 : 트리 구조에서 부모가 없는 최상위 노드
  

       (1) 루트노드 데이터행이 출력시 포함되지 않게 만들기

       (2) 루트노드 데이터행이 출력시 포함되게 만들기

 

---

< 계층 쿼리 담당 정렬 명령어 >

(1) ORDER SIBLINGS BY

     일반정렬로 해도 오류는 나지 않음 (이렇게 사용되는 경우도 있단 의미!)

 

---

(2) CONNECT_BY_ROOT

     계층형 쿼리에서 최상위 로우를 반환하는 연산자

     연산자이므로 CONNECT_BY_ROOT 다음에는 표현식이 온다.

 

---

(3) CONNECT_BY_ISLEAF : 마지막노드이면(자식노드가 없는 경우)1, 아니면(자식있는 경우) 0
     해당 로우가 최하위 자식 로우이면 1을, 그렇지 않으면 0을 반환하는 의사 컬럼이다.

 

---

(4) SYS_CONNECT_BY_PATH (colm, char)

     루트 노드에서 시작해 자신의 행까지 연결된 경로 정보를 반환

     (*char은 컬럼 간 구분자를 의미)

 

---

(5) CONNECT_BY_ISCYCLE
     오라클의 계층형 쿼리는 루프(반복) 알고리즘을 사용
     루프 알고리즘에서 주의할 점은 조건을 잘못 주면 무한루프를 타게 된다는 점인데, 

     계층형 쿼리에서도 부모-자식 간의 관계를 정의하는 값이 잘못 입력되면 무한루프를 타고 오류가 발생한다.

     ex) 생산팀(170)의 부모 부서는 구매/생산부(30)인데, 구매/생산부(30)의 parent_id 값을 생산팀으로 바꾸면

           두 부서가 상호 참조가 되어 무한루프가 발생할 것이다. 

 

---

< 계층형 쿼리 응용 >

• (1) 샘플 데이터 생성
  계층형 쿼리와 오라클에서 제공하는 DBMS_RANDOM이란 패키지 사용 ➡ 몇 만 건의 데이터 생성가능
  
  
• (2) 로우를 컬럼으로 변환하기
  LISTAGG(expr, delimiter) WITHIN GROUP(ORDER BY 절)
  expr을 delimiter로 구분 ➡ 로우를 컬럼으로 변환해 조회하는 함수.
• (3) 컬럼을 로우로 변환하기
  계층형 쿼리를 사용하면 쉽게 변환할 수 있다. 

---

< WITH 절 > - 개선된 서브 쿼리

• 별칭으로 사용한 SELECT 문의 FROM 절에 다른 SELECT 구문의 별칭 참조가 가능
• 쿼리가 훨씬 간단해지면서 동일한 결과가 조회된 것을 확인할 수 있다. 
  
  
• 일반 서브 쿼리와 중복 구문이 필요 없는 WITH 절을 그림으로 나타내면 다음과 같다.

출처 : https://thebook.io/006696/part01/ch07/02/

 

 

---

< 분석함수 와 window 함수>

분석 함수 구문
분석 함수 (집계 함수에 속함)	OVER 키워드 뒤에 위치한 분석 함수 절
AVG, SUM MAX, MIN, COUNT, CUM_DIST, DENSE_RANK, PERCENT_RANK, FRIST, LAST, LAG, LEAD, ROW_NUMBER	PARTITION BY : 계산될 대상 그룹 지정 ORDERED BY : 파티션 안에서의 순서 지정 WINDOW 절 : 더 상세한 그룹 분할 시 사용