[해커랭크 HackerRank] SQL Project Planning (Oracle)

SQL Project Planning | HackerRank

SQL Project Planning | HackerRank

Advanced Join 문제이다.

 

주어진 테이블에 start_date와 end_date라는 컬럼이 있는데, 같은 행의 두 원소는 무조건 하루가 차이 난다.

이때, 연속하는 날짜의 행은 같은 프로젝트이다. 프로젝트가 시작하는 날짜와 끝나는 날짜를 찾고 걸린 기간에 따라 정렬하면 된다.

 

위와 같은 테이블은 1~3행, 4~5행, 6행, 7행이 각각의 프로젝트임을 알 수 있다.

이때, 걸린 기간대로 정렬하면 하루가 걸린 6행, 7행이 가장 먼저 오고, 그 다음에 4~5행, 1~3행이 뒤따라옴을 알 수 있다.

 

그럼 어떻게 연속하는 행끼리 그룹화할 수 있을까?

바로 행 번호를 이용하면 된다.

START_DATE - ROW_NUMBER() OVER(ORDER BY START_DATE) AS PRJ

ROW_NUMBER() 함수는 OVER(ORDER BY COL) 조건대로 정렬한 테이블의 행 번호를 반환한다.

어떤 행 집합의 날짜가 연속한다면(=START_DATE가 1씩 커진다면), 행 번호 또한 일정하게 1씩 커지므로 연산한 결과는 같은 값을 가지게 된다.

 

위의 테이블의 경우, 1~3행은 2015-09-30, 4~5행은 2015-10-09, ... 와 같은 결과가 나오게 되는 것이다.

따라서 위 컬럼으로 GROUP BY를 실행하면, 같은 프로젝트끼리 묶을 수 있다.

 

걸린 기간으로 먼저 정렬해야 하므로, MAX(END_DATE) - MIN(START_DATE)으로 걸린 기간을 계산한다.

SELECT MIN(START_DATE), MAX(END_DATE)
FROM (
      SELECT START_DATE
           , END_DATE
           , START_DATE - ROW_NUMBER() OVER(ORDER BY START_DATE) AS PRJ
      FROM PROJECTS P
     )
GROUP BY PRJ
ORDER BY MAX(END_DATE) - MIN(START_DATE), MIN(START_DATE)
;