해커랭크의 SQL Project Planning 문제를 풀어보았다.
👉🏻 문제보러가기
문제 요약
Projects라는 테이블에는 Task_ID, Start_Date, End_Date가 기록되어 있다.
- 각 행의 Start_Date와 End_Date는 모두 하루 차이가 난다.
- Task의 End_Date가 연속적이라면, 그 Task들은 한 프로젝트이다.
=> 각 프로젝트 의 Start_Date와 End_Date를 출력하라
풀이1 : ROW_NUBMER를 활용한 풀이
샘플 데이터를 살펴보자.
같은 프로젝트라면 각 행이 하루씩 차이가 나고, 전 행의 End_Date가 다음 행의 Start_Date가 됨을 알 수 있다.
- 따라서 프로젝트의 Start_Date는 End_Date 데이터에 존재하지 않는다.
- 또한, 프로젝트의 End_Date는 Start_Date 데이터에 존재하지 않는다.
이 점을 활용해서 '프로젝트'의 Start_Date와 End_Date를 찾아줄 수 있다.
1. IN을 활용해 프로젝트의 Start_Date와 End_Date 리스트를 찾아주자.
A) 프로젝트의 End_Date를 찾는다.
SELECT End_Date
FROM Projects
WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)
B) 프로젝트의 Start_Date를 찾는다.
SELECT Start_Date
FROM Projects
WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)
2. ROW_NUMBER()를 활용해 프로젝트의 Start_Date와 End_Date를 매칭해주자.
이제 문제는 '이 각각의 데이터를 어떻게 하나의 프로젝트로 매칭해줄 것인가'이다.
2번 코드로 뽑은 Start_Date의 각 데이터와 1번 코드로 뽑은 End_Date의 각 데이터를 순서대로 매칭해주기 위해 ROW_NUMBER()를 활용할 수 있다.
ROW_NUMBER()
ROW_NUMBER()는 쿼리 결과 데이터의 각 행에 순서대로 숫자를 매기는 윈도우 함수이다.ROW_NUMBER() OVER ( [PARTITION BY expr1, expr2,...] ORDER BY expr1 [ASC | DESC], expr2,... )
- ORDER BY에 어떤 컬럼을 기준으로 정렬하여 숫자를 매길 것인지 지정해준다.
- PARTITION BY는 선택사항이다.
A) 1번 쿼리에 ROW_NUMBER()를 추가하여 다시 추출해준다.
SELECT End_Date,
ROW_NUMBER() OVER(ORDER BY End_Date) AS prj_num
FROM Projects
WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)SELECT Start_Date,
ROW_NUMBER() OVER(ORDER BY Start_Date) AS prj_num
FROM Projects
WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)B) 최종 ROW_NUMBER를 기준으로 두 쿼리 결과를 조인해주고, 문제에서 요구하는대로 정렬하여 쿼리를 완성한다.
SELECT Start_Date, End_Date
FROM (SELECT Start_Date,
ROW_NUMBER() OVER(ORDER BY Start_Date) AS prj_num
FROM Projects
WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)
) AS p_start
INNER JOIN (SELECT End_Date,
ROW_NUMBER() OVER(ORDER BY End_Date) AS prj_num
FROM Projects
WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)
) AS p_end
ON p_start.prj_num = p_end.prj_num
3. 최종: 문제에서 요구하는대로 정렬하여 출력해준다.
문제의 정렬에 대한 요구사항
1. 프로젝트의 기간이 긴 순으로 정렬하라 -> DATEDIFF() 함수를 활용
2. 프로젝트의 기간이 같은 프로젝트가 있다면, Start_Date 순으로 정렬하라.
DATEDIFF()
일자의 차이를 구해주는 함수SELECT DATEDIFF(OLD_DATE,NEW_DATE) FROM DATETBALE
SELECT Start_Date, End_Date
FROM (SELECT Start_Date,
ROW_NUMBER() OVER(ORDER BY Start_Date) AS prj_num
FROM Projects
WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)
) AS p_start
INNER JOIN (SELECT End_Date,
ROW_NUMBER() OVER(ORDER BY End_Date) AS prj_num
FROM Projects
WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)
) AS p_end
ON p_start.prj_num = p_end.prj_num
ORDER BY DATEDIFF(End_Date, Start_Date), Start_Date풀이2 : 집계함수를 이용한 풀이
ROW_NUMBER를 사용하지 않고는 어떻게 풀 수 있을까?
1. 프로젝트의 Start_Date와 End_Date를 찾아주고, 조인해준다.
이번에는 Start_Date < End_Date 를 조건으로 주어 조인을 해준다.
SELECT *
FROM
(SELECT Start_Date
FROM Projects
WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
(SELECT End_Date
FROM Projects
WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b
WHERE Start_Date < End_Date
2. 최종 : Start_Date를 기준으로 GROUP BY 하여 End_Date를 찾아주고, 정렬해준다.
1번의 결과를 보면, Start_Date < End_Date 인 모든 경우의 수에 대하여 조인된 것을 볼 수 있다. 이 중에서 "찐" End_Date는 어떻게 찾아줄 수 있을까?
모든 경우의 수 중에서, 가장 예전 일자가 프로젝트의 End_Date일 것이다. 따라서 Start_Date를 기준으로 GROUP BY 해주고, MIN()함수로 가장 예전 일자의 End_Date를 찾아준다.
SELECT Start_Date, MIN(End_Date) AS End_Date
FROM
(SELECT Start_Date
FROM Projects
WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
(SELECT End_Date
FROM Projects
WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b
WHERE Start_Date < End_Date
GROUP BY 1
ORDER BY DATEDIFF(MIN(End_Date), Start_Date), Start_Date배운 점
- 프로젝트의 Start_Date와 End_Date를 찾아내는 논리는 생각해냈었는데 복잡하게 셀프조인을 시켜서 NULL인 값을 추출해내는 방법만 생각했었다. 간단하게
IN을 사용할 수 있다! - 조건에 맞는 컬럼을 각각 구한 후 합쳐서 프린트해주고 싶을 때, ROW_NUMBER를 사용해서 조인을 해줄 수 있다.
- ORDER BY 에서도 함수 사용이 가능하다.
