[ Oracle ] NVL, LAG, LEAD, INTERVAL, ROW_NUMBER를 사용해 프로젝트 시작 날짜와 끝난 날짜 출력

본문으로

안녕하세요. 녀기입니다.

 

분석가로서 SQL의 사용은 중요하기 때문에

 

계속 연습하고 있습니다!!

아오 머리터져

 

오늘 저를 힘들게 했던 문제의 쿼리문을 올려 놓고

 

필기를 해 놓기로 합시다!

 

그럼 렛츠 고도리~~

---

NVL, LAG, LEAD, INTERVAL, ROW_NUMBER

with dates as (
        select start_date, 
                nvl(lead(start_date) over(order by start_date), start_date + interval '2' day) as next_start,
                end_date, 
                nvl(lag(end_date) over(order by end_date), end_date - interval '2' day) as prev_end
        from Projects
               ),
    starts as (
        select start_date, row_number() over(order by start_date) as rank
        from dates
        where 1=1
            and start_date != prev_end
                ),
    ends as (
        select end_date, row_number() over(order by start_date) as rank
        from dates
        where 1=1
            and end_date != next_start
                )
select ss.start_date, es.end_date
from starts ss inner join ends es
on ss.rank = es.rank
order by es.end_date - ss.start_date, ss.start_date

 

문제는 프로젝트의 시작 날짜와 끝난 날짜가 저장돼 있는 'Projects' 테이블에서 

 

프로젝트를 분류하고, 분류된 프로젝트의 시작 날짜와 최종적으로 끝난 날짜를 찾는 것입니다.

 

약간 무슨 말이신지 모르겠죠?

 

 

 

예를 들면, ID가 1인 프로젝트의 시작 날짜가 22년 2월 21일이고, 끝난 날짜가 22년 2월 22일입니다.

 

ID가 2인 프로젝트의 시작 날짜가 22년 2월 22일이고, 끝난 날짜가 22년 2월 23일이라면,

 

ID 1과 2는 동일 프로젝트 입니다.

 

 

 

끝난 날짜가 연속적인 프로젝트는 전부 동일하기 때문에 

 

비연속적인 프로젝트를 찾고,

 

각 프로젝트의 날짜를 출력해 줘야 합니다.

 

 

 

이 때 사용된 함수는

• NVL : 데이터가 NULL일 때 반환할 값을 설정합니다.
• LAG : 기준이 되는 컬럼의 이전 행의 값을 반환합니다.
• LEAD : 기준이 되는 컬럼의 다음 행의 값을 반환합니다.
• INTERVAL 'n' day : n만큼 일수를 더해주거나 빼줍니다.
• ROW_NUMBER() OVER() : OVER절 기준으로 정렬했을 때, 정렬된 순서대로 숫자를 부여합니다.

 

예시를 만들어볼게요.

TASK_ID	START_DATE	LEAD(START_DATE)	END_DATE	LAG(END_DATE)
1	2024-02-21	2024-02-22	2024-02-22	2024-02-20 (원래 NULL)
2	2024-02-22	2024-02-25	2024-02-23	2024-02-22
3	2024-02-25	2024-02-26	2024-02-26	2024-02-23
4	2024-02-26	2024-02-27	2024-02-27	2024-02-26
5	2024-02-27	2024-03-03	2024-02-28	2024-02-27
6	2024-03-03	2024-03-05 (원래 NULL)	2024-03-04	2024-02-28

 

문제에서 주어진 조건에 따라, TASK_ID 1~2, 3~5, 6은 동일 프로젝트 입니다.

 

각 프로젝트 별 시작 날짜와 끝난 날짜는 표의 빨간색으로 표시된 부분이 출력되면 되는 것입니다.

 

 

 

시작 날짜만 출력하기 위해, WHERE 절 조건에 START_DATE != LAG(END_DATE) 인 데이터만 추출하고,

 

각 행마다 번호를 붙여줍니다.

 

그리고 끝난 날짜만 출력하기 위해, WHERE 절 조건에 END_DATE != LEAD(START_DATE) 인 데이터만 추출하고,

 

각 행마다 번호를 붙여줍니다.

 

 

 

그리고 각 서브 쿼리의 ROW_NUMBER를 기준으로 JOIN을 해주면 문제를 해결할 수 있게 됩니다.

---

항상 코드나 쿼리문을 짤 때

 

내가 생각하는 것보다 더 좋은 방법이 있을 것 같은데?

 

하는 생각이 들어서 시간이 오래 걸리는 것이 좀 힘드네요..ㅎㅎㅎㅎ

 

(누가 정답을 알려줘!!!)

 

연습만이 살 길이죠!!

 

 

 

그럼 저는 다음 포스팅으로 돌아오겠습니다.

 

살아있기를 기도하며ㅋㅋㅋㅋㅋ

 

뿅~