이번 퀴즈로 배워보는 SQL 시간에는 기간 병합 검색 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.
진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.
[리스트 1]과 [표 1]은 시작일과 종료일 정보를 나타내는 리스트와 테이블입니다. 보는 것처럼 식별키(ID)와 시작일(SDT), 종료일(EDT) 항목이 있습니다.
[리스트 1]과 [표 1]은 시작일과 종료일 기간에 따른 금액 정보를 나타낸 리스트와 테이블입니다. 보는 것처럼 식별키(ID)와 시작일(SDT) 그리고 종료일(EDT)에 따른 금액(AMT)과 수량(CNT) 항목이 있음을 알 수 있습니다.
- [리스트 1] 기간 리스트
-
CREATE TABLE t AS SELECT 1 id, '20120901' sdt, '20130531' edt, 6250 amt, 25 cnt FROM dual UNION ALL SELECT 2, '20130401', '20130831', 5500, 20 FROM dual UNION ALL SELECT 3, '20130501', '20140430', 5000, 15 FROM dual UNION ALL SELECT 4, '20150101', '20151231', 1000, 10 FROM dual; SELECT * FROM t;
- [표 1] 기간 테이블
문제
[표 1]의 각 ID별 시작일과 종료일 기간에 해당하는 값을 이용해, 기간이 서로 겹치는 구간들을 하나의 기간으로 연결하는 [표 2]와 같은 형태의 결과 테이블을 도출하는 결과 쿼리를 작성하세요.
- [표 2] 결과 테이블
-
SDT EDT ---------------- ------------ 20120901 20140430 20150101 20151231
문제설명
지난 강좌에는 배웠던 문제가 기간이 서로 겹치는 구간을 따로 분리해 내고 분리된 각 구간의 금액 및 수량을 합산해 보여주는 것이었다면, 이번 시간에는 기간이 서로 겹치는 구간을 하나로 병합하는 것을 보여주는 문제입니다.
문제를 쉽게 이해할 수 있도록 도식화한 [그림 1]을 살펴보겠습니다.
- [그림 1] 기간 병합
[그림 1]의 기간 병합 자료를 보면 위에서부터 ID 1번부터 4번까지의 기간 정보가 시작일부터 종료일까지 선분으로 표시됩니다.
이때 4개의 기간들은 서로 겹치는 구간이 존재하게 되는데요. 이렇게 서로 겹치는 구간들을 하나의 연결된 기간으로 병합합니다. 이렇게 병합된 기간을 맨 아래쪽에 있는 A, B, C라는 3개의 구간으로 표현했습니다.
각 구간에 대해 자세히 살펴보죠. A 구간은 서로 연결된 1번, 2번, 3번 구간이 연결됨으로써 형성된 구간입니다. 1번 구간과 2번 구간에는 서로 겹친 구간이 있고, 1번 구간과 3번 구간에도 서로 겹친 구간이 있습니다.
2번 구간과 3번 구간은 서로 직접적으로 겹치지는 않지만, 1번 구간을 매개로 서로 연결되면서 결국 1, 2, 3 번 구간이 하나의 구간으로 연결되는 구조입니다.
B 구간은 아무 것도 없는 구간이 되며, C 구간은 4번 구간이 단독으로 형성된 구간입니다. 이번 문제는 <그림 1>과 같이 서로 겹치는 구간을 하나로 통합해 [표 2]처럼 표시하는 문제입니다.
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트 2] 정답 리스트
SELECT MIN(sdt) sdt
, MAX(edt) edt
FROM (
SELECT sdt
, edt
, SUM(flag) OVER(ORDER BY sdt, edt) grp
FROM (
SELECT sdt
, edt
, CASE WHEN MAX(edt) OVER(ORDER BY sdt, edt
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) >= sdt
THEN 0 ELSE 1 END flag
FROM t
)
)
GROUP BY grp
ORDER BY grp
;
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
이번 시간에는 기간이 서로 겹치는 구간을 하나로 병합해 보여주는 문제입니다.
지난 강좌에는 기간이 서로 겹치는 구간을 따로 분리해 보여주는 문제를 풀어 봤는데요. 지난 시간 풀어본 문제와 개념은 서로 비슷하면서 결과는 분할과 통합으로 서로 반대되는 결과로 도출되는 문제입니다.
우선 정답 쿼리를 살펴보기 이전에, 제가 이 문제를 처음 풀면서 시행착오를 겪었던 부분을 함께 공유해보도록 하겠습니다. 이 문제를 풀기 위해서는 기간과 기간이 서로 겹치는지를 파악해야 하는데요. 그래서 기간과 기간이 연결된다는 의미가 기간의 시작일이 바로 앞 기간의 종료일보다 작은 경우가 된다고 생각을 했습니다.
그래서 바로 앞단의 종료일을 확인하기 위해 LAG 함수를 사용하면 될 것이라 판단했죠.
- [리스트 3] LAG 함수를 이용한 연결여부 확인
-
SELECT id , sdt , edt , LAG(edt) OVER(ORDER BY sdt, edt) lag_edt , CASE WHEN LAG(edt) OVER(ORDER BY sdt, edt) >= sdt THEN 0 ELSE 1 END flag FROM t ORDER BY sdt, edt ;
[리스트 3]의 쿼리를 이용해 [표 3]과 같은 결과를 얻었습니다.
- [표 3] LAG 함수를 이용한 연결여부 확인
-
ID SDT EDT LAG_EDT FLAG ------- ---------------- ---------------- ---------------- ---------- 1 20120901 20130531 1 2 20130401 20130831 20130531 0 3 20130501 20140430 20130831 0 4 20150101 20151231 20140430 1
분석함수 중 바로 직전 행의 결과를 참조할 수 있는 함수가 바로 LAG 함수입니다. 이 LAG 함수를 이용해 시작일과 종료일을 정렬기준으로 고려해 직전행의 종료일을 가져온 값이 바로 LAG_EDT에 해당됩니다.
따라서 LAG_EDT 값이 시작일보다 큰 경우가 기간이 연결되는 경우겠죠. CASE 문을 이용해 기간이 겹치는 경우엔 0을 겹치지 않는 경우엔 1을 표현하도록 한 값이 바로 FLAG에 해당됩니다.
첫 번째 행은 당연히 직전 값이 없으므로 1이 나오고요, 두 번째 행은 이전 행의 종료일인 20130831보다 시작일인 20130401이 더 작으므로 FLAG 값이 0이 나왔습니다. 이전 행의 기간과 연결된다는 의미입니다.
세 번째 행은 이전 행의 종료일인 20130430보다 시작일인 20130531이 더 크므로 FLAG 값이 1이 나왔습니다. 즉, 이전 행의 기간과 연결되지 않는다는 의미입니다.
그런데 2, 3번 구간이 서로 연결되는 구조였는데요. 그렇다면 세 번째 행의 FLAG도 0이 나와야 맞는데 1이 나왔네요. 결과적으로 잘못된 결과가 도출됐습니다. 고로 문제를 풀기 전에 세웠던 가설이 틀렸다는 의미입니다.
즉, 바로 앞단의 종료일이 시작일보다 큰 경우가 아니더라도 서로 연결될 수 있다는 것을 알 수 있게 됐습니다. 다시 한 번 [그림 1]을 보면서 설명 드리겠습니다. 1번 구간은 2번 구간과 연결이 됩니다. 그러나 2번은 3번과 연결이 되지 않습니다. 2번과 3번은 서로 단절이 되죠. 그래서 [표 3]의 FLAG 값이 1이 나온 것입니다.
그런데 다시 보면 3번 구간은 2번과는 단절됐지만 1번과는 연결이 됩니다. 그래서 1, 2, 3번이 서로 연결되는 구조인 것이죠.
그렇다면 3번을 기준으로 보면 바로 앞의 2번과의 연결만 생각해서는 답이 안 나오며, 바로 앞의 구간뿐만 아니라 이전의 구간까지도 모두 비교를 해봐야 한다는 결론을 낼 수 있을 것 같습니다. 바로 앞에 있는 자료뿐 아니라 그 앞에 있던 모든 자료를 참조하려면 LAG 함수로는 안 됩니다.
앞에 있는 모든 자료의 종료일 중에 현재 행의 시작일보다 큰 자료가 하나라도 있으면 연결되는 구조로 보고, FLAG를 0으로 표현하도록 하려면 어떻게 해야 할까요?
- [리스트 4] 기간 연결여부 확인
-
SELECT id , sdt , edt , LAG(edt) OVER(ORDER BY sdt, edt) lag_edt , MAX(edt) OVER(ORDER BY sdt, edt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) max_edt , CASE WHEN MAX(edt) OVER(ORDER BY sdt, edt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) >= sdt THEN 0 ELSE 1 END flag FROM t ORDER BY sdt, edt ;
[리스트 4]의 쿼리를 이용해 [표 4]라는 결과를 얻었습니다.
- [표 4] 기간 연결여부 확인
-
ID SDT EDT LAG_EDT MAX_EDT FLAG ------ ------------- ------------ ----------- ---------- ------ 1 20120901 20130531 1 2 20130401 20130831 20130531 20130531 0 3 20130501 20140430 20130831 20130831 0 4 20150101 20151231 20140430 20140430 1
이번에는 LAG 함수 대신 MAX 함수를 이용해 봤습니다.
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
이 구문이 없다면 MAX 값을 처리하는 범위가 정렬 결과의 처음 행부터 현재 행까지가 될 것입니다. 현재 행은 제외하고 바로 이전 행까지의 자료만을 처리대상으로 한정하기 위해 추가된 구문입니다.
3번 행의 결과를 보면 LAG_EDT와 다른 MAX_EDT를 구했으며 이를 이용해 구한 FLAG 값이 우리가 원하는 0값이 나온 것을 확인하실 수 있습니다. 이제 구간 연결여부는 확인이 됐고요, 이제 이 결과를 이용해 연결된 자료끼리 그룹으로 묶어주어야 할 텐데요. 어떻게 해야 할까요?
- [리스트 5] 기간 연결여부를 이용한 그룹핑
-
SELECT id , sdt , edt , flag , SUM(flag) OVER(ORDER BY sdt, edt) grp FROM ( SELECT id , sdt , edt , CASE WHEN MAX(edt) OVER(ORDER BY sdt, edt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) >= sdt THEN 0 ELSE 1 END flag FROM t ) ;
[리스트 5]의 쿼리를 이용해 [표 5]라는 결과를 얻었습니다.
- [표 5] 기간 연결여부를 이용한 그룹핑
-
ID SDT EDT FLAG GRP ------ ---------------- ---------------- ---------- ---------- 1 20120901 20130531 1 1 2 20130401 20130831 0 1 3 20130501 20140430 0 1 4 20150101 20151231 1 2
[리스트 5]를 살펴보면 [리스트 4]의 FLAG를 이용해 GRP 값을 만들어 냈습니다.
, SUM(flag) OVER(ORDER BY sdt, edt) grp
분석함수를 이용해 FLAG 값을 순차적으로 누적 합산한 결과입니다. 그리고 그 결과는 <표 5>의 GRP 값입니다. 이 GRP 값의 의미를 살펴볼까요?
FLAG 1이 나오는 구간은 앞에 연결되는 구간이 존재하지 않는다는 의미로 새로운 구간이 시작됨을 의미하며, FLAG 0이 나오는 구간은 앞의 구간이 연결되는 구간임을 의미합니다.
그래서 [표 5]의 FLAG 값인 1, 0, 0이 하나의 구간으로 합쳐져야 하는 즉, 서로 연결된 구간이 된다는 의미이며 이 FLAG 값을 누적 합산해 그룹핑의 기준이 되는 GRP 값을 만들어낸 것입니다. 이 GRP를 이용해 마지막으로 GROUP BY한 다음 정답을 완성해 보도록 하겠습니다.
- [리스트 6] 그룹핑
-
SELECT grp , MIN(sdt) sdt , MAX(edt) edt FROM ( SELECT sdt , edt , flag , SUM(flag) OVER(ORDER BY sdt, edt) grp FROM ( SELECT sdt , edt , CASE WHEN MAX(edt) OVER(ORDER BY sdt, edt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) >= sdt THEN 0 ELSE 1 END flag FROM t ) ) GROUP BY grp ORDER BY grp ;
[리스트 6]의 쿼리를 이용해 [표 6]라는 결과를 얻었습니다.
- [표 6] 그룹핑
-
GRP SDT EDT ------- ---------------- ------------- 1 20120901 20140430 2 20150101 20151231
이번 퀴즈로 배우는 SQL 시간에는 기간 병합 검색 문제를 풀어봤는데요. 지난 시간의 기간 분할 문제에서와는 조금은 다른 접근방법으로 문제를 해결했네요.
지난 시간 풀이 방법 기억하시나요? 복습해볼까요? 풀이방법은 선분 형태의 기간 정보를 점 형태의 일자 정보로 변환한 뒤, 분석함수를 이용해 다시 선분 형태의 기간정보로 표현하는 것입니다. 마지막으로 지난 시간과 동일한 방법으로도 이번 문제를 풀어보도록 할까요?
- [리스트 7] 선분 이력·점 이력·선분 이력
-
SELECT TO_CHAR(MIN(dt) , ‘yyyymmdd’) sdt , TO_CHAR(MAX(dt) - 1, ‘yyyymmdd’) edt FROM (SELECT dt , SUM(flag) OVER(ORDER BY dt) grp FROM (SELECT dt , CASE WHEN SUM(SUM(s)) OVER(ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) > 0 THEN 0 ELSE 1 END flag FROM (SELECT DECODE(lv, 1, 1, -1) s , DECODE(lv, 1, TO_DATE(sdt, ‘yyyymmdd’) , 2, TO_DATE(edt, ‘yyyymmdd’) + 1 ) dt FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2) ) GROUP BY dt ) ) GROUP BY grp ORDER BY grp ;
[리스트 7]의 풀이 방법은 지난 시간에 이미 설명했으므로 자세한 설명은 생략하겠습니다.
- 강좌 URL : http://www.gurubee.net/lecture/2849
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
문제설명이 조금 잘못된게 있는 것 같아 댓글을 등록합니다.
2번 구간과 3번 구간은 서로 직접적으로 겹치지는 않지만, 1번 구간을 매개로 서로 연결되면서 결국 1, 2, 3 번 구간이 하나의 구간으로 연결되는 구조입니다.
라고 풀이되어있는데
20130501 ~ 20130831의 구간이 2번구간과 3번구간간에 직접적으로 겹쳐지는 구간이 아닌지요?
음..당시 왜 저렇게 잘못 작성했는지? ㅡ,.ㅡ
리스트나, 표에 나온 자료가 그림과 다르네요.
그림을 보고 해당 설명을 이해하셔야 할 것 같습니다.
그림의 예시자료로 바꾸어서 테스트 해보세요.
) 버튼을 클릭하여 작성 하시면 됩니다.