HackerRank SQL Project Planning

https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true

SQL Project Planning | HackerRank

 

아... 못 풀었어요..

이틀정도.. 생각을 하고 별짓을 다했는데... 더 시간 끌면 뭔가 시간 낭비인 거 같아서 이쯤 하고 답을 찾아봤습니다.

 

일단 문제 요약해 드릴게요

요약 :

Table구성은 task_id, start_date, end_date로 이루어져 있고, id가 어떻든 end_date와 start_date가 일치하면 동일한 프로젝트인 것입니다.

예로, id 1인 프로젝트가 2월 24일에 끝났고, id 2인 프로젝트가 2월 24일에 시작했다면 이 두 개의 pj는 같은 pj입니다.

그래서 우리가 구해야 할 값은 이어져있는 pj의 제일 첫날과 가장 끝날을 구해야 하는 것입니다.

 

문제는 참 쉬워 보이는데... 이걸 구현해 내기가 정말 힘들더라고요..

 

처음 접근했던 방식은 먼저 끝날을 가져오는 sql을 만들어 보자였습니다.

그래서 case when과 lead함수를 사용해서 뒤의 start와 앞의 end가 같다면 end를 출력하라였습니다. 

이렇게 해서 row_nuber를 주고, 만약 start와 end의 차이가 1보다 크면 다른 pj이기 때문에 row_number에 2를 더하는 식으로 구현을 했었어요.. 어찌어찌해서 마지막 날을 구하긴 했는데.. 첫날을 구하기가 정말.. 후..

눈에 습기가 차네요.. 손바닥과... 흑흑

 

 

 

그래서 다른 블로그들을 참고해 봤고... 정말.. 간단하게 풀어내신 분이 있어 그걸 참고했습니다.. 

제 원래 코드는 거의 30줄이었는데.. 하...

(참고 : https://sowhatmylifeismine.tistory.com/149)

 

제가 이해한 대로 풀어보겠습니다.

전체 코드는 아래와 같습니다. 

 

코드 첫 문장.. 아니 처음 돌아가는 sub부터 설명해 보겠습니다.

(아시다시피 sub query가 먼저 돌아가고, from에 쓰였으니 그 결괏값을 바탕으로 외부 쿼리의 select 부분이 도출이 됩니다.)

서브에서 보시면 row_number가 쓰였고, 쓴 이유는 순서를 주기 위함이죠

왜 순서를 줘야 하냐? 왜냐하면 그룹으로 묶어야 하기 때문입니다.  백문이불여일견이니 아래 그림을 참고해 주세요.

 

 

여기서 보시면 row_number로 순서가 정해졌습니다. 

( SELECT START_dATE, END_DATE
    , ROW_NUMBER() OVER(ORDER BY START_dATE)
    FROM PROJECTS)

 

그래서 뭐 어떻게 하라고? 여기서 start_date와 뺄셈을 한다면? 아래의 그림처럼 동일한 형식의 행이 만들어져요.

그거를 기준으로 group  by를 해주면 됩니다. (GROUP BY rnk)

 

그다음, 서브쿼리 결과로 나온 걸 외부 쿼리에서 select 해주고, order by로 정렬을 해주면 됩니다. 

여기서 min과 max를 사용하시면 됩니다. min, max가 어떻게 먹히느냐?

group by를 rnk를 기준으로 해주었으니, 그 그룹 안에서 가장 작은 start_date과 가장 큰 end_date를 구하면 됩니다. 

그다음, order by에서 datediff 함수를 써서 기간을 구해주고, 그 기간에 맞추어 정렬을 해주면 됩니다. 

 

후...

정말 힘들었어요...

이러다가 머리카락 다 빠질 거 같아서.. 살기 위해.. 답을 봤습니다...

진 기분이라 그리 마음이 좋지만은 안내요..

근데 이 문제를 통해서 뭐... mysql에 해당하는 건 아니지만 connected by? 계층형? 이런 말도 좀 알게 되었어요..

mysql에도 orcle같이 그런 기능이 있으면 모두들 머리카락 사수하고 얼마나 좋아요... 하....

하여간.. 그래도... 이런 식으로도 접근이 가능하다는 것도 알게 되어서.. 시야가 넓어진 느낌입니다.

 

다들.. 열공하세요.... 흑흑

 

 

(수정 : 수석님께서.. 풀어주신... 완벽한 코드입니다..)

 

WITH RECURSIVE
     prcs_data AS (
         SELECT task_id, start_date, end_date, af_start_date
              , case when end_date = af_start_date then af_start_date end AS conn_key
           FROM (SELECT task_id, start_date, end_date
                      , LEAD(start_date) OVER (ORDER BY start_date) AS af_start_date
                   FROM PROJECTS
                ) s1
  ), level_data AS (
         SELECT task_id, start_date, end_date, af_start_date, conn_key, start_date AS root_date
           FROM prcs_data
          where conn_key IS NULL 
          UNION ALL
         SELECT p1.task_id, p1.start_date, p1.end_date, p1.af_start_date, p1.conn_key, p2.root_date
         FROM prcs_data p1
         JOIN level_data p2 ON p1.conn_key = p2.start_date   
)
SELECT  MIN(start_date) AS start_date, MAX(end_date) AS end_date FROM level_data
 GROUP BY root_date
 order by count(1)

재귀 함수를 사용해서 푸는 방법입니다. 

recursive가 다른 with절이 들어간다는 것과 한 행만 비교하는게 아니라 위의 행과 비교할 수 있는 능력이 있더라구요..

너무 띄엄띄엄 알고 있었나봅니다ㅜㅜㅜ

다시..공부해야겠어요ㅜㅜ