문제
You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.
If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects completed.
Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.
예시
Sample Input
Sample Output
2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04
Plain Text
복사
정답
SELECT MAX(Start_Date), MAX(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 End_Date
ORDER BY DATEDIFF(MAX(End_Date),MAX(Start_Date)), MAX(Start_Date);
SQL
복사
풀이
•
DATEDIFF() 함수와 CROSS JOIN 방법을 묻는 문제이다.
•
우선 프로젝트의 진짜 시작 날짜와 끝 날짜만을 구해보자.
•
진짜 시작 날짜의 경우 Start_Date에는 포함되어 있지만 End_Date에는 포함되어 있지 않은 날짜이다. 따라서 진짜 시작 날짜를 구하는 쿼리는 다음과 같다.
(SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects))
SQL
복사
•
반대로 진짜 끝 날짜의 경우 Start_Date에 포함되어서는 안 된다.
(SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects))
SQL
복사
•
이제 진짜 시작 날짜 테이블을 a, 진짜 끝 날짜 테이블을 b라고 명명하고 CROSS JOIN을 수행하자. MySQL에서 CROSS JOIN을 수행하는 방법은 그냥 테이블을 ,로 이으면 된다.
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
SQL
복사
•
이때 결과는 다음과 같다.
•
왼쪽에 시작 날짜가 큰 값부터 차례로 붙고 오른쪽은 끝 날짜이다.
•
이때 하나의 프로젝트를 구분하는 방법은 왼쪽에 있는 시작 날짜의 값이 오른쪽에 있는 끝 날짜보다 작다면 하나의 프로젝트로서 가능하다.
•
예를 들어 위의 결과에서 2015-10-01은 2015-10-05보다 작기 때문에 이것은 하나의 프로젝트가 될 수 있다는 것을 의미한다.
•
다만 주의할 것은 모든 결과가 JOIN 되어있는 상태이기 때문에 하나의 프로젝트가 될 가능성이 있는 것이지 하나의 프로젝트가 맞다는 뜻은 아니다.
•
Start_Date < End_Date를 만족하는 결과를 살펴보자.
WHERE Start_Date < End_Date
SQL
복사
•
끝 날짜가 2015-10-05의 경우 작은 값이 2015-10-01 한 개이지만
•
끝 날짜가 2015-10-13인 경우 2015-10-11과 2015-10-01 두 개이다. 즉, 시작 날짜가 누적해서 나타나는 것.
•
따라서 끝 날짜를 기준으로 그룹핑을 한 뒤 Start_Date가 MAX인 데이터를 추출하자.
SELECT MAX(Start_Date), MAX(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 End_Date
SQL
복사
•
그리고 마지막으로 DATEDIFF() 함수를 이용하여 날짜 차이가 적을수록 위에 오도록 정렬하고, start_date를 기준으로 이차적으로 정렬하면 된다.
SELECT MAX(Start_Date), MAX(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 End_Date
ORDER BY DATEDIFF(MAX(End_Date),MAX(Start_Date)), MAX(Start_Date);
SQL
복사