이번 퀴즈로 배워보는 SQL 시간에는 출석현황 테이블을 이용하여 5일 연속 결석 여부를 구하는 쿼리 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.
진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.
문제
- [리스트 1] 원본리스트(출석현황)
-
CREATE TABLE t AS SELECT 'A' id, '20141103' dt, 1 st FROM dual UNION ALL SELECT 'A', '20141104', 1 FROM dual UNION ALL SELECT 'A', '20141105', 2 FROM dual UNION ALL SELECT 'A', '20141106', 2 FROM dual UNION ALL SELECT 'A', '20141107', 1 FROM dual UNION ALL SELECT 'A', '20141110', 2 FROM dual UNION ALL SELECT 'A', '20141111', 2 FROM dual UNION ALL SELECT 'A', '20141112', 2 FROM dual UNION ALL SELECT 'A', '20141113', 1 FROM dual UNION ALL SELECT 'A', '20141114', 1 FROM dual UNION ALL SELECT 'B', '20141103', 1 FROM dual UNION ALL SELECT 'B', '20141104', 1 FROM dual UNION ALL SELECT 'B', '20141105', 2 FROM dual UNION ALL SELECT 'B', '20141106', 2 FROM dual UNION ALL SELECT 'B', '20141107', 2 FROM dual UNION ALL SELECT 'B', '20141110', 2 FROM dual UNION ALL SELECT 'B', '20141111', 2 FROM dual UNION ALL SELECT 'B', '20141112', 2 FROM dual UNION ALL SELECT 'B', '20141113', 1 FROM dual UNION ALL SELECT 'B', '20141114', 1 FROM dual; SELECT * FROM t;
- [표 1] 원본테이블(할인쿠폰)
-
ID DT ST -- ---------------- ---------- A 20141103 1 A 20141104 1 A 20141105 2 A 20141106 2 A 20141107 1 A 20141110 2 A 20141111 2 A 20141112 2 A 20141113 1 A 20141114 1 B 20141103 1 B 20141104 1 B 20141105 2 B 20141106 2 B 20141107 2 B 20141110 2 B 20141111 2 B 20141112 2 B 20141113 1 B 20141114 1
<표 1> 원본테이블로부터 <표 2>의 결과를 도출하는 SQL을 작성하세요.
- [표 2] 결과테이블(5일 이상 결석 여부)
-
ID YN -- -- A n B Y
문제설명
<표 1>은 출결현황 테이블입니다. 각 대상자(ID)별, 일자(DT)별 출결상태(ST)가 저장되어 있습니다.
출결상태 1은 출석을 의미하며, 2는 결석을 의미합니다. 대상자별 출결사항을 체크하여 연속 결석일수가 5일 이상인 경우에 Y값을, 5일 미만일 경우 N값을 반환하는 SQL을 작성하는 문제입니다.
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트 2] 정답 리스트
SELECT id
, CASE WHEN
LISTAGG(st) WITHIN GROUP(ORDER BY dt)
LIKE '%22222%' THEN 'Y' ELSE 'n' END yn
FROM t
GROUP BY id
ORDER BY id
;
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
이번 문제는 연속 결근일수가 5일 이상인지를 체크하는 문제입니다. 단순 결석일수의 합계가 아닌 연속이라는 개념이 들어가 있어서 어려운 문제입니다. 연속 개념 없이 Group By 했을 때의 결과를 살펴보겠습니다.
- [리스트 3] ID별 결석일수
-
SELECT id , COUNT(DECODE(st, 2, 1)) cnt FROM t GROUP BY id ORDER BY id ;
- [표 3] ID별 결석일수
-
ID CNT -- ---------- A 5 B 6
<리스트 3>의 쿼리를 수행하여 <표 3>의 결과를 얻었습니다. 결석일수가 각각 5일과 6일이 나왔습니다. 하지만 이 결과를 이용 할 수는 없습니다.
A의 경우 5일 이상 결석한 것은 맞지만 5일 연속 결근한 것은 아닙니다. 연속이라는 개념이 도입되어야 하는데요. 우선 연속값을 그룹화 하는 몇 가지 예제를 살펴보도록 하겠습니다.
- [리스트 4] 연속값 그룹화 하기(1)
-
SELECT id , MIN(dt) sdt , MAX(dt) edt , COUNT(*) cnt , st , grp FROM (SELECT id, dt, st , SUM(flag) OVER(PARTITION BY id ORDER BY dt) grp FROM (SELECT id, dt, st , DECODE(LAG(st) OVER(PARTITION BY id ORDER BY dt ), st, 0, 1) flag FROM t ) ) GROUP BY id, grp, st ORDER BY id, sdt ;
- [표 4] 연속값 그룹화 하기(1)
-
ID SDT EDT CNT ST GRP -- ---------------- ---------------- ---------- ---------- ---------- A 20141103 20141104 2 1 1 A 20141105 20141106 2 2 2 A 20141107 20141107 1 1 3 A 20141110 20141112 3 2 4 A 20141113 20141114 2 1 5 B 20141103 20141104 2 1 1 B 20141105 20141112 6 2 2 B 20141113 20141114 2 1 3
<리스트 4>의 쿼리를 수행하여 <표 4>의 결과를 얻었습니다. 연속된 값끼리 묶어 시작일, 종료일, 일수를 간단하게 구했습니다. 동작원리는 다음과 같습니다.
1단계, LAG 함수를 이용하여 이전 상태와 비교하여 Flag를 설정합니다. 이전상태와 동일하면 0 그렇지 않으면 1 이 됩니다.
2단계, Flag를 누적합산해 Grp 값을 구합니다. 연속인 경우 증가하지 않고 연속이지 않은 경우 값이 증가합니다.
3단계, Grp 값을 이용하여 Group By 합니다. 생각보다 간단한 방법으로 연속된 값을 그룹화해 결과를 조출할 수 있습니다. 이번에는 또 다른 방법을 소개해 보겠습니다.
- [리스트 5] 연속값 그룹화 하기(2)
-
SELECT id , MIN(dt) sdt , MAX(dt) edt , COUNT(*) cnt , st , rn1 - rn2 grp FROM (SELECT id, dt, st , ROW_NUMBER() OVER( PARTITION BY id ORDER BY dt) rn1 , ROW_NUMBER() OVER( PARTITION BY id, st ORDER BY dt) rn2 FROM t ) GROUP BY id, st, rn1 - rn2 ORDER BY id, sdt ;
- [표 5] 연속값 그룹화 하기(2)
-
ID SDT EDT CNT ST GRP -- ---------------- ---------------- ---------- ---------- ---------- A 20141103 20141104 2 1 0 A 20141105 20141106 2 2 2 A 20141107 20141107 1 1 2 A 20141110 20141112 3 2 3 A 20141113 20141114 2 1 5 B 20141103 20141104 2 1 0 B 20141105 20141112 6 2 2 B 20141113 20141114 2 1 6
<리스트 5>의 쿼리를 수행하여 <표 5>의 결과를 얻었습니다. 이번에는 인라인뷰의 깊이가 하나 줄어 좀더 간결해 졌습니다. 동작원리는 다음과 같습니다.
1단계, ROW_NUMBER 함수를 이용하여 rn1과 rn2값을 각각 구합니다. 두 값의 차이는 PARTITION BY 구문에 ST가 있는지 여부의 차이가 있습니다. 2단계, rn1에서 rn2를 뺀 값을 이용해 Group By 합니다
이번에는 LAG 함수 없이 ROW_NUMBER 함수를 이용했으며 GRP 의 값은 서로 다르지만 그 역할(연속값의 그룹화)은 충실해 해냈습니다. 또한 인라인뷰의 깊이가 한단계 줄어들어 좀 더 간결해 졌습니다.
이제 이 결과를 이용하여 한번 더 GROUP BY 한다면 최종 결과를 도출 할 수 있을 것입니다.
- [리스트 6] 연속값 그룹화 하기를 이용 최종 결과 도출
-
SELECT id , MAX( CASE WHEN cnt >= 5 THEN 'Y' ELSE 'N' END ) yn FROM (SELECT id , COUNT(*) cnt FROM (SELECT id, dt, st , ROW_NUMBER() OVER( PARTITION BY id ORDER BY dt) rn1 , ROW_NUMBER() OVER( PARTITION BY id, st ORDER BY dt) rn2 FROM t ) GROUP BY id, st, rn1-rn2 ) GROUP BY id ORDER BY id ;
- [표 6] 연속값 그룹화 하기를 이용 최종 결과 도출
-
ID YN -- -- A N B Y
<리스트 6>의 쿼리를 수행하여 <표 6>의 결과를 얻었습니다. 원하는 결과를 도출해 냈습니다. 하지만 뭔가 아쉬움이 남습니다.
인라인 뷰가 2번 사용되어 쿼리 깊이가 3단계가 되었습니다. 조금 더 간략하게 할 수는 없을까? 고민을 해볼 단계입니다.
<표 5>는 단지 최종 결과 <표 6>을 구하기 위한 중간 단계 역할을 하고 있을 뿐입니다.
연속값을 그룹화하여 그룹별 건수를 구한 결과가 최종 조건절에서 사용이 되었지만 결국 우리가 필요한 것은 단지 YN 뿐입니다. 연속값 그룹화의 개념이 아닌 이전 5개의 값만 확인하면 어떨까요?
- [리스트 7] LAG 함수 5회 사용
-
SELECT id , MAX(DECODE(x, '22222', 'Y', 'N')) yn FROM (SELECT id, dt, st , LAG(st, 1) OVER( PARTITION BY id ORDER BY dt) || LAG(st, 2) OVER( PARTITION BY id ORDER BY dt) || LAG(st, 3) OVER( PARTITION BY id ORDER BY dt) || LAG(st, 4) OVER( PARTITION BY id ORDER BY dt) || LAG(st, 5) OVER( PARTITION BY id ORDER BY dt) AS x FROM t ) GROUP BY id ORDER BY id ;
<리스트 7>의 쿼리를 통해 원하는 결과를 얻었습니다. LAG 함수를 5번 이용해 단순하면서도 직관적으로 문제를 풀었습니다. 이전 값 5개를 하나로 연결해 ‘22222’ 인지를 체크 했습니다.
인라인뷰가 줄어들었지만, 조금은 무식해 보이는 방법입니다. 앞의 5개 상태를 확인 할 방법이 이 방법밖에는 없을까요? <리스트 8>의 쿼리를 통해 원하는 결과를 얻었습니다.
- [리스트 8] 윈도우 함수 사용
-
SELECT id , MAX(DECODE(cnt, 5, 'Y', 'N')) yn FROM (SELECT id, dt, st , COUNT(DECODE(st, 2, 1)) OVER(PARTITION BY id ORDER BY dt ROWS 4 PRECEDING ) cnt FROM t ) GROUP BY id ORDER BY id ;
LAG 함수를 5번 사용해 연결하는 대신 분석함수의 윈도우 절을 이용했습니다. 윈도우 절은 분석함수에서 대상 범위를 지정하는 구문으로 ORDER BY절에 항상 따라오게 되는 구문입니다.
평상시에는 생략해 사용하므로 잘 모르고 지나가는 경우가 많죠. 윈도우 절이 생략된 것을 기술하면 <리스트 9>와 같습니다.
- [리스트 9] 윈도우 구문
-
, COUNT(DECODE(st, 2, 1)) -- 함수 OVER( -- 분석함수임을 나타냄 PARTITION BY id -- 대상 그룹을 지정 ORDER BY dt -- 정렬기준 -- 생략된 윈도우절 -- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) cnt
<리스트 9> 구문의 의미를 해석해 보면 분석함수를 이용해 상태(ST)가 결석(2)인 것만 COUNT하는데 대상 그룹은 ID이고 정렬기준은 일자(DT)가 되고 이때 정렬 결과의 맨 처음부터 현재행까지가 대상이 되어 카운트를 하게 됩니다.
처음부터 현재행까지가 생략된 윈도우 구문입니다. 이 대상 기준을 5행 전까지로 변경해 보겠습니다.
- [리스트 10] 윈도우 구문(5행전까지)
-
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
값의 범위인 RANGE 가 아닌 행 기준인 ROWS로 변경했습니다. 현재행 포함 5행 이므로 4 PRECEDING를 사용했습니다. <리스트 10>의 구문은 <리스트 11>처럼 간략하게 표현할 수 있습니다.
- [리스트 11] 윈도우 구문(5행전까지) 간략하게
-
ROWS 4 PRECEDING
<리스트 8>을 통해 조금 더 간략하게 결과를 구했습니다. 마지막으로 인라인 뷰를 하나 더 줄여 볼까요?
- [리스트 12] 정답 리스트
-
SELECT id , CASE WHEN LISTAGG(st) WITHIN GROUP(ORDEER BY dt) LIKE '%22222%' THEN 'Y' ELSE 'N' END yn FROM t GROUP BY id ORDER BY id ;
인라인뷰 없이 최종 결과를 도출했습니다. 정답 쿼리가 완성되었습니다. 동작원리는 상당히 간단합니다. LISTAGG를 이용하여 상태값을 하나의 문자열로 합칩니다.
상태값에 ‘22222’ 가 포함되어 있는지를 확인 합니다. 결과물은 같지만, 결과물을 도출하는 방법은 상당히 다양합니다.
연속값의 구룹화된 결과를 원할 때는 <리스트 4>, <리스트 5>의 방법이 좋을 듯 하구요. 중간 결과가 필요한게 아닌 단순 연속 결석여부만이 필요할 때는 <리스트 12>의 방법을 사용하는 게 간단할 듯 합니다.
- 강좌 URL : http://www.gurubee.net/lecture/2908
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
SELECT ID,DECODE(SUM(TEMP),0,'N','Y') AA FROM ( SELECT ID, DECODE(SUM(ST) OVER(PARTITION BY ID ORDER BY ID,DT ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) ,10,1,0) TEMP FROM t) GROUP BY ID ;
SELECT ID,CASE
WHEN MAX(AA) >= 10 THEN 'Y'
ELSE 'N'
END
FROM(SELECT ID
,DT
,(SUM(ST) OVER (PARTITION BY ID ORDER BY DT ROWS 4 PRECEDING AND CURRENT ROW))AA
FROM t)
GROUP BY ID;
) 버튼을 클릭하여 작성 하시면 됩니다.