[문제풀이] Hacker Rank - SQL Project Planning

출처

SQL Project Planning | HackerRank

 

문제

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.

 

문제에 대한 해석

프로젝트 1: 작업 1, 2, 3은 연속된 날에 완료되므로 프로젝트의 일부입니다. 따라서 프로젝트 시작일은 2015-10-01이고 종료일은 2015-10-04 프로젝트 2: 작업 4와 5는 연속된 날에 완료되므로 프로젝트의 일부입니다. 따라서 프로젝트 시작일이 2015-10-13이고 종료일이 2015-10-15, 소요시간 2일다 프로젝트 3: 작업 6만 프로젝트의 일부입니다. 따라서 프로젝트 시작일은 2015-10-28이고 소요시간 1일 프로젝트 4: 작업 7만 프로젝트의 일부입니다. 따라서 프로젝트 시작일은 2015-10-30이고 종료일은 2015-10-31이고 소요시간 1일

답에 대한 예시

프로젝트의 시작날짜와 끝나는 날짜를 조회하는데 소요 시간(끝나는 날짜 - 시작날짜)의 오름차순으로 정렬 단, 동일한 소요시간의 경우 시작날짜 순으로 정렬

 

 

즉, end_date와 다른task_id의 start_date가 동일하면 이어지는 프로젝트다

 

쿼리의 목적 

end_date와 동일한  start_date를 찾아라

-> NO!  결국 필요한 것은 시작날짜와 프로젝트의 끝날짜 이므로

end_date와 동일한  start_date는 쿼리에 조회되지 않는다(불필요한 데이터..)

 

결국, 시작날짜와 끝 날짜가 서로 동일하지 않는 날짜를 찾아야

각 프로젝트의 첫 날짜와 끝 날짜를 조회할 수 있음

 

오답 풀이(MYSQL)  

-- 오답
with tmp as (select 
    task_id,
    start_date,
    end_date,
    lead(start_date)over(order by task_id ) as next_start_date
from Projects
), -- 다음 프로젝트의 시작일을 출력
tmp2 as (
    select task_id, 
     start_date,
     end_date,
     next_start_date,
     if(datediff(next_start_date ,end_date)=0, 1,0) as next_end 
from tmp) -- 해당 프로젝트의 완료일과 다음 프로젝트의 시작일의 차가 0일 경우 1 아니면 0
select
    task_id, 
     start_date,
     end_date,
          next_start_date,
          next_end 
from tmp2
where next_end = 1 -- 해당 프로젝트와  다음 프로젝트의 시작일의 차가 0일 경우만 조회

 

오답의 원인

문제의 의도가 맞다면 테스크 1의 완료날짜와 테스크 1의 시작날짜가 같아야 하지만 datediff를 통해 날짜의 차를 구할때 0인 경우가 없다

즉, task_id순으로 업무가 이어지는 게 아닌 것 같다

 

정답 풀이(MYSQL)

SELECT Start_Date, MIN(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 -- a와 b의 Cartesian product(cross조인)
WHERE Start_Date < End_Date -- 논리상 시작날짜가 끝 날짜보다 먼저여야 한다
GROUP BY Start_Date -- 시작날짜끼리 조인하여 끝나는 날짜가 가장 작은 날짜를 출력한다
ORDER BY DATEDIFF(MIN(End_Date), Start_Date), Start_Date

 

1단계

SELECT Start_Date, 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))

 

의미 : 시작날짜가 끝날짜에 포함되지 않은(시작날짜를 출력할때 끝나는 날짜들과 동일하니 않는 시작날짜)와

마찬가지의 끝나는 날짜를 카르시안 곱을 해서 모든 경우의 수의 Start_Date, End_Date를 출력하라

 

> 무슨 의미 인지잘 모르겠음..

굼금증1. Start_Date NOT IN End_Date와  Start_Date NOT IN (SELECT End_Date FROM Projects))의 차이

 

