이번 퀴즈로 배워보는 SQL 시간에는 지난시간에 이어 숫자를 영문으로 바꾸는 문제를 풀어보도록 하겠습니다. 지면 특성상 문제와 정답 그리고 해설이 같이 있습니다.
진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결 한 후 정답과 해설을 참조하길 바랍니다. 공부를 잘 하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 잊지 말자.
문제
다음과 같은 집합에서 그룹별 이름으로 정렬하여 다음 자료를 출력하세요.
- - 문제1. 홀수행 자료만 검색하세요.
- - 문제2. 짝수행 자료만 검색하세요. 단, 짝수행이 없어도 그룹명은 나와야 합니다.
- [리스트 1] 원본 리스트
WITH t AS
(
SELECT 1 grp, 'A' nm FROM dual
UNION ALL SELECT 1, 'B' FROM dual
UNION ALL SELECT 1, 'C' FROM dual
UNION ALL SELECT 2, 'D' FROM dual
UNION ALL SELECT 2, 'E' FROM dual
UNION ALL SELECT 3, 'F' FROM dual
UNION ALL SELECT 4, 'G' FROM dual
UNION ALL SELECT 4, 'H' FROM dual
UNION ALL SELECT 4, 'I' FROM dual
UNION ALL SELECT 4, 'J' FROM dual
)
SELECT * FROM t
;
GRP N
---------- -
1 A
1 B
1 C
2 D
2 E
3 F
4 G
4 H
4 I
4 J
10 rows selected.
- [표 1] 원본 테이블
- [표 2] 결과 테이블 - 홀수행 검색
- [표 3] 결과 테이블 - 짝수행 검색
문제설명
이 문제는 주어진 조건(홀수/짝수)에 따라 그룹별 이름으로 정렬한 후, 그룹별 (홀수/짝수) 행 자료만 조회하는 문제입니다. 단, 짝수행 조회 시에 1건뿐이라 짝수행ㅇ 없는 그룹이라 할지라도 그룹명은 빠짐없이 조회되어야 합니다.
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트 2] 정답 리스트 - 홀수행 검색
WITH t AS
(
SELECT 1 grp, 'A' nm FROM dual
UNION ALL SELECT 1, 'B' FROM dual
UNION ALL SELECT 1, 'C' FROM dual
UNION ALL SELECT 2, 'D' FROM dual
UNION ALL SELECT 2, 'E' FROM dual
UNION ALL SELECT 3, 'F' FROM dual
UNION ALL SELECT 4, 'G' FROM dual
UNION ALL SELECT 4, 'H' FROM dual
UNION ALL SELECT 4, 'I' FROM dual
UNION ALL SELECT 4, 'J' FROM dual
)
SELECT grp
, nm
FROM (SELECT nm, grp
, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY nm) rn
FROM t
)
WHERE MOD(rn, 2) = 1
;
GRP N
---------- -
1 A
1 C
2 D
3 F
4 G
4 I
6 rows selected.
- [리스트 3] 정답 리스트 - 짝수행 검색
WITH t AS
(
SELECT 1 grp, 'A' nm FROM dual
UNION ALL SELECT 1, 'B' FROM dual
UNION ALL SELECT 1, 'C' FROM dual
UNION ALL SELECT 2, 'D' FROM dual
UNION ALL SELECT 2, 'E' FROM dual
UNION ALL SELECT 3, 'F' FROM dual
UNION ALL SELECT 4, 'G' FROM dual
UNION ALL SELECT 4, 'H' FROM dual
UNION ALL SELECT 4, 'I' FROM dual
UNION ALL SELECT 4, 'J' FROM dual
)
SELECT grp
, DECODE(rn, 1, '', nm) nm
FROM (SELECT grp, nm
, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY nm) rn
, COUNT(*) OVER(PARTITION BY grp) cnt
FROM t
)
WHERE MOD(rn, 2) = 0
OR cnt = 1
ORDER BY grp, nm
;
GRP N
-------- -
1 B
2 E
3
4 H
4 J
어떤가요? 여러분이 만들어본 리스트와 같은가요?
틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
이번 문제는 그룹별로 정렬하고, 정렬결과를 홀수행과 짝수행으로 분류하는 방법에 대해 배워보는 문제입니다.
우선 임의의 숫자를 홀수와 짝수로 구별하는 방법은 무엇일까요? 초등학교 수학문제이지요. 주어진 수를 2로 나누었을 때 나머지가 1이면 홀수, 나누어떨어지면 짝수가 됩니다. 나머지를 구하는 함수 MOD 를 이용하면 문제를 풀 수 있을 듯합니다.
MOD 함수를 이용해 홀/짝 구분을 하기 위해서는 우선 정렬된 순번이 있어야 합니다. 정렬된 순번을 얻으려면 분석함수 ROW_NUMBER 을 이용하면 됩니다.
- [리스트 4] 그룹별 정렬 순번
SELECT nm, grp
, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY nm) rn
FROM t
;
N GRP RN
- ---------- ----------
A 1 1
B 1 2
C 1 3
D 2 1
E 2 2
F 3 1
G 4 1
H 4 2
I 4 3
J 4 4
- [표 4] 그룹별 정렬 순번
분석함수 ROW_NUMBER 를 이용해 간단하게 그룹별 순번을 구했습니다. 이렇게 구한 순번 값에 MOD 함수를 적용해 보겠습니다.
- [리스트 5] 그룹별 정렬 순번에 MOD 함수 적용
SELECT grp
, nm
, rn
, MOD(rn, 2) md
FROM (SELECT nm, grp
, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY nm) rn
FROM t
)
;
GRP N RN MD
------ - ---------- ----------
1 A 1 1
1 B 2 0
1 C 3 1
2 D 1 1
2 E 2 0
3 F 1 1
4 G 1 1
4 H 2 0
4 I 3 1
4 J 4 0
- [표 5] 그룹별 정렬 순번에 MOD 함수 적용
[표 5]의 결과를 보면 그룹별 순번 RN 에 MOD 함수를 적용한 결과 MD 값을 확인 할 수 있습니다. 그룹별로 1 과 0 이 번갈아 반복되고 있습니다. 이 결과를 이용하여 홀수행 검색쿼리를 완성해 보겠습니다.
- [리스트 6] 홀수행 검색
SELECT grp
, nm
, rn
, MOD(rn, 2) md
FROM (SELECT nm, grp
, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY nm) rn
FROM t
)
WHERE MOD(rn, 2) = 1
;
GRP N RN MD
---- - ---------- ----------
1 A 1 1
1 C 3 1
2 D 1 1
3 F 1 1
4 G 1 1
4 I 3 1
- [표 6] 홀수행 검색
MOD(rn, 2) = 1 홀수행만 검색하는데 성공했습니다. 이번에는 MOD(rn, 2) = 0 조건을 추가하여 짝수행만 검색해보도록 하겠습니다.
- [리스트 7] 짝수행 검색
SELECT grp
, nm
, rn
, MOD(rn, 2) md
FROM (SELECT nm, grp
, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY nm) rn
FROM t
)
WHERE MOD(rn, 2) = 0
;
GRP N RN MD
----- - ---------- ----------
1 B 2 0
2 E 2 0
4 H 2 0
4 J 4 0
- [표 7] 짝수행 검색
[표 7] 의 결과를 보면 3번 그룹의 자료가 누락된 것을 확인 할 수 있습니다. 3번 그룹엔 자료가 1건 뿐이고 짝수행이 없어서 MOD(rn, 2) = 0 조건에 의해 자료가 누락 된 것입니다. 하지만 짝수행이 없더라도 그룹명칭은 반드시 나와야 한다는 조건이 있으므로 이를 해결하기 위한 추가 조건을 생각해 봐야 합니다.
3번 그룹은 데이터가 1건 뿐이지만 나와야 합니다. 따라서 그룹별 데이터 건수를 확인하여 1건인 경우에는 짝수행이 없더라도 데이터가 검색되도록 조건을 추가해야 합니다. 그룹별 건수는 마찬가지로 분석함수인 COUNT(*) OVER() 를 이용해 보겠습니다.
- [리스트 8] 짝수행 검색시 그룹별 건수 확인 추가
SELECT grp
, nm
, rn
, MOD(rn, 2) md
, cnt
FROM (SELECT grp, nm
, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY nm) rn
, COUNT(*) OVER(PARTITION BY grp) cnt
FROM t
)
WHERE MOD(rn, 2) = 0
OR cnt = 1
;
GRP N RN MD CNT
----- - ---------- ---------- ----------
1 B 2 0 3
2 E 2 0 2
3 F 1 1 1
4 H 2 0 4
4 J 4 0 4
- [표 8] 짝수행 검색시 그룹별 건수 확인 추가
[표 8] 의 결과를 보면 MD 의 값이 1인데도 불구하고 CNT 값이 1이기 때문에 3번 그룹 이 결과에 포함되었습니다. 이를 위해 조건식은 OR 로 연결하였습니다. 짝수행 이거나 그룹건수가 1인 데이터를 검색하는 쿼리입니다.
그런데 문제가 또 하나 있습니다. 3번그룹의 이름(NM) 항목에 “F" 값이 조회됩니다. 그라나 이 값은 짝수행의 값이 아니므로 나와서는 안됩니다. 즉, 그룹명칭은 나와야 하지만 이름은 나와서는 안됩니다. 이 두가지 조건을 함께 WHERE 절에 기술할 수는 없습니다.
그룸명이 나오게 하는것은 WHERE 절에서 처리를 하고 이름 표시부분은 SELECT 절에서 조건 에 따라 처리해야 합니다. 조건에 따라 처리하도록 해주는 함수는 DECODE, CASE WHEN 함수 가 있습니다. DECODE를 이용해 그룹건수가 1인 경우에는 조회되지 않도록 처리해 보겠습니 다.
- [리스트 9] 짝수행 검색시 이름 조회 조건 추가
, nm -- 변경 전
, DECODE(rn, 1, '', nm) nm -- 변경 후
이렇게 해서 홀수행과 짝수행 검색 쿼리를 모두 풀어 보았습니다. 홀수행 쿼리에 비해 짝수행 쿼리에서는 생각해야 할 부분이 좀 더 많았습니다.
추가 문제입니다 이번에는 두 . 개 쿼리를 하나로 합쳐보도록 할까요? 주어진 구분값에 따라 두가지 조건을 모두 만족하는 하나의 쿼리를 작성해 보세요.
- - 홀수행 검색 조건 : gubun = 1
- - 짝수행 검색 조건 : gubun = 0
- [리스트 10] 구분조건에 따른 홀/짝수행 검색
SELECT grp
, DECODE(:gubun||rn, '01', '', nm) nm
FROM (SELECT grp, nm
, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY nm) rn
, COUNT(*) OVER(PARTITION BY grp) cnt
FROM t
)
WHERE MOD(rn, 2) = :gubun -- (구분 - 1:홀수, 0:짝수)
OR cnt = 1
ORDER BY grp, nm
;
- 강좌 URL : http://www.gurubee.net/lecture/2828
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
문제1에서 홀수행이 왜
1 A 1 C 2 D 3 F 4 G 4 I1 c
2 e
4 g
4 i
그룹별 (홀수/짝수) 행 자료만 조회하는 문제입니다.
-- 엄한짓을 많이 해서 만들어 봤어요..
WITH t AS
(
SELECT 1 grp, 'A' nm FROM dual
UNION ALL SELECT 1, 'B' FROM dual
UNION ALL SELECT 1, 'C' FROM dual
UNION ALL SELECT 2, 'D' FROM dual
UNION ALL SELECT 2, 'E' FROM dual
UNION ALL SELECT 3, 'F' FROM dual
UNION ALL SELECT 4, 'G' FROM dual
UNION ALL SELECT 4, 'H' FROM dual
UNION ALL SELECT 4, 'I' FROM dual
UNION ALL SELECT 4, 'J' FROM dual
)
SELECT NVL(AA.GRP,BB.GRP) GRP
, AA.NM
, DECODE(MOD(NVL(AA.GRP_RN,BB.GRP_RN),2), 0,'짝수','홀수') GUBUN
FROM (
SELECT GRP, NM
, ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY NM) GRP_RN
FROM T
) AA
FULL OUTER JOIN(
SELECT *
FROM (SELECT GRP, MIN(NM) FROM T GROUP BY GRP)
, (SELECT ROWNUM GRP_RN FROM DUAL CONNECT BY LEVEL <= 2)
) BB
ON AA.GRP = BB.GRP
AND AA.GRP_RN = BB.GRP_RN
--WHERE MOD(NVL(AA.GRP_RN,BB.GRP_RN),2) = 1
ORDER BY GRP,NM
;
count 가 분석함수로도 쓰이는군요. 감사합니다
-- 짝수행
select A.grp, B.nm
from
(select grp from t group by grp) A
left outer join
(
select grp, nm, row_number() over(partition by grp order by nm) gb1
from t
) B
on A.grp = B.grp and mod(B.gb1, 2) = 0
order by 1
-- 홀수행
select A.grp, B.nm
from
(select grp from t group by grp) A
left outer join
(
select grp, nm, row_number() over(partition by grp order by nm) gb1
from t
) B
on A.grp = B.grp and mod(B.gb1, 2) = 1
order by 1
) 버튼을 클릭하여 작성 하시면 됩니다.