MySQL 해커랭크 | SQL Project Planning (테이블에서 필드 값 빼기)

 

문제

https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true

SQL Project Planning | HackerRank

• 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 the End_Date of the tasks are consecutive, then they are part of the same project.
  find the total number of different projects completed.
• If there is more than one project that have the same number of completion days, then order by the start date of the project.
• 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.

# 문제 해석

• 한 프로젝트의 시작일과 마지막일은 연이어져(consecutive) 있음
  • 한 행의 End_Date와 다음 행의 Start_Date가 같은 날짜면
    연이어져 있기 때문에 같은 프로젝트로 취급
• 각각의 다른 프로젝트 별로 프로젝트 첫 날과 마지막 날의 날짜를 출력하는 쿼리문 작성하기
  • 연이어져 있는 중간 날짜들 조회X

 

테이블

Projects 테이블	(예시) 각 박스 별로 한 프로젝트

 

 

풀이과정

# Projects 테이블\

• 각각의 노란 박스, 빨간 박스, 파란 박스는 다른 프로젝트임
  • 박스 안의 날짜들은 서로 연이어져 있음

SELECT *
FROM Projects

 

# Start_Date (End_Date와 겹치지 않는)

• 같은 프로젝트의 경우, 한 행의 End_Date가 다음 행의 Start_Date와 연이어져 있다는 점을 감안하면
  다음 행 End_Date들과 겹치지 않는 Start_Date를 찾으면 각 프로젝트들의 첫 시작일을 찾을 수 있음.

SELECT Start_Date From Projects WHERE Start_Date NOT IN(SELECT End_Date From Projects)

 

# End_Date (Start_Date와 겹치지 않는)

• 마찬가지로 같은 프로젝트의 경우, 한 행의 End_Date가 이전 행의 Start_Date와 연이어져 있다는 점을 감안하면
  이전 행 Start_Date들과 겹치지 않는 End_Date를 찾으면 각 프로젝트들의 마지막일을 찾을 수 있음.

SELECT End_Date FROM Projects WHERE End_Date NOT IN(SELECT Start_Date FROM Projects)

 

# 오답 : 위 두 테이블을 조인함

• 처음엔 위에서 만든 겹치지 않는 Strart_Date들과 겹치지 않는 End_Date들로 구성된 테이블을 조인하면 되겠다는 안일한 생각을 했다..

SELECT *
FROM 
(SELECT Start_Date FROM Projects WHERE Start_Date NOT IN(SELECT End_Date FROM Projects)) sd,
(SELECT End_Date FROM Projects WHERE End_Date NOT IN(SELECT Start_Date FROM Projects)) ed
ORDER BY Start_Date

두 테이블이 CROSS JOIN됨

MySQL에서는

JOIN = INNER JOIN = CROSS JOIN 된다고 함https://mag1c.tistory.com/163

[MySQL/DB] 조인(JOIN), 합집합(UNION), 중복제거(DISTINCT)

 

• 또 여기에 굳이 WHERE 조건절로 Start_Date가 End_Date보다 과거여야 한다고 필터링까지 해준 나..
• 그 후 두 날짜 사이만큼의 날들로 오름차순, 그 후엔 프로젝트 시작일로 오름차순 정렬까지 함

SELECT *
FROM (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN(SELECT End_Date FROM Projects)) sd,
     (SELECT End_Date FROM Projects WHERE End_Date NOT IN(SELECT Start_Date FROM Projects)) ed
WHERE Start_Date < End_Date
ORDER BY End_Date - Start_Date ASC, Start_Date ASC

아주 요상한 결과가 나옴

 

# 풀이과정 : SELECT문 서브쿼리 사용

• SELECT문 서브쿼리 내용을 살펴보면
  • 프로젝트 시작일과 겹치지 않는 마지막 일자를 필터링하되, 
  • 시작일보다는 커야 하며 (마지막일이 시작일보다 미래여야 함)
  • 시작일보다는 미래인 일자들 중 가~장 작은 마지막 일자를 조회하게 만듦

SELECT p.Start_Date,
      (SELECT MIN(End_Date) FROM Projects WHERE End_Date NOT IN(SELECT Start_Date FROM Projects) AND p.Start_Date < End_Date)
FROM Projects p
 
-- SELECT 문의 서브쿼리에 MIN(End_Date)가 아니라 End_Date를 쓰면 런타임 에러가 뜸
-- Runtime Error : ERROR 1242 (21000) at line 1: Subquery returns more than 1 row

 

# 정답

• 위 테이블에 프로젝트 기간 오름차순, 프로젝트 시작일 오름차순으로 정렬 조건까지 주면 끝!

• 특이사항
  • SELECT문 서브쿼리 이름을 ed라고 지음
  • ed라는 테이블에서 SELECT 하고 있는 데이터 : MIN(End_Date)
  • ORDER BY절에 보면 ed - p.Start_Date 이렇게 해줬는데
    이렇게 한 테이블에서 한 데이터를 빼는 게 가능하다는 것을 알게 됨
    (그 서브쿼리 테이블에서 반환하는 값이 하나라서 가능한 것 같음!)

SELECT p.Start_Date,
      (SELECT MIN(End_Date) FROM Projects WHERE End_Date NOT IN(SELECT Start_Date FROM Projects) AND p.Start_Date < End_Date) ed
FROM Projects p 
WHERE p.Start_Date NOT IN(SELECT End_Date FROM Projects)
ORDER BY ed - p.Start_Date ASC, p.Start_Date