쿼리1) 오답

SELECT Task_id, Start_Date , End_date
FROM Projects 
WHERE Start_Date NOT IN ( End_Date)

의미 : Start_Date가 End_Date컬럼의 데이터에 포함되지 않는 Start_Date를 조회

즉, 2015-10-01의 시작날짜는 End_Date컬럼에 없다는 것

 

결과 

쿼리1)의 조회 = 모든 데이터 출력

 

왜) 1행의 2015-10-01의 시작일이 projects테이블의 end_date컬럼에 포함되지 않는 걸 찾는게 아니라

1행의 2015-10-01의 시작일이 1행의 2015-10-01의 끝날짜에 포함되지 않는 것을 찾는 것으로 출력됨

그러나 데이터 설명에서 동일한task_id의 시작날짜와 끝날짜는 모조건 하루 날짜의 차이가 있다

따르서 모든행이 조건에 충족되어 조회된 것

원했던 것	결과
2015-10-01과  하단 end_date컬럼과의 비교	2015-10-01과 2015-10-02의 비교(같은행끼리)

 

즉, start_date의 행1개의 데이터와 end_date컬럼의 모든 데이터를 비교하기 위해서는

서브쿼리를 통해 end_date를 불러와야 한다

 

쿼리2 : start_date)

SELECT Task_id, Start_Date , End_date
FROM Projects 
WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)

 

쿼리3 : end_date)

SELECT  Task_id, Start_Date , End_date
     FROM Projects
    WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)

 

쿼리2와 쿼리3의 결과를 표시해 보면

각 프로젝트의 시작날(쿼리2)과 끝나는 날짜(쿼리3)인 것을 알 수 있다

이유 : 시작날짜가 끝날짜랑 동일하거나

끝 날짜가 시작날짜와 동일한 경우는 모두 필터링 하여 제외했기 때문

쿼리1 (모든 데이터)	쿼리1에 쿼리2(start_Date) 표시	쿼리1에 쿼리3(end_Date) 표시

 

2단계 : 구해진 시작날짜와 끝날짜 합치기

데이터에서 중복이 없는 시작날짜와 끝날짜의 크로스 조인(조인조건 on을 사용하지 않으면 자동으로 카르시안 곱이 완성) 하여 논리적 구조 반영

 

 

SELECT Start_Date, 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

 

결과 : 각 시작날짜(11개) X 각 끝나는 날짜(11개)의 결과에

논리적으로 끝날짜는 시작날짜 이후여야 하므로 start date < end_date필터링

데이터에서 끝날짜는 시작날짜보다 하루 다음날이므로 같을 수는 없음

where적용 전 (121개)	where적용 후(66개)

 

3단계 : 시작날짜와 끝 날짜 매칭하기

SELECT Start_Date, MIN(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 Start_Date 
ORDER BY DATEDIFF(MIN(End_Date), Start_Date), Start_Date

실제 데이터에서의 매칭 2015-10-25 와 2015-10-31	크로스 조인된 데이터에서의 매칭

 

결과:

(1) 나열된 시작날짜와 끝날짜에서 시작날짜를 기준으로 그룹화 하여

끝날짜의 최솟값을 구한다

*MIN함수를 쓰는 이유 : 시작날짜와 가장 가까운 날짜를 계산하기 위해 > 다른 날짜는 다른 시작날짜의 끝날짜이기 때문

(2) 시작날짜와 그룹회된 시작날짜의 끝날짜와의 기간(datediff )를 통계 계산하여 오름차순 + 시작날짜 오름차순

 

배운점

문제에 대한 이해

> 시작날짜와 끝날짜를 찾는 것이 문제의 시작,

> 시작날짜들과 끝날짜들을 찾았으면 올바르게 매칠할 수 있는가 : 날짜의 조건, 가장 가까운 끝날짜 계산

> 각 날짜들의 차를 계산하여 소요시간에 따라 정렬할 수 있는가