[MYSQL] SQL Project Planning / 2가지 풀이

>> 문제

=> Project table이 주어지고, task_id, start_date, end_date가 주어짐

=> start_date와 end_date의 차이는 무조건 1로 고정되어 있음

=> 만약 end_date가 연속된다면 => 이건 같은 프로젝트임

=> 각 프로젝트의 start_date와 end_date를 구하시오

=> 프로젝트의 일수별로 오름차순 -> start_date별로 오름차순

 

>> input 예시

 

어려웠던 점

=> 아니 연속되는 값을 어떻게 판별하는데?

=> 같은 행에서 왔다갔다하며 조회하는 게 가능? => 가능했음

 

---

>>풀이1. 분석함수 없이 기준선 만들기

 

결국 end_date가 연속된 데이터들끼리 묶어줄 무언가가 필요함

=> end_date가 연속된다면, 각 end_date 별 차이는 1임

=> 그럼 1씩 증가하는 무언가를 빼준다면, 연속된 데이터는 같은 숫자를 반환하지 않을까?

 

 

ex1)

1 - 2 - 3 - 5 - 6 - 9 에서 각각 1씩 증가하는 데이터인

1 - 2 - 3 - 4 - 5 - 6 을 빼보자

0 - 0 - 0 - 1 - 1 - 3 

 

=> 그럼 프로젝트 이름을 0, 1, 3으로 정해서 묶을 수 있음

 

 

 

=> 그렇게 만든 테이블 

=> end_date에서 1씩 증가하는 값인 row_number를 빼주자!

 

=>결과

본 연산을 조금 예를 들면

첫번째 행은 2015-10-02 - 1(행넘버) = 20151001

두번째 행은 2015-10-03 - 2(행넘버) = 20151001

=> end_date가 연속되는 것에 대해서는 같은 숫자를 생성

 

그러나, 그렇지 않은 경우를 보면

4번째 행 : 2015-10-05 -4 = 20151001

5번째 행 : 2015-10-12 -5 = 20151007

=> end_date가 연속되지 않으면 다른 숫자를 생성

 

이렇게 groupby로 묶을 수 있는 열이 완성되었음

이제 이걸 from 절 서브쿼리로 넣으면

각 프로젝트 별로 분류가 가능해짐

 

각 프로젝트의 시작하는 날짜 : min(start_date)

각 프로젝트의 끝나는 날짜 : max(end_date)가 됨

 

 

=> 최종 sql문

 

 

 

 

 

---

 

 

 

>>풀이2. 분석함수 Lag 사용하기

 

# Lead /Lag 함수 정리

LAG (expr [,offset] [,default]) OVER ([partition_by_clause] order_by_clause)
LEAD(expr [,offset] [,default]) OVER([partition_by_clause] order_by_clause)

LAG 함수 : 이전 행의 값을 리턴
LEAD 함수 : 다음 행의 값을 리턴

expr : 대상 컬럼명
offset : 값을 가져올 행의 위치 기본값은 1, 생략가능
default : 값이 없을 경우 기본값, 생략가능
partition_by_clause : 그룹 컬럼명, 생략가능
order_by_clause : 정렬 컬럼명, 필수

예를 들어 date행의 이전/다음 행을 조회하려면
LAG (date) over(order by date) as date_prev => 이전 행 조회
LEAD(date) over(order by date) as date_next => 다음 행 조회

 

 

=> 풀이2는 총 3단계로 나뉘어져 있음

step1. 각 날짜가 연속되는지 판단하는 flag 생성 (연속되면 0, 연속안되면 1)

step2. 앞서 만든 flag를 바탕으로 프로젝트 구분자 생성

step3. 문제 조건에 알맞은 쿼리 생성

 

---

step1. 각 날짜가 연속되는지 판단하는 flag 생성 (연속되면 0, 연속안되면 1)

 

# 코드 설명
LAG(end_date) over (order by end_date) = end date-1
=> end_date이전 행이 지금 end_date랑 차이가 1밖에 안난다면 = 연속된다면
=> 0으로 초기화

=> 연속되지 않으면 1로 초기화

 

>>step1 결과

=> 1을 기점으로 각 프로젝트의 시작점을 알려줌

---

step2. 앞서 만든 flag를 바탕으로 프로젝트 구분자 생성

 

step1에서 만든 테이블을 바탕으로 각 프로젝트를 구분하는 구분자가 필요 => 구분자를 바탕으로 group by할 예정

=> 프로젝트 시작점이 1이니 더해내려가면 되지 않을까?

 

>>구현 모습

 

>>결과

=> flag를 더해내려오면서 연속된 행은 0이기 때문에 각 프로젝트 구분자가 생성

=> 즉 행1-4까지는 프로젝트 1

=> 행 5.6은 프로젝트2

 

---

step3. 문제 조건에 알맞은 쿼리 생성

- step2에서 만든 테이블을 from 서브쿼리로 넣음

- 프로젝트 구분자를 바탕으로 groupby

- 뒤는 풀이 1과 같음

 

=> 구현모습

 

>>결과

 

 

#풀이를 참고한 사이트 => 훨씬 자세히 설명되어있음

https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?mod=document&uid=236593 

퀴즈로 배우는 SQL - 연속된 날짜를 하나의 그룹으로 표현해보자