계층 쿼리 누적 합 구하기

계층 쿼리 누적합 구하기 

 

 * 난이도 ★★★★★★★★★★

 

주어진 상황 : 특성 부서의 조직원 수 

 

하고싶은 것 : 해당 부서와 하위 부서의 누적 조직원수

 

사용할 테이블 no_emp

 

1. LEAF 노드 구하기, LEVEL 구하기 

 

    * LEAF 노드를 알기 위해서는 하향식으로 전개를 해봐야 함

 

    * LEVEL은 상향 탐색시 그룹을 묶기 위해 필요한 값

 

 

  -> parent_org_cd가 NULL인 'XX회사'를 부모노드로 설정하고

 

      부모노드의 org_cd와 parent_org_cd가 같은 조건으로 계층을 구성함  ==> 하향식 전개

 

  -> LEAF 컬럼이 1인 ROW가 LEAF 노드  

 

 

2. 구한 LEAF 노드부터 상향 탐색하고 ROWNUM을 구해줌

 

    * 몇번째로 해당 ROW를 탐색 했는지 구해 주기 위해서

 

  ****** 왜 아까 LEVEL을 구하고 지금 ROWNUM을 구하냐? ---> 위에서 언급했듯이 그룹번호를 구해주기 위해서

 

  

 --->> XX회사를 ROOT노드로 하는 3개의 그룹이 있다

 

   세개의 그룹은 각각의 그룹번호로 묶을 수 있는데

 

  디자인 그룹의 경우 해당 노드의 LEVEL + 방문한 순서 = 4

 

  기획팀의 경우 해당 노드의 LEVEL + 방문한 순서 = 8

 

  개발팀1 의 경우 해당 노드의 LEVEL + 방문한 순서  = 14 

 

  개발팀2 의 경우 해당 노드의 LEVEL + 방문한 순서  = 14

 

   의 각각의 그룹이 공통된 값을 갖는 것을 알 수 있다!!!

 

    ==> 상향식 전개 했을 떄의 방문 순서임

 

 -  상향식 계층구조가 눈에 잘 안보여서 이번 쿼리에서는 구조를 볼 수 있게 LPAD를 사용했다.

 

 -  LV + RN = GRN   !!! 위에 말했듯이  4개의 그룹번호가 구해진다

 

    위에 말로는 디자인부가 4라고 했는데 그건 내가 디자인부의 LEAF노드부터 나올줄 알고 한건데

 

    어느 그룹이 몇 번인지가 중요한게 아니라 그룹별로 번호가 나눠져 있다는게 중요

 

 

 

 3. WINDOW FUNCTION을 사용해서 GRN별로 인원을 더해준다.

   

  -> GRN을 PARITITION으로,  RN을 기준으로 정렬하되, 현재행 이전의 모든 행부터 자신의 행까지 SUM(no_emp) 해라

 

 

    **** 근데!!

 

    부모노드가 중복될 때 마다 중복되는 부모노드의 팀 인원수가 계속 더해짐 

 

 

그래서

 

6. 역전개시 해당 조직이 총 몇번 나오는지 분석함수를 통해 카운팅, 조직 인원수를 카운팅 횟수로  나눈다.

 

   -> 즉, 정보시스템부가 2번 중복되니까 정보시스템부 인원 /2 , XX회사는 4번이니까 /4를 해주겠다는 얘기

 --> org_cd를 PARTITION BY 해서 총 카운트를 구하면 org_cd가 몇번 중복되는지 구해지는데 이 값으로

 

    no_emp를 나누겠다~   ==> 중복되지 않아 한번만 나오는 org_cd는 1로 나누어 지기 때문에 값의 변동 없음

 

 

7. 지금까지 구한 쿼리를 인라인 쿼리로 묶어 no_emp의 합을 구한다.

 

중복된 부서가 계속 나오긴 하지만 중복된 횟수로 나눠져서 구해짐

 

 

8. 이제 위의 쿼리의 합을 구한다.

-- org_cd와 parrent_org_cd를 그룹함수로 묶어 sum 그룹함수를 사용하면 같은 조직끼리의 합이 구해진다.

 

 

9. 하향식으로 계층구조 다시 만들기~

 

--> 아까 인라인 쿼리안의 계층 구조를 알기위해 LAPD했던 것은 지우고

 

     현재 구해지는 쿼리의 계층 구조를 알기위해 LPAD를 사용해주었다

 

--> 계층구조를 하향식으로 다시 구했다