이번 퀴즈로 배워보는 SQL 시간에는 분석함수를 사용할 수 없는 상황에서 연결된 기간을 하나로 합쳐 보여주는 문제를 풀어본다.
진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.
[리스트 1]과 [표 1]은 사원의 근무이력 정보를 보여주는 리스트와 테이블입니다. 보는 것처럼 성명(NM), 근무처(LOC), 업무(JOB)와 근무 시작일(SDT), 근무 종료일(EDT) 항목이 있습니다.
- [리스트 1] 근무이력 테이블
-
CREATE TABLE t ( id NUMBER(8) , nm VARCHAR2(8) , loc VARCHAR2(8) , job VARCHAR2(8) , sdt DATE , edt DATE ); INSERT INTO t VALUES(1, '철수', '강남', '판매' , TO_DATE('110101', 'yymmdd'), TO_DATE('110630', 'yymmdd')); INSERT INTO t VALUES(1, '철수', '강남', '판매' , TO_DATE('110701', 'yymmdd'), TO_DATE('111231', 'yymmdd')); INSERT INTO t VALUES(1, '철수', '강동', '영업' , TO_DATE('120101', 'yymmdd'), TO_DATE('130101', 'yymmdd')); INSERT INTO t VALUES(2, '길동', '강남', '판매' , TO_DATE('110101', 'yymmdd'), TO_DATE('110630', 'yymmdd')); INSERT INTO t VALUES(2, '길동', '강동', '영업' , TO_DATE('110701', 'yymmdd'), TO_DATE('111231', 'yymmdd')); INSERT INTO t VALUES(2, '길동', '강남', '판매' , TO_DATE('120101', 'yymmdd'), TO_DATE('130101', 'yymmdd')); COMMIT; SELECT * FROM t ORDER BY id, sdt;
- [표 1] 근무이력 테이블
-
ID NM LOC JOB SDT EDT ---- ------ ------ ------ ---------- ---------- 1 철수 강남 판매 2011-01-01 2011-06-30 1 철수 강남 판매 2011-07-01 2011-12-31 1 철수 강동 영업 2012-01-01 2013-01-01 2 길동 강남 판매 2011-01-01 2011-06-30 2 길동 강동 영업 2011-07-01 2011-12-31 2 길동 강남 판매 2012-01-01 2013-01-01
문제
[표 1]의 각 사원별 근무처, 업무별 시작일과 종료일 기간의 값을 이용해 같은 근무처와 같은 업무로 기간이 서로 연결되는 이력을 다시 하나의 기간으로 연결해 [표 2]와 같은 형태의 결과 테이블로 도출하는 결과 쿼리를 작성하세요. 단, 분석함수를 사용할 수 없습니다.
- [표 2] 결과 테이블
-
ID NM LOC JOB SDT EDT ----- ------ ------ ------ ---------- ---------- 1 철수 강남 판매 2011-01-01 2011-12-31 1 철수 강동 영업 2012-01-01 2013-01-01 2 길동 강남 판매 2011-01-01 2011-06-30 2 길동 강동 영업 2011-07-01 2011-12-31 2 길동 강남 판매 2012-01-01 2013-01-01
문제설명
이번 시간에는 기간이 서로 연결되는 근무이력을 하나로 합해 보여주는 문제를 풀어보도록 하겠습니다.
[표 1]에 있는 철수의 1행과 2행 자료를 보면 근무처와 직무가 강남과 판매로 동일합니다. 그리고 1행의 종료일인 06/30의 다음 날인 07/01이 바로 2행의 시작일이므로 기간이 서로 연결됩니다.
이 경우 두 개 행의 기간인 01/01 ~ 06/30과 07/01 ~ 12/31을 <표 2>의 첫 번째 행처럼 하나로 합친 기간 즉, 01/01 ~ 12/31로 보이게 하는 것이 이번 문제입니다.
그리고 철수의 3번 행은 2번 행과 기간이 서로 연결되지만 근무처와 직무가 다르기 때문에 연결되지 않습니다. 길동의 경우엔 강남과 판매가 두 번 나오지만 서로 일자가 떨어져 있으므로 합쳐지지 않고 각각 출력됩니다.
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트 2] 정답 리스트
SELECT id, nm, loc, job
, MIN(sdt) AS sdt
, MAX(edt) AS edt
FROM (SELECT a.id, a.nm, a.loc, a.job, a.sdt, a.edt
, SUM(b.flag) AS grp
FROM t a
INNER JOIN
(SELECT a.id, a.sdt
, CASE WHEN b.id IS NULL
THEN 1 ELSE 0 END AS flag
FROM t a
LEFT JOIN t b
ON a.id = b.id
AND a.loc = b.loc
AND a.job = b.job
AND a.sdt-1 = b.edt
) b
ON a.id = b.id
AND a.sdt >= b.sdt
GROUP BY a.id, a.nm, a.loc, a.job, a.sdt, a.edt
)
GROUP BY id, nm, loc, job, grp
ORDER BY id, sdt;
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
이번 문제는 기간이 서로 연결되는 근무이력을 하나로 합해 보여주는 문제입니다. 그런데 다소 난감한 조건 하나가 있습니다. 분석함수를 사용할 수 있다는 것입니다.
사실 우리는 항상 분석함수가 지원되는 데이터베이스 사용을 보장받을 수 없습니다. 분석함수가 지원되지 않는 DBMS도 있고 여러 가지 여건에 따라 사용하지 못하는 경우도 발생합니다.
이런 경우에 기본적인 조인만으로 문제를 해결할 수 있는 능력을 기른다는 취지에서 이번 문제를 출제하게 됐습니다. 우선 정답 쿼리를 살펴보기 전 분석함수를 사용해 문제를 풀어보도록 하겠습니다.
- [리스트 3] 분석함수를 이용한 정답 리스트
-
SELECT id, nm, loc, job , MIN(sdt) sdt , MAX(edt) edt FROM (SELECT id, nm, loc, job, sdt, edt , SUM(flag) OVER( PARTITION BY id ORDER BY sdt, edt) grp FROM (SELECT id, nm, loc, job, sdt, edt , CASE WHEN sdt - 1 = LAG(edt) OVER( PARTITION BY id, loc, job ORDER BY sdt, edt) THEN 0 ELSE 1 END flag FROM t ) ) GROUP BY id, nm, loc, job, grp ORDER BY id, sdt;
[리스트 3]의 쿼리를 이용해 [표 3]과 같은 결과를 얻었습니다. [리스트 3]의 쿼리의 가장 안쪽 인라인뷰만 실행한 결과는 [표 3]과 같습니다.
- [표 3] 분석함수를 이용한 정답 리스트
-
ID NM LOC JOB SDT EDT ----- ------ ------ ------ ---------- ---------- 1 철수 강남 판매 2011-01-01 2011-12-31 1 철수 강동 영업 2012-01-01 2013-01-01 2 길동 강남 판매 2011-01-01 2011-06-30 2 길동 강동 영업 2011-07-01 2011-12-31 2 길동 강남 판매 2012-01-01 2013-01-01
분석함수 중 바로 직전 행의 결과를 참조할 수 있는 함수가 바로 LAG 함수입니다.
LAG(edt) OVER(PARTITION BY id, loc, job ORDER BY sdt, edt)
이 LAG 함수를 이용해 사원별, 근무처, 업무를 그룹기준으로 삼고, 시작일과 종료일을 정렬기준으로 해 직전행의 종료일을 가져온 다음 이 값이 시작일의 전일인 경우라면 기간이 연결되는 것입니다. 그리고 CASE 문을 이용해 기간이 연결되는 경우엔 0을, 연결되지 않는 경우엔 1을 표현하도록 한 값이 바로 FLAG에 해당됩니다.
첫 번째 행은 당연히 직전값이 없으므로 1이 나오고 두 번째 행은 이전 행의 종료일인 06/30이 시작일인 07/01로 연결되므로 FLAG 값이 0이 나왔습니다. 다시 말해 이전 행의 기간과 연결된다는 의미입니다.
이제 기간 연결여부는 확인이 됐습니다, 이제 이 결과를 이용해 연결된 자료끼리 그룹으로 묶어주어야 할 텐데요. 어떻게 해야 할까요? FLAG 0으로 나온 행을 앞에 있는 FLAG 1의 행에 포함시키면 될 것 같습니다.
- [리스트 4] 기간 연결여부를 이용한 그룹핑
-
SELECT id, nm, loc, job, sdt, edt , flag , SUM(flag) OVER( PARTITION BY id ORDER BY sdt, edt) grp FROM (SELECT id, nm, loc, job, sdt, edt , CASE WHEN sdt - 1 = LAG(edt) OVER( PARTITION BY id, loc, job ORDER BY sdt, edt) THEN 0 ELSE 1 END flag FROM t ) ORDER BY id, sdt;
[리스트 4]의 쿼리를 이용해 [표 4]라는 결과를 얻었습니다.
- [표 4] 기간 연결여부를 이용한 그룹핑
-
ID NM LOC JOB SDT EDT FLAG GRP ----- ------ ------ ------ ---------- ---------- ---------- ---------- 1 철수 강남 판매 2011-01-01 2011-06-30 1 1 1 철수 강남 판매 2011-07-01 2011-12-31 0 1 1 철수 강동 영업 2012-01-01 2013-01-01 1 2 2 길동 강남 판매 2011-01-01 2011-06-30 1 1 2 길동 강동 영업 2011-07-01 2011-12-31 1 2 2 길동 강남 판매 2012-01-01 2013-01-01 1 3
[리스트 4]를 살펴보면 [리스트 3]의 FLAG를 이용해 GRP 값을 만들어 냈음을 알 수 있습니다.
SUM(flag) OVER(PARTITION BY id ORDER BY sdt, edt) grp
분석함수를 이용해 FLAG 값을 순차적으로 누적 합산한 것입니다. 그 결과는 [표 4]의 GRP 값입니다. 이 GRP 값의 의미를 좀더 살펴보면, FLAG 1이 나오는 행은 앞에 연결되지 않고 새롭게 시작한다는 의미이고 FLAG 0이 나오는 행은 앞에 있는 행과 연결되는 행을 의미합니다.
즉, [표 4]의 FLAG 값인 1, 0이 하나의 기간으로 합쳐져 서로 연결된 기간이라는 의미인 셈이며, 이 FLAG 값을 누적 합산해 그룹핑의 기준이 되는 GRP 값을 만들어낸 것입니다.
첫 번째 행과 두 번째 행이 동일한 GRP 값이 나왔으므로 이 GRP를 이용해 GROUP BY하면 [리스트 3]의 정답이 완성됩니다. 이 때, 합쳐진 기간들의 시작일과 종료일은 각각 MIN과 MAX를 이용해 구할 수 있습니다.
, MIN(sdt) sdt, MAX(edt) edt
오라클의 분석함수를 사용해 정답을 완성해 봤는데, 이제 분석함수 없이 여러 DBMS에 범용적으로 적용할 수 있도록 문제를 풀어볼까요?
- [리스트 5] Self Join과 Outer Join을 이용해 기간 연결여부 구하기
-
SELECT a.id, a.loc, a.job, a.sdt, a.edt , b.id, b.edt , CASE WHEN b.id IS NULL THEN 1 ELSE 0 END AS flag FROM t a LEFT JOIN t b ON a.id = b.id AND a.loc = b.loc AND a.job = b.job AND a.sdt-1 = b.edt ORDER BY a.id, a.sdt ;
[리스트 5]의 쿼리를 이용해 [표 5]라는 결과를 얻었습니다.
범용적으로 적용하기 위해 ANSI 조인 구문을 사용했습니다. 동일 테이블을 두 번 읽어 Self Join 했습니다. 즉, a를 기준으로 b를 Outer Join 했습니다. 조인 조건은 사원ID, 근무처, 업무가 동일하고, a의 시작일 하루 전날이 b의 종료일인 경우를 찾습니다.
- [표 5] Self Join과 Outer Join을 이용해 기간 연결여부 구하기
-
ID LOC JOB SDT EDT ID EDT FLAG ------- ------ ------ ---------- ---------- ---------- ---------- ---------- 1 강남 판매 2011-01-01 2011-06-30 1 1 강남 판매 2011-07-01 2011-12-31 1 2011-06-30 0 1 강동 영업 2012-01-01 2013-01-01 1 2 강남 판매 2011-01-01 2011-06-30 1 2 강동 영업 2011-07-01 2011-12-31 1 2 강남 판매 2012-01-01 2013-01-01 1
이렇게 되면 a의 모든 자료가 나오고 b의 자료는 조인 조건을 만족한 자료만 나오게 됩니다. 조인 조건을 만족하지 못하면 Null이 나옵니다. 이러한 성질을 이용해 Null일 때와 아닐 때로 구별해 FLAG 값을 지정해 줍니다. 그렇게 연결여부인 FLAG를 구했습니다.
이번에는 연결여부를 이용해 그룹핑 기준인 GRP를 구해보도록 할까요? 분석함수에서는 단순 Order By 구문으로 누적함수를 쉽게 구할 수 있지만 분석함수 없이 누적합계를 어떻게 구할까요?
- [리스트 6] 기간 연결여부를 이용한 그룹핑
-
SELECT a.id, a.nm, a.loc, a.job, a.sdt, a.edt , b.flag , SUM(b.flag) AS grp FROM t a INNER JOIN (SELECT a.id, a.sdt , CASE WHEN b.nm IS NULL THEN 1 ELSE 0 END AS flag FROM t a LEFT JOIN t b ON a.id = b.id AND a.loc = b.loc AND a.job = b.job AND a.sdt-1 = b.edt ) b ON a.id = b.id AND a.sdt >= b.sdt GROUP BY a.id, a.nm, a.loc, a.job, a.sdt, a.edt, b.flag ORDER BY a.id, a.sdt ;
[리스트 6]의 쿼리를 이용해 [표 4]와 같은 결과를 얻었습니다.
[리스트 6]에서는 [리스트 5]의 결과를 인라인뷰로 감싸고 다시 테이블과 조인합니다. 이번에는 조인 조건이 사원ID와 시작일입니다. 이는 누적합계를 구하기 위함인 데요. 동일한 사원에 대해 시작일이 작거나 같은 행을 조인합니다.
그 결과 a의 1번 행에 대해 b는 1번 행이 조인되고, a의 2번 행에 대해 b는 1번과 2번 행이 조인되며, a의 3번 행에 대해 b는 1번과 2번 그리고 3번 행이 조인됩니다.
이렇게 조인된 결과를 다시 a의 컬럼들을 기준으로 GROUP BY해 b의 FLAG를 SUM하는 것입니다. 이렇게 하면 마치 SUM() OVER() 구문을 이용해 누적합계를 구한 것과 동일한 결과를 얻을 수 있습니다.
이제 마지막으로 이렇게 구한 그룹핑 기준를 이용해 GROUP BY하면 [리스트 2]의 정답을 완성할 수 있습니다.
이번 퀴즈로 배우는 SQL 시간에는 연결된 기간을 하나로 합쳐 보여주는 문제를 풀어봤는데요. 이번 문제를 풀기 위한 여러 가지 팁들이 있었는데, 정리해 보겠습니다.
1. 기간 연결여부 구하기
분석함수의 LAG() OVER()의 기능을 구현하기 위해 동일 테이블을 두 번 읽는 Self Join을 시도했으며, Outer Join을 이용해 연결여부를 구했습니다.
2. 연결여부를 이용해 그룹핑 기준 구하기
분석함수인 SUM() OVER() 기능을 구현하기 위해 마찬가지로 동일 테이블을 다시 조인했으며 시작일을 이용한 Non-Equal-Join (a.sdt >= b.sdt)으로 시작일 기준 이전 행들을 조인한 다음 기준 테이블의 모든 항목을 기준으로 GROUP BY한 뒤 FLAG 값을 SUM해 원하는 기능을 구현했습니다.
그룹핑
마지막으로 위에서 구한 GRP를 기준으로 GROUP BY한 뒤 MIN과 MAX 함수를 이용해 최종 결과를 도출했습니다.
우리는 분석함수 없이 문제를 해결하고자 동일 테이블을 세 번이나 읽는 수고를 했고, Outer Join과 Non-Equal-Join 등의 수고를 더했으며, Group By를 한 번 더 하는 수고를 또 더했습니다. 여러 가지로 비효율이 많은 쿼리가 되고 말았습니다.
하지만 분석함수를 사용할 수 없는 상황이라면 이렇게 해결해야 함을 알게 됐다는 것이 좋았습니다. 물론 분석함수를 이용할 수 있는 상황이라면 더할 나위 없이 좋겠지요. 분석함수의 편리함과 효율성을 새삼 느끼게 해주는 문제였습니다.
- 강좌 URL : http://www.gurubee.net/lecture/2855
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.