[TIL] 다중 CTE 활용한 복잡한 문제 해결 # MySQL # SQL

HackerRank Intermediate - SQL Project Planning

👉 문제 바로가기(HackerRank)

WITH CTE AS (
    SELECT Task_ID, Start_Date, End_Date
    FROM Projects
    WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)
    ORDER BY Start_Date
    ), 
CTE_2 AS (
    SELECT
        t1.Task_ID, t1.Start_Date, t1.End_Date, t2.Start_Date AS Start_Date_2, t2.End_Date AS End_Date_2
    FROM 
        Projects t1 LEFT OUTER JOIN CTE t2
        ON t1.task_ID = t2.task_ID
    ),
CTE_3 AS (
    SELECT
        Task_ID,
        RANK() OVER(ORDER BY Start_Date) AS rnk,
        CASE
            WHEN Task_ID = 1 THEN Start_Date
            WHEN Task_ID !=1 AND (LAG(End_Date_2, 1) OVER (ORDER BY Start_Date)) IS NOT NULL THEN Start_Date
        END as new_Start_Date,
        CASE
            WHEN End_Date_2 IS NOT NULL THEN End_Date_2 
        END AS new_End_Date
    FROM
      CTE_2
    ), 
CTE_4 AS (
SELECT
    new_Start_Date AS Start_Date,
    CASE 
        WHEN new_End_Date IS NULL THEN LEAD(new_End_Date, 1) OVER (ORDER BY rnk)
        ELSE new_End_Date
    End AS End_Date
FROM
    CTE_3
WHERE
    new_Start_Date IS NOT NULL OR new_End_Date IS NOT NULL
    )

SELECT *
FROM CTE_4
WHERE Start_Date IS NOT NULL
ORDER BY TIMESTAMPDIFF(DAY, DATE(End_Date), DATE(Start_Date)) DESC, Start_Date

---

[문제 상황]

• Task별로 Start_Date, End_Date가 한 행에 무조건 1일 차이로 집계된다. (같은 행의 End_Date - Start_Date = 1일)
• 같은 프로젝트 Task더라도 Task_ID로 구분되며, 프로젝트 관련 식별자가 존재하지 않아서 한번에 프로젝트별 기간 산출 불가
• 시간순으로 정렬했을 때, 한 행의 End_Date와 바로 다음 행의 Start_Date가 같지 않을 때가 프로젝트로 구분되는 시점

[해결]

<해결 아이디어>

1. End_Date가 Start_Date 리스트에 없는 시점이 프로젝트 구분 시기이다. (구분 시점의 End_Date 먼저 구하기)
   • End_Date NOT IN (SELECT Start_Date FROM Projects)으로 CTE1 생성
2. 프로젝트 구분 시점의 End_Date와 본래 테이블을 병합하여 프로젝트 시작 날짜를 가져올 수 있도록 한다.
   • 원래 테이블에LEFT OUTER JOIN으로 CTE1 병합 : CTE2 생성
3. CTE2의 t2.End_Date 컬럼에서 직전 행에 데이터가 존재하지 않는 행의 t1.Start_Date가 프로젝트 시작점이 된다.
   • Task_ID가 1이 아닌 경우에 대해서 LAG함수를 활용 : CTE3 생성
   • 데이터 모든 행에 NULL이 존재하므로 정렬을 활용할 수 있도록 하기 위해 rnk 컬럼 생성
4. CTE3에서 rnk컬럼으로 오름차순 정렬했을 때, new_Start_Date, new_End_Date가 NULL이 아닌 데이터 중에서 new_End_Date가 NULL이면 다음 행의 new_End_Date가 프로젝트의 종료 시점이 된다.
   • LEAD함수 활용하여 프로젝트 종료 시점 구하기
   • CTE3의 new_Start_Date, new_End_Date가 NULL이 아닌 데이터 : 프로젝트 시작 시점의 Start_Date 데이터만 남김 : CTE4 생성
5. CTE4에서 Start_Date가 NULL인 경우를 제외하고 정렬하면 원하는 결과 도출

[회고]

• CTE를 적게쓰거나 쓰지않고 더 간편하게 해결하는 방법은 없었을까?
• 다른 프로그래밍 언어와 마찬가지로 <코드작성 - 실행 - 결과 관찰 - 디버깅 및 수정 - 결과 관찰 - ...> 을 반복하며 원하는 결과를 차근차근 얻어갈 수 있다.
• 하지만 비용(효율, 시간 등) 감소를 위해 더 간단하게 해결할 수 있는 로직을 연구해야한다.