SQL Project Planning과 Islands and Gaps 패턴

MYSQL/HakerRank_Medium

SQL Project Planning과 Islands and Gaps 패턴

수스리 2025. 4. 21. 13:08

문제

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.

Projects라는 테이블이 주어집니다. 이 테이블은 Task_ID, Start_Date, End_Date 세 개의 열을 포함하고 있습니다. 테이블의 각 행에서 End_Date와 Start_Date의 차이는 1일임이 보장됩니다.

만약 작업들의 End_Date가 연속적이라면, 그 작업들은 같은 프로젝트의 일부입니다. Samantha는 완료된 서로 다른 프로젝트의 총 개수를 찾는 데 관심이 있습니다.

프로젝트를 완료하는 데 걸린 일수에 따라 오름차순으로 정렬하여 프로젝트의 시작 날짜와 종료 날짜를 출력하는 쿼리를 작성하세요. 완료하는 데 같은 일수가 걸린 프로젝트가 둘 이상 있다면, 프로젝트의 시작 날짜를 기준으로 정렬하세요.

WITH TEMP AS (
    SELECT *, ROW_NUMBER() OVER(ORDER BY End_Date) AS NUM
    FROM Projects
    ORDER BY End_Date
), TEMP2 AS (
    SELECT *, (END_DATE - NUM) AS S_P
    FROM TEMP
)
SELECT MIN(Start_date), MAX(End_date)
FROM TEMP2
GROUP BY S_P
ORDER BY TIMESTAMPDIFF(DAY, MIN(Start_date), MAX(End_date)) , MIN(Start_date)

여기서 써야할 패턴이 하나 있다. 나는 이전 까지 SQL에도 알고리즘 같은 개념이 있는 지 몰랐다. 이번에 처음 알았다. 여기서 쓰는 패턴은 Islands and Gaps 패턴이다. Islands and Gaps 패턴은 DB에서 연속된 값들의 집합(이것들을 Islands)과 그 사이의 간격을 알려주는 패턴이다.

예시

2023-01-01, 2023-01-02, 2023-01-03, 2023-01-07, 2023-01-08, 2023-01-12

이런 패턴이 있다고 생각해보자. 여기는 세 그룹(Islands) 이 있다.

그룹 1: 2023-01-01, 2023-01-02, 2023-01-03
그룹 2: 2023-01-07, 2023-01-08
그룹 3: 2023-01-12

1. 먼저 날짜를 정렬하고 각 행에 번호를 부여한다.

날짜	행 번호
2023-01-01	1
2023-01-02	2
2023-01-03	3
2023-01-07	4
2023-01-08	5
2023-01-12	6

2. 각 날짜에서 행 번호를 배 준다.

날짜	행 번호	날짜 - 행 번호(일)	그룹ID
2023-01-01	1	2022-12-31	1
2023-01-02	2	2022-12-31	1
2023-01-03	3	2022-12-31	1
2023-01-07	4	2023-01-03	2
2023-01-08	5	2023-01-03	2
2023-01-12	6	2023-01-06	3

이렇게 하면 그룹을 나눌 수 있다. 이런식으로 연속된 데이터를 집합해서 그 격차를 구한다.\

WITH TEMP AS (
    SELECT *, ROW_NUMBER() OVER(ORDER BY End_Date) AS NUM
    FROM Projects
    ORDER BY End_Date
), TEMP2 AS (
    SELECT *, (END_DATE - NUM) AS S_P
    FROM TEMP
)

나는 여기서 날짜에 End_Date를 기준으로 번호를 매겼다. 그리고 End_Date에서 NUM을 빼서 각 그룹을 찾았다.

SELECT MIN(Start_date), MAX(End_date)
FROM TEMP2
GROUP BY S_P
ORDER BY TIMESTAMPDIFF(DAY, MIN(Start_date), MAX(End_date)) , MIN(Start_date)

그룹화 한 다음에 종료일과 시작일을 구해서 정렬한다음 쿼리를 마무리한다.

<2025년 5월 5일 복습>

WITH TEMP AS(
    SELECT
        *, ROW_NUMBER() OVER(ORDER BY End_Date) AS row_num
    FROM
        Projects
),
TEMP2 AS (
    SELECT *, End_Date - row_num AS project
    FROM TEMP
)
SELECT MIN(Start_Date), MAX(End_Date)
FROM TEMP2
GROUP BY project
ORDER BY COUNT(*) ASC , MIN(Start_Date)

쉽게 풀었다. 한번 공부하고 나니 괜찮네!