[SQL] HackerRank - Project Planning
Problem
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
해석하면.. 연속되는 날짜의 Task는 같은 프로젝트라는 조건이고 사만다라는 친구가 알고 싶은 데이터는, 프로젝트 별로 걸린 시간을 알고 싶어하는 것 같다.
Sample Input을 예로 든다면 2015-10-01에 시작한 프로젝트는 연속된 task가 존재하고 2015-10-04에 종료된다. 이걸 알 수 있는 이유는 4번 task에 시작된 project의 start-date가 2015-10-13으로 연속되지 않기 때문이다.
그리고 문제에서 정렬 조건을 추가했는데, 프로젝트에 걸린 기간이 가장 짧은 순으로 정렬하길 원한다. 요건 간단하게
ORDER BY (end_date) - (start_date), start_date 로 정렬이 가능할 것 같다.
따라서 총 프로젝트는
2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04
Solution
문제 해결의 키워드는 다음과 같다.
1. start_date 컬럼에 존재하는 날짜가 end_date에 존재하지 않으면 새로운 프로젝트의 시작 날짜
2. end_date 컬럼에 존재하는 날짜가 start_date에 존재하지 않으면 해당 프로젝트의 종료 날짜
3. 프로젝트별 그룹핑 방법
SELECT sd, ed
FROM (
SELECT start_date AS sd, end_date AS ed
FROM Projects
)sub
WHERE sd NOT IN (ed) AND ed NOT IN (sd);역시나 간단하게 해결되지 않았다. 내일 다시 해결해보자.
- 다음날
일어나자마자 콤퓨타를 키고 어제 작성한 쿼리문을 봤는데 문법적으로 잘못된 부분이 몇가지 보였다. 수정해서 다시 작성했고 아직은 order 와 group은 신경 쓰지 않았다.
SELECT start_date, end_date
FROM projects
WHERE start_date NOT IN (SELECT end_date FROM Projects) AND end_date NOT IN(SELECT start_date FROM Projects)
ORDER BY start_date
10-01에 시작한 프로젝트가 빠져있엇다. 이유를 살펴보니 Where 절에 있는 AND 연산자 때문인것 같았다. 2015-10-02이라는 레코드의 end_date가 다음 row의 start_date에 포함되기 때문에 결과값에서 제외당한듯. 그러면 AND 연산자가 아니라 start_date 컬럼과 end_date컬럼을 따로 분리시켜서 뽑아야하고 이걸 start_date로 그룹핑 시키면 내가 원하는 데이터셋을 뽑을 수 있지 않을까? 서브쿼리를 사용해야겠다고 생각했다.
GROUP BY 절을 사용하려면 집계함수를 포함해야한다?
그룹별로 데이터를 묶어서 처리하기 위한 구문으로 집계함수 없이는 그룹 단위로 데이터를 집계할 수 없다.
예를들어, 아래의 쿼리는 employees 테이블에서 department_id 별로 그룹을 지어서 department_id와 해당 부서의 직원 수를 출력
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
SELECT start_date, end_date
FROM
(SELECT start_date FROM Projects WHERE start_date NOT IN (SELECT end_date FROM Projects)) project_start_date,
(SELECT end_date FROM Projects WHERE end_date NOT IN (SELECT start_date FROM Projects)) project_end_date
ORDER BY start_date역시나 실패헀다 결과값은 하나의 start_date 값에 모든 end_date (which is not in start_date)값이 하나의 row씩 전부 출력되었다. 요 문제는 그룹핑이 안되있어서 발생하는 문제였다 이건 해결하려면 GROUP BY start_date하면 해결할 수 있지 않을까?
this is incompatible with sql_mode=only_full_group_by위와 같은 runtime error 발생 SELECT 절에서 집계함수가 사용되지 않아 그루핑 할 대상이 명확하지 않았기 때문에 발생했다. @SET을 이용해서 설정을 잘 하면 집계함수를 사용하지 않더라도 해결이 가능하다고 하지만.. 다음에 공부하기로 하고 집계함수를 사용했더니 그루핑이 잘 되었다.
SELECT start_date, MIN(end_date)
FROM
(SELECT start_date FROM Projects WHERE start_date NOT IN (SELECT end_date FROM Projects)) project_start_date,
(SELECT end_date FROM Projects WHERE end_date NOT IN (SELECT start_date FROM Projects)) project_end_date
GROUP BY start_date
ORDER BY start_date
end_date가 뭔가 이상하다. MIN 함수를 사용했더니 최소값만 반환하고 있다. WHERE 컨디션이 추가가 필요 할 것 같다.
start_date보다 큰, 최소값의 end_date를 뽑아오면 적당할 것 같다.
WHERE end_date>start_date
이제 정렬을 해야하는데... 위에서 말한 정렬조건은
1. 프로젝트 기간이 가장 짧은 순 ==> end_date - start_date
2. Start_date 오름차순
손이 많이 갈것 같기도하고 분명 날짜 비교 관련 함수가 존재할 것 같아서 검색해 봤다.
DATEDIFF() 함수를 사용함.
SELECT start_date, MIN(end_date)
FROM
(SELECT start_date FROM Projects WHERE start_date NOT IN (SELECT end_date FROM Projects)) project_start_date,
(SELECT end_date FROM Projects WHERE end_date NOT IN (SELECT start_date FROM Projects)) project_end_date
WHERE start_date<end_date
GROUP BY start_date
ORDER BY DATEDIFF(MIN(end_date), start_date), start_date
문제 해결!