이번 퀴즈로 배워보는 SQL 시간에는 연속된 날짜를 하나의 그룹으로 표현하는 쿼리를 어떻게 작성하는지에 대해 알아본다.
지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결 한 후 정답과 해설을 참조하길 바란다.
공부를 잘 하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 잊지 말자
문제
번호와 날짜를 가진 테이블([그림1] 참조)에서 번호별 연속된 날짜를 하나의 그룹으로 묶어 [그림2]와 같이 번호, 시작일, 종료일, 일수를 보여주는 쿼리를 작성하세요.
테이블의 번호와 날짜는 중복되지 않는 유일한 값입니다.
- [그림1] 원본 테이블
- [그림2] 쿼리 결과 집합
다음 예제 테이블을 생성한 후 작성해 보세요.
CREATE TABLE T AS SELECT 100 no, '20090101' dt FROM dual UNION ALL SELECT 100, '20090102' FROM dual UNION ALL SELECT 100, '20090103' FROM dual UNION ALL SELECT 100, '20090105' FROM dual UNION ALL SELECT 100, '20090106' FROM dual UNION ALL SELECT 100, '20090109' FROM dual UNION ALL SELECT 100, '20090120' FROM dual UNION ALL SELECT 200, '20090101' FROM dual UNION ALL SELECT 200, '20090102' FROM dual UNION ALL SELECT 200, '20090103' FROM dual UNION ALL SELECT 200, '20090104' FROM dual UNION ALL SELECT 200, '20090131' FROM dual UNION ALL SELECT 200, '20090201' FROM dual ;
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트1] 정답
SELECT no
, MIN(dt) from_dt
, MAX(dt) to_dt
, COUNT(*) cnt
FROM (SELECT * FROM t ORDER BY no, dt)
GROUP BY no, TO_DATE(dt, 'yyyymmdd') - ROWNUM
ORDER BY no, from_dt
;
-- 실행결과
NO FROM_DT TO_DT CNT
---------- -------- -------- ----------
100 20090101 20090103 3
100 20090105 20090106 2
100 20090109 20090109 1
100 20090120 20090120 1
200 20090101 20090104 4
200 20090131 20090201 2
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
이 문제는 연속된 날짜를 하나로 묶어 시작일과 종료일로 표현하는 문제입니다. 물론 연속된 날짜를 하나로 묶는 것 보다 번호가 같아야 한다는 조건이 우선입니다.
여러분들도 알고 계실 것입니다. 여러 행을 하나의 행으로 묶기 위해선 그룹바이를 해야 한다는 것을. 그런데 그룹바이를 하기 위해선 그룹을 구분할 기준 값이 있어야 합니다. 하지만 원본 자료에 번호가 기준이 되긴 하지만 번호 외에는 연속된 날짜를 하나로 묶어줄 기준은 보이질 않습니다.
따라서 원본자료를 가공하여 그룹을 나눌 수 있는 기준 값을 구해내는 것이 이 문제의 핵심 포인트라고 할 수 있습니다. 자 이제부터 어떤 방법으로 문제를 풀어나가는지 차근차근 접근해 보도록 하겠습니다.
우선 앞에 제시한 정답[리스트1]에 대한 해설에 앞서 다른 형태의 또 다른 정답을 살펴 보겠습니다.
- [리스트2] 정답2
SELECT no
, MIN(dt) from_dt
, MAX(dt) to_dt
, COUNT(*) cnt
FROM ( -- 2단계 --
SELECT no, dt
, SUM(flag) OVER(PARTITION BY no ORDER BY dt) grp
FROM ( -- 1단계 --
SELECT no, dt
, DECODE(LAG(dt) OVER(PARTITION BY no ORDER BY dt)
, TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 1, 'yyyymmdd')
, 0, 1) flag
FROM t
)
)
GROUP BY no, grp
ORDER BY no, from_dt
;
-- 실행 결과
NO FROM_DT TO_DT CNT
---------- -------- -------- ----------
100 20090101 20090103 3
100 20090105 20090106 2
100 20090109 20090109 1
100 20090120 20090120 1
200 20090101 20090104 4
200 20090131 20090201 2
[리스트2]를 실행하면 [그림2]의 쿼리 결과 집합이 나오게 됩니다.
[리스트2]에 대한 설명을 먼저 하는 이유는 [정답1]의 개념을 바로 이해하는 것보다 [리스트2]의 쿼리가 단계별로 처리가 되어 있어 더 이해 하기 쉽고, [정답1]의 개념을 이해하는데 도움이 될 것 같아서입니다.
자 그럼 이제 어떻게 [리스트2]를 실행했을 때 [그림2]의 쿼리 결과 집합이 나올 수 있는지 쿼리를 단계별로 쪼개어 수행해보고, 각 단계별 쿼리 내용에 대해 자세히 알아보도록 하겠습니다.
문제에서 제시한 연속된 날짜라는 것을 구별하려면 어떻게 해야 할까요?
날짜 순서대로 나열 했을 때 바로 직전의 날짜와 비교하여 날짜 차이가 1일인 경우가 연속된 날짜가 되겠지요. 그렇다면 직전 날짜와의 비교는 어떻게 해야 할까요?
SQL에서는 집합간의 조인은 가능했지만 하나의 집합 안에서 행을 넘나들며 비교 하는 것이 불가능했습니다. 즉 직전 행과 현재 행을 비교하려면 셀프조인을 이용했어야 했습니다.
그러나 오라클 버전 8.1.6 부터는 분석함수(Analytic Function) 기능이 추가되면서 이 기능을 이용하게 되면 이러한 제약을 받지 않을 수 있게 되었습니다. 여러 가지 분석함수가 있는데 그 중 LAG 함수를 이용하면 셀프조인을 하지 않더라도 아주 손쉽게 직전 행의 값을 조회할 수 있습니다.
[리스트2] 쿼리의 1단계 쿼리만 따로 실행해 보도록 하겠습니다.
- [리스트3] 1단계
SELECT no, dt
, DECODE(LAG(dt) OVER(PARTITION BY no ORDER BY dt)
, TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 1, 'yyyymmdd')
, 0, 1) flag
FROM t
;
NO DT FLAG
---------- -------- ----------
100 20090101 1
100 20090102 0
100 20090103 0
100 20090105 1
100 20090106 0
100 20090109 1
100 20090120 1
200 20090101 1
200 20090102 0
200 20090103 0
200 20090104 0
200 20090131 1
200 20090201 0
[리스트 3] 1단계 쿼리의 실행결과의 FLAG 항목을 보면 바로 앞 날짜와 연속된 날짜인 경우 0 그렇지 않은 경우 1로 표현되는 것을 알 수 있습니다.
FLAG 값을 구한 계산식에 대해 설명을 하겠습니다. 우선 LAG 함수로 직전 값을 가져오고 날짜의 하루차이를 계산하기 위해 변환함수 TO_DATE 와 TO_CHAR가 사용하여 현재행의 날짜보다 하루 전날짜를 계산하여 비교합니다.
DECODE 문을 이용하여 두 날짜값을 비교하여 같으면 0을 다르면 1을 반환합니다. 즉, 연속인 경우엔 0, 연속이 아닌 경우엔 FLAG 값이 1이 되는 것입니다.
자료를 유심히 살펴보시면 FLAG값이 1인 행부터 다음 1이 나오기 직전행까지가 하나의 연속된 날짜임을 알 수 있습니다. 이러한 특성을 이용하여 연속된 skfWK 그룹을 구별 할 수 있는 GRP 항목의 값을 만들어 보도록 하겠습니다.
[리스트 2] 쿼리의 2단계 쿼리를 실행해 보겠습니다.
- [리스트4] 2단계
SELECT no, dt
, flag
, SUM(flag) OVER(PARTITION BY no ORDER BY dt) grp
FROM ( -- 1단계 --
SELECT no, dt
, DECODE(LAG(dt) OVER(PARTITION BY no ORDER BY dt)
, TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 1, 'yyyymmdd')
, 0, 1) flag
FROM t
)
;
NO DT FLAG GRP
---------- -------- ---------- ----------
100 20090101 1 1
100 20090102 0 1
100 20090103 0 1
100 20090105 1 2
100 20090106 0 2
100 20090109 1 3
100 20090120 1 4
200 20090101 1 1
200 20090102 0 1
200 20090103 0 1
200 20090104 0 1
200 20090131 1 2
200 20090201 0 2
[리스트4]의 실행결과를 보면 GRP 항목 값이 연속된 날짜끼리 동일하게 조회되는 것을 확인 할 수 있습니다.
[리스트3]에서 구한 FLAG 값을 순차적으로 합산해 나가면 [리스트4]의 GRP값을 구할 수 있습니다. GRP 값을 자세히 살펴보시면 연속된 날짜인 경우에 동일한 값을 나타내고 있다는 것을 알 수 있습니다.
이번 단계에서도 1단계와 마찬가지로 분석함수가 사용되었는데요.
SUM(flag) OVER(PARTITION BY no ORDER BY dt) grp
구문에 대해 간략하게 설명해 보면, no 그룹 별로 날짜로 정렬하면서 FLAG 값을 합산해 나간다는 의미입니다. 즉, 누적합계를 구하는 것입니다.
이렇게 구한 GRP 항목을 NO 와 함께 그룹바이(GROUP BY no, grp) 하게 되면 [리스트2]의 쿼리가 최종적으로 완성되어 나오게 됩니다.
연속된 날짜를 하나로 묶는데 성공했습니다. 이제 필요한 항목을 구하기 위해 그룹함수를 사용하기만 하면 되는 것입니다. 시작일은 해당 그룹 중 최소일자가 될 것이고, 종료일은 반대로 최대일자가 될 것입니다. 마지막으로 총 일수는 COUNT(*) 함수를 이용해 구할 수 있습니다.
자 이제 [리스트2]의 쿼리가 완성되었습니다. 쿼리 수행 원리를 이해 하셨나요?
[리스트2]에서는 여러 단계를 거쳐서 최종 결과를 도출해 냈는데요. 좀 더 간단하게 구할 수 있는 방법은 없을까요? [리스트1] 정답 쿼리를 분석해 보도록 하겠습니다.
[리스트2]에서는 2단계에 걸쳐 복잡하게 계산해낸 GRP 항목으로 그룹바이 했는데요. [리스트1]에서는 TO_DATE(dt, 'yyyymmdd') - ROWNUM 으로 그룹바이를 했습니다.
이 값(TO_DATE(dt, 'yyyymmdd') - ROWNUM)이 도체체 무었이길래 동일한 결과를 도출해 내게 되는 것일까요? 다음 쿼리를 통해 확인해 보도록 하겠습니다.
- [리스트5] TO_DATE(dt,'yyyymmdd') - ROWNUM 의 값 확인
SELECT no, dt
, ROWNUM rn
, TO_DATE(dt,'yyyymmdd') - ROWNUM grp
FROM (SELECT * FROM t ORDER BY no, dt)
;
-- 실행결과
NO DT RN GRP
---------- -------- ---------- --------
100 20090101 1 08/12/31
100 20090102 2 08/12/31
100 20090103 3 08/12/31
100 20090105 4 09/01/01
100 20090106 5 09/01/01
100 20090109 6 09/01/03
100 20090120 7 09/01/13
200 20090101 8 08/12/24
200 20090102 9 08/12/24
200 20090103 10 08/12/24
200 20090104 11 08/12/24
200 20090131 12 09/01/19
200 20090201 13 09/01/19
[리스트5]의 실행결과를 살펴보자.
우선 NO별로 날짜 정렬을 하고 ROWNUM을 구했습니다. 그리고 DT 컬럼의 값을 날짜타입으로 변환하여 ROWNUM 값을 뺀 결과로 GRP 값이 날짜 형태로 조회되었습니다.
결과를 자세히 살표보면 연속된 날짜의 경우 이 GRP값이 동일하게 나온 것을 확인하실 수 있습니다. [리스트5]의 첫 3개 행을 보면 DT값이 ‘20090101’, ‘20090102’, ‘20090103’ 으로 순차적으로 1일이 증가하고 있습니다. RN도 마찬가지로 순차적으로 1씩 증가합니다.
이렇게 DT 와 RN 의 값이 모두 순차적으로 증가하고 있을 때 두 값을 빼게 되면 어떤 결과가 나올까요? 날짜에서 숫자를 빼면 숫자에 해당하는 일수만큼 차감된 날짜가 나오게 됩니다. 결과를 계삭식으로 정리해 보면 다음과 같습니다.
2009.01.01 - 1 ===> 2008.12.31
2009.01.02 - 2 ===> 2008.12.31
2009.01.03 - 3 ===> 2008.12.31
동일한 값이 날짜형태로 나오게 됩니다. 그렇다면 순차적이지 않은 DT가 나올때는 그 값이 어떻게 나오게 될까요?
[리스트5] 세번째 행과 네번째 행의 결과와 비교해 보겠습니다.
2009.01.03 - 3 ===> 2008.12.31
2009.01.05 - 4 ===> 2009.01.01
DT 는 날짜 차이가 2일이 나고, RN은 항상 값의 차이가 1입니다. 여기서 DT-RN의 차감값은 동일하지 않은 값이 나오게 됩니다.
여기서 한가지 규칙을 발견하게 됩니다. 순차적으로 정렬된 날짜 값에서 ROWNUM을 뺀 값은 날짜가 연속적일 경우 같은 날짜 값이 나오게 되며, 연속적이지 않은 경우에는 다른 값이 나오게 된다는 것입니다. 바로 이 특성을 이용하여 GRP 값을 구할 수 있습니다.
물론 [리스트1] 쿼리에서 사용된 GRP 와 [리스트2] 쿼리에서 사용된 GRP 는 그값이 서로 전혀 다른 값이 나옵니다. 하나는 숫자 형태의 값이고, 다른 하나는 날짜 형태의 값입니다. 그 값이 전혀 다른데도 불구하고 최종 결과는 동일하게 나옵니다. 그 값이 어떤 값인지가 중요한 것이 아니고, 그 값이 어떤 역할을 하게 되는지가 중요한 것입니다.
결국 값은 다르지만 동일한 역할(연속된 날짜를 하나의 그룹으로 묶어주는 역할)을 하는 GRP를 만들어 냈습니다. 이 GRP를 이용하여 GROUP BY no, grp 를 하게 된다면 동일한 최종 결과를 얻을 수 있는 쿼리 [리스트1]이 완성됩니다.
[리스트1]쿼리에서는 날짜 순서대로 ROWNUM을 구하기 위해 인라인뷰 안에서 정렬작업이 선행되긴 했지만, [리스트2]에 비해 훨씬 간결한 쿼리가 완성이 되었습니다.
[리스트2]에서는 분석함수를 이용해 GRP를 구했습니다. 분석함수는 SELECT절에서만 사용이 가능하고 다른 분석함수나 그룹함수와 중첩하여 사용이 불가능합니다. 이런 특징으로 인해 한번에 결과를 도출하지 못하고 여러 단계에 걸쳐서 쿼리를 작성해야 했습니다. 하지만 [리스트1]의 경우엔 분석함수가 전혀 사용되지 않아 인라인뷰에서 GRP 따로 구하고 밖에서 그룹바이 하지 않고, GRP를 구하는 계산식을 바로 그룹바이절에서 사용 할 수 있어 쿼리가 더욱 간결해 졌습니다.
이번 퀴즈에선 연속된 날짜를 하나의 그룹으로 묶기 위한 과정이 소개되었습니다.
[리스트1]과 [리스트2]의 두 개의 쿼리를 살펴보았는데요. 독자 분들께서는 모두 이해가 가셨나요? 이번 퀴즈를 통해 연속된 날짜를 하나의 그룹으로 묶는 방법을 자신의 것으로 만든다면 이와 유사한 상황에서 당황하지 않고 자신 있게 문제를 해결하실 수 있을 것입니다.
- 강좌 URL : http://www.gurubee.net/lecture/2194
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
감사합니다.ㅎㅎ
감사합니다.^^
좋은글 출처를 표시하고 블로그에 담아갑니다. ^^
요문제는 드뎌 오늘 이해했네요..ㅠㅠ 나머지는 하루에 한개씩 처리하다가 요곤 걍 남겨두고 다시 풀었거든요.
이제서야 이해가.. ㅠㅠ 부끄럽네요 ㅎ
감사합니다. ^^
정말 유용한정보네요 감사합니다
와... GROUP BY no, TO_DATE(dt, 'yyyymmdd') - ROWNUM 로 구하실줄은
생각지도 못했습니다. 대단히 흥미로운 퀴즈이고 정답이네요.
감사합니다.
리스트2의 1단계에서 못나가고 그만 정답을 ㅋㅋ 와 정말 잼있어요^^
감사합니다.^^
감사합니다.
SELECT no, dt
, DECODE(LAG(dt) OVER(PARTITION BY no ORDER BY dt)
, TO_CHAR(TO_DATE(dt, 'yyyymmdd') - 1, 'yyyymmdd')
, 0, 1) flag
요부분을 MSSQL을 사용하면 어떻게 되나요? CASE WHEN THEN 과 CONVERT를 사용해야 할 것 같은데, 잘 안됩니다.
정답 1에서 ROWNUM 은 ROW_NUMBER () OVER로 변경하는 것인가요? 저는 에러메세지가 뜹니다.
Windowed functions can only appear in the SELECT or ORDER BY clauses
-- MSSQL 용 정답1 -- SELECT no , MIN(dt) from_dt , MAX(dt) to_dt , COUNT(*) cnt FROM (SELECT no, dt , CAST(dt AS DATE) dt1 , ROW_NUMBER() OVER(ORDER BY no, dt) rn FROM t ) a GROUP BY no, DATEADD(day, -rn, dt1) ORDER BY no, from_dt ; -- MSSQL 용 정답2 -- SELECT no , MIN(dt) from_dt , MAX(dt) to_dt , COUNT(*) cnt FROM (SELECT no, dt , SUM(flag) OVER(PARTITION BY no ORDER BY dt) grp FROM (SELECT no, dt , CASE LAG(dt) OVER(PARTITION BY no ORDER BY dt) WHEN CONVERT(VARCHAR, DATEADD(d, -1, dt), 112) THEN 0 ELSE 1 END flag FROM t ) a ) a GROUP BY no, grp ORDER BY no, from_dt ;
WITH T (NO , DT ) AS (
SELECT 100 no, '20090101' dt FROM dual
UNION ALL SELECT 100, '20090102' FROM dual
UNION ALL SELECT 100, '20090103' FROM dual
UNION ALL SELECT 100, '20090105' FROM dual
UNION ALL SELECT 100, '20090106' FROM dual
UNION ALL SELECT 100, '20090109' FROM dual
UNION ALL SELECT 100, '20090120' FROM dual
UNION ALL SELECT 200, '20090101' FROM dual
UNION ALL SELECT 200, '20090102' FROM dual
UNION ALL SELECT 200, '20090103' FROM dual
UNION ALL SELECT 200, '20090104' FROM dual
UNION ALL SELECT 200, '20090131' FROM dual
UNION ALL SELECT 200, '20090201' FROM dual
)
SELECT
NO
, MIN(DT) FROM_DT
, MAX(DT) TO_DT
, COUNT(*) CNT
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY NO ORDER BY NO , DT ) RN
, NO , DT
FROM T
)
GROUP BY NO , TO_DATE(DT,'YYYYMMDD') - RN
ORDER BY NO , FROM_DT
해설있었네.. 앞에만 보고.. ㅡㅡ
감사합니다. MSSQL은 제약이 많은데도 성실히 답변해 주시는 마농님의 내공, 대단하십니다.
문제중에,
'테이블의 번호와 날짜는 중복되지 않는 유일한 값입니다.' 를 중복되는 값이 있다면 원하는 값을 얻을 수 없게 되는 것인가요?
글쎄요...
일단 중복이 없어야 유의미한 데이터가 될 것 같네요.
중복이 있다면? 원하는 값을 얻을 수 없냐고 질문하셨는데요.
중복된 경우에 대한 원하는 결과값을 우선 정해야 할 듯 하네요.
원하는 결과가 어떻게 되느냐애 따라 쿼리를 작성하면 됩니다.
불가능은 없습니다. 아마도?ㅎㅎ
SELECT NO
, DT AS "FROM_DT"
, LAG(DT) OVER (ORDER BY NO) AS PREV
, DECODE(LAG(DT) OVER (ORDER BY NO ), DT-1, 0, 1) flag --연속된 값이면 0 아니면 1. 이전값 알 수 있는 LAG 함수 이용
FROM TMP
WHERE 1 = 1
;
여기까지밖에 생각 못하고 정답 보았습니다. 겅부 완료!
WITH T AS (SELECT 100 NO, '2200' DT FROM DUAL UNION ALL SELECT 100, '2201' FROM DUAL UNION ALL SELECT 100, '2159' FROM DUAL UNION ALL SELECT 100, '2202' FROM DUAL UNION ALL SELECT 100, '2204' FROM DUAL UNION ALL SELECT 100, '2205' FROM DUAL UNION ALL SELECT 100, '2206' FROM DUAL UNION ALL SELECT 100, '2158' FROM DUAL) SELECT MIN ( DT ) FROM_DT, MAX ( DT ) TO_DT, COUNT ( * ) CNT FROM ( SELECT * FROM T ORDER BY DT) GROUP BY TO_DATE ( DT, 'HH24MI' ) - ( ROWNUM / 24 / 60 ) ORDER BY FROM_DT;
연속된 시간도 검사할 수 있는지 체크하고 싶었는데 되네요 ! 감사합니다.
그동안 궁금했던건데 오늘에서야 방법을 알아갑니다.. 이런문제가 종종있었는데 찾아봐도 원하는답이 안나와 어렵게 짯었는데 정말 감사합니다..
) 버튼을 클릭하여 작성 하시면 됩니다.