[LeetCode] 3482. Analyze Organization Hierarchy

🔗 문제 링크

https://leetcode.com/problems/analyze-organization-hierarchy/description/

 

🔓 문제 풀이

WITH RECURSIVE orgChart AS (
    -- 재귀 쿼리 초기문(manager_id가 없는 값=ceo)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        salary, 
        department, 
        1 AS level,
        CAST(employee_id AS CHAR) AS org_order  
    FROM Employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 재귀 쿼리 반복문
    SELECT 
        e.employee_id, 
        e.employee_name, 
        e.manager_id, 
        e.salary, 
        e.department, 
        o.level + 1 AS level,
        CONCAT(o.org_order, ',', CAST(e.employee_id AS CHAR)) AS org_order 
    FROM Employees e
    JOIN orgChart o ON e.manager_id = o.employee_id
)

SELECT 
    orgChart.employee_id, 
    orgChart.employee_name, 
    orgChart.level, 

    -- team_size구하기
    (SELECT 
        COUNT(*) - 1 
     FROM orgChart oc
     WHERE FIND_IN_SET(CAST(orgChart.employee_id AS CHAR), oc.org_order) > 0
    ) AS team_size,

    -- budget 구하기
    (SELECT 
        SUM(oc.salary) 
     FROM orgChart oc
     WHERE FIND_IN_SET(CAST(orgChart.employee_id AS CHAR), oc.org_order) > 0
    ) AS budget

FROM orgChart
ORDER BY 
    orgChart.level ASC, 
    budget DESC, 
    orgChart.employee_name ASC;

 

 

조직도를 구해서 level, team size, budget을 구하면 된다.
1. level : 사원이 속한 조직 레벨, ceo는 레벨 1
2. team size : 각 관리자가 보고를 받는 직원 수. (직접 보고 직원 수, 간접 보고 직원 수)모두 포함된다.
3. budget : 각 관리자가 담당하는 예산, (본인 급여+ 직접 보고 직원 급여+ 간접 보고 직원 급여)

일단 with recursive문을 이용해서 계층 구조를 구해야된다. 

WITH RECURSIVE cte_name AS (
    -- 기본 쿼리 (초기 데이터를 선택)
    SELECT column1, column2, ..., columnN
    FROM table_name
    WHERE condition

    UNION ALL

    -- 재귀적 쿼리 (CTE를 자기 자신과 결합하여 반복적으로 결과를 생성)
    SELECT t.column1, t.column2, ..., t.columnN
    FROM table_name t
    JOIN cte_name cte ON t.column = cte.column
    WHERE condition
)
-- 최종 결과 쿼리
SELECT * FROM cte_name;

with recursive 문법은 이렇게 되어있는데,
초기 데이터로 ceo 행을 구하면 된다. 그리고 반복하려는 데이터로 각 직원의 하위 직원을 계속해서 가져온다.
재귀쿼리를 구하면서 level을 구하기 위해 level+1을 해주었다.

그리고 teme size와 budget을 구하기 위해 org_order을 구했다.
왜냐면 두 값을 구하기 위해서는 보고 체계 순서를 알아야한다.
여기서 employee_id가 6인경우 보고 체계 순서(org_order)은 1,3,6이다. 
재귀 쿼리 초기문에 CAST(employee_id AS CHAR) as org_order로 초기값을 세팅하고,
재귀 쿠리  반복문에 CONCAT(o.org_order, ',', CAST(e.employee_id AS CHAR)) as org_order
o.org_order(초기 데이터)와 employee_id를 쉼표로 연결해줬다.
그러면 재귀 쿼리문이 반복되면서 보고 체계 순서를 구할 수 있다.

 

orgChart 테이

재귀 쿼리로 구한 orgchart를 조회해보면, 미리 구해 놓은 보고 체게 순서(org_order)가 있다.
이제 구한 org_order 컬럼을 이용해서 team size와 budget을 구할 수 있다.

FIND_IN_SET함수를 이용해서 구하면 되는데, 이 함수는 쉼표로 구분된 문자열에서 찾을 값이 몇 번째 위치에 있는 지 반환하는 함수다.

(SELECT 
        COUNT(*) - 1 
     FROM orgChart oc
     WHERE FIND_IN_SET(CAST(orgChart.employee_id AS CHAR), oc.org_order) > 0
    ) AS team_size,


FIND_IN_SET함수를 이용해, org_order컬럼에서 해당 직원이 속한 행의 개수를 찾고 -1 하면 team_size를 구할 수 있다.
(-1 하는 이유는 본인은 제외하기 때문에)

  (SELECT 
        SUM(oc.salary) 
     FROM orgChart oc
     WHERE FIND_IN_SET(CAST(orgChart.employee_id AS CHAR), oc.org_order) > 0
    ) AS budget
    
    그리고 위와 같은 방법으로 budget을 구할 수 있는데,
FIND_IN_SET함수를 이용해, org_order컬럼에서 해당 직원이 속한 행의 월급을 모두 더하면 된다.

 

✅ 사용 문법 정리

재귀 쿼리문

WITH RECURSIVE cte_name AS (
    -- 기본 쿼리 (초기 데이터를 선택)
    SELECT column1, column2, ..., columnN
    FROM table_name
    WHERE condition

    UNION ALL

    -- 재귀적 쿼리 (CTE를 자기 자신과 결합하여 반복적으로 결과를 생성)
    SELECT t.column1, t.column2, ..., t.columnN
    FROM table_name t
    JOIN cte_name cte ON t.column = cte.column
    WHERE condition
)
-- 최종 결과 쿼리
SELECT * FROM cte_name;

FIND_IN_SET(찾을_값, '값1,값2,값3,...')

: :쉼표로 구분된 문자열에서 찾을 값이 몇 번째 위치에 있는 지 반환