240608_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 코드카타 176번

이 문제에서 구해야 할 것들은

1. 어떤 task의 end_date가 다른 task의 start_date와 같은 한, 그 task들은 한 개의 프로젝트로 본다.
2. 한 프로젝트의 기간은 end_date - start_date다.
3. 출력 결과 start_date와 end_date를 명시해야 한다.
4. 기간이 짧은 순으로 정렬 (동률일 경우 start_date가 빠른 순으로 정렬)

처음에는 하나의 Project는 end_date와 start_date가 같은 task끼리 연결되어 있다는 점을 이용해 join으로 해결해 보려고 이것저것 시도해 봤는데, 이렇게 되면 프로젝트 안에 들어있는 task의 수가 늘어남에 따라 join을 그에 맞춰서 계속 해줘야 하기 때문에 일반화된 답을 구할 수 없다.

먼저 Projects 테이블을 start_date 순으로 오름차순 정렬하고, row_number 함수를 써서 각 행별로 번호를 부여해 준다.

SELECT Row_number()
         OVER(
           ORDER BY start_date) AS num,
       task_id,
       start_date,
       end_date
FROM   projects p1
ORDER  BY start_date

문제의 논리에 따르면 종료일과 시작일이 겹치는 1, 24, 2, 23번이 하나의 프로젝트로 묶이게 되는데, row_number()의 결과도 1씩 증가하고 end_date도 1씩 증가한다는 속성을 이용해 Project끼리 구분할 수 있는 구분값을 만들어 준다.

SELECT task_id,
       start_date,
       end_date,
       end_date - Row_number()
                    OVER(
                      ORDER BY start_date) AS project_no
FROM   projects p1
ORDER  BY start_date 

이제 이미지에서 보이는 것처럼 같은 project_no를 가진 것들끼리 그루핑을 해 주면 날짜의 최소값, 최대값, 그리고 프로젝트 기간을 구할 수 있다. project_no를 컬럼처럼 사용하기 위해 위 쿼리를 인라인뷰 서브쿼리로 처리하고, min(start_date)와 max(end_date)를 구한다.

SELECT project_no,
       Min(start_date),
       Max(end_date)
FROM   (SELECT task_id,
               start_date,
               end_date,
               end_date - Row_number()
                            OVER(
                              ORDER BY start_date) AS project_no
        FROM   projects p1) a
GROUP  BY 1

출력해야 할 답에 project_no는 들어가면 안 되므로(위 쿼리에서 project_no를 바로 빼면 group by를 할 수 없으므로), 위 쿼리를 다시 한 번 인라인뷰 서브쿼리 처리하고 문제의 조건대로 order by를 맞춰주면

SELECT project_start,
       project_end
FROM   (SELECT project_no,
               Min(start_date) AS project_start,
               Max(end_date)   AS project_end
        FROM   (SELECT task_id,
                       start_date,
                       end_date,
                       end_date - Row_number()
                                    OVER(
                                      ORDER BY start_date) AS project_no
                FROM   projects p1) a
        GROUP  BY 1) b
ORDER  BY project_end - project_start ASC,
          project_start ASC

위와 같이 정답이 나온다.

연속된 날짜나 숫자들을 같은 그룹으로 처리해서 해결해야 하는 문제가 있을 때는

1. 데이터의 특징을 먼저 파악해 본다. 이 문제의 경우, start_date와 end_date의 간격이 1일인 점, 그리고 매 행의 start_date가 겹치지 않는다는 점, 동일한 프로젝트라면 end_date가 1씩 증가하는 행들이 쌓일 거라는 점 등이 특징이다.
2. 이 특징들로부터 어떤 풀이를 쓸지 생각해 본다. row_number()와 같은 window함수를 능숙하게 써야 하는 것은 기본. 이 문제에서도 end_date가 1씩 증가한다는 데이터의 특징 덕분에 row_number()를 적용할 수 있었다.
   
   정직하게 join부터 시도하는 것은 시간만 잡아먹고 실효가 없는 방법인 듯. 이 문제에서는 각각의 행으로부터 '같은 Project라는 것을 구분할 수 있을만한 값'이 있어야 했기 때문(그래야 그루핑을 할 테니)에 더더욱 join은 별로인 방식이었다.

SQL 코드카타의 난이도는 다시 올라가고 있지만 차분히 풀다 보면 이해가 안 가는 문제는 없다. 한 번도 접하지 못한 엄청난 함수나 복잡한 풀이법이 등장했던 적도 없고. 첫 번째 시도가 실패했을 때 멘붕이 오지만 않게 멘탈 관리 잘 하자.