SQL (계층쿼리, INNER JOIN) - New Companies

문제

https://www.hackerrank.com/challenges/the-company/problem?isFullScreen=true

 

목표/풀이

• 회사, 설립자별 리더, 시니어, 매니저, 직원 명수 구하기

 

처음 작성한 코드

SELECT c.company_code
    ,  c.founder
    ,  COUNT(DISTINCT l.lead_manager_code)
    ,  COUNT(DISTINCT s.senior_manager_code)
    ,  COUNT(DISTINCT m.manager_code)
    ,  COUNT(DISTINCT e.employee_code)
FROM company c
INNER JOIN lead_manager l ON c.company_code = l.company_code 
INNER JOIN senior_manager s ON c.company_code = s.company_code
INNER JOIN manager m ON c.company_code = m.company_code
INNER JOIN employee e ON c.company_code = e.company_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code;

• OUTPUT은 정답처리 되었지만 소요시간이 꽤나 길었음.
• 이는 company_code로 묶어 불필요한 조합이 무수히 많이 생성되었기 때문
• 즉 ‘A회사에 속하기만 하면 다 연결해’라는 형식이라 카디널리티 곱폭발 문제가 발생

상세

• 회사 A에 이런 데이터가 있다고 해보자. 
  

  company_code	lead_manager_code
  A	L1
  A	L2

  
  

  company_code	senior_manager_code	lead_manager_code
  A	S1	L1
  A	S2	L2

  현실은:
  • L1 아래엔 S1만
  • L2 아래엔 S2만
  • 즉, (L1,S2), (L2,S1) 조합은 존재하면 안 됨.

  ---

  company_code로만 붙이면 무슨 일이 벌어지냐?
  이건 “A회사에 속하기만 하면 다 연결해” 라는 뜻이라서 결과가 이렇게 된다: 

  company_code	lead	senior
  A	L1	S1
  A	L1	S2
  A	L2	S1
  A	L2	S2

  즉, 서로 상관없는 행들을 회사 단위로 섞어서 “실제론 없는 관계”를 만들어버린다.
  만약 회사 A에:
  • lead 100명
  • senior 200명
  • manager 500명
  • employee 10,000명
  이면 회사 A에서만 대략 행이
  • 100 × 200 × 500 × 10,000 = 100,000,000,000 (천억 행 수준) 이나 생겨 처리 과정이 느려짐
  • 즉 카디널리티 곱폭발의 시작

•  

수정한 코드

• 따라서 계층 관계가 명확하다면, 회사별로 조직 계층 (Lead → Senior → Manager → Employee)을 따라 내려가면서 각 레벨에 몇 명이 있는 집계

company
 └─ lead_manager
     └─ senior_manager
         └─ manager
             └─ employee

- 기준 집합: company
- 구조: 계층형 (트리 구조)
- 집계: 회사 단위 GROUP BY

SELECT c.company_code,
         c.founder,
         COUNT(DISTINCT l.lead_manager_code),
         COUNT(DISTINCT s.senior_manager_code),
         COUNT(DISTINCT m.manager_code),
         COUNT(DISTINCT e.employee_code)
FROM company c
LEFT JOIN lead_manager l 
	ON c.company_code = l.company_code
LEFT JOIN senior_manager s 
	ON l.lead_manager_code = s.lead_manager_code
LEFT JOIN manager m 
	ON s.senior_manager_code = m.senior_manager_code
LEFT JOIN employee e 
	ON m.manager_code = e.manager_code
GROUP BY c.company_code, c.founder
ORDER BY c.company_code

 

주의사항

1. 왜 LEFT JOIN인지?
   • 상위 계층은 무조건 유지하고 하위 계층이 없으면 NULL로 두기 위해
   • 예를 들어 매니저는 있지만 직원이 0명 인경우도 결과에 포함하기 위함
2. GROUP BY c.company_code, c.founder
   • 회사 단위로 결과 1행으로 만들기 위함
3. COUNT(DISTINCT l.lead_manager_code)
   • 해당 회사에 실제로 연결된 리드 매니저 수 집계
   • LEFT JOIN 특성상 매칭되지 않은 경우 NULL이 되며 COUNT는 자동으로 NULL을 제외 함.
4. 왜 DISTINCT인가?
   • 계층 JOIN에서는 1:N 관계의 N쪽 조인으로 행이 증가함.
   • 예를들어 L1 아래 senior가 2명(S1, S2) 이라면 조인 시 결과에서 L1 행이 2번 반복됨.
   • 이 상태에서 COUNT(l.lead_manager_code)를 쓰면 같은 리드가 중복 카운트 되므로 중복 제거를 위해 DISTINCT 필요