이번 퀴즈로 배워보는 SQL 시간에는 목록과 함께 평균, 최대, 최소값을 구하는 문제를 풀어본다. 지면 특성상 문제와 정답 그리고 해설이 같이 있다.
진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결한 다음 정답과 해설을 참조하길 바란다. 공부를 잘하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 기억하자.
[리스트 1]과 [표 1]은 일별 자료 현황 정보를 보여주는 리스트와 테이블입니다. 일자(DT)별 4개의 숫자값(V1, V2, V3, V4)과 상태 코드(YN) 항목을 확인할 수 있습니다.
- [리스트 1] 일별 자료 현황
-
CREATE TABLE t AS SELECT '20140101' dt, 9 v1, 2 v2, 9 v3, 9 v4, 'N' yn FROM dual UNION ALL SELECT '20140102', 9.9, 2.2, 9, 9, 'N' FROM dual UNION ALL SELECT '20140103', 9.8, 2.3, 9, 9, 'N' FROM dual UNION ALL SELECT '20140104', 9.7, 2.4, 9, 9, 'N' FROM dual UNION ALL SELECT '20140105', 9.6, 2.5, 9, 9, 'N' FROM dual UNION ALL SELECT '20140106', 9.5, 2.6, 5, 5, 'Y' FROM dual UNION ALL SELECT '20140107', 9.4, 2.7, 5, 5, 'Y' FROM dual UNION ALL SELECT '20140108', 9.3, 2.8, 5, 5, 'Y' FROM dual UNION ALL SELECT '20140109', 9.2, 2.9, 5, 5, 'Y' FROM dual UNION ALL SELECT '20140110', 9.1, 3.0, 5, 5, 'Y' FROM dual; SELECT * FROM t ORDER BY id, sdt;
- [표 1] 일별 자료 현황
-
DT V1 V2 V3 V4 YN -------- ---------- ---------- ---------- ---------- -- 20140101 9 2 9 9 N 20140102 9.9 2.2 9 9 N 20140103 9.8 2.3 9 9 N 20140104 9.7 2.4 9 9 N 20140105 9.6 2.5 9 9 N 20140106 9.5 2.6 5 5 Y 20140107 9.4 2.7 5 5 Y 20140108 9.3 2.8 5 5 Y 20140109 9.2 2.9 5 5 Y 20140110 9.1 3 5 5 Y
문제
[표 1]의 각 일자별 수치 정보와 상태 코드 목록을 출력하고, 상태 코드가 ‘Y’인 자료의 평균, 최대값, 최소값을 출력하세요. 마찬가지로 전체 자료에 대한 평균, 최대값, 최소값을 함께 출력하는 쿼리를 작성하세요. 또한 [표 2]의 결과를 도출하는 쿼리를 작성하세요. 평균값은 소수 2자리까지 표시하세요.
- [표 2] 결과 테이블
-
DT V1 V2 V3 V4 YN ------------ ---------- ---------- ---------- ---------- -- 20140101 9 2 9 9 N 20140102 9.9 2.2 9 9 N 20140103 9.8 2.3 9 9 N 20140104 9.7 2.4 9 9 N 20140105 9.6 2.5 9 9 N 20140106 9.5 2.6 5 5 Y 20140107 9.4 2.7 5 5 Y 20140108 9.3 2.8 5 5 Y 20140109 9.2 2.9 5 5 Y 20140110 9.1 3 5 5 Y Y 평균값 9.3 2.8 5 5 Y Y 최대값 9.5 3 5 5 Y Y 최소값 9.1 2.6 5 5 Y 전체 평균값 9.45 2.54 7 7 전체 최대값 9.9 3 9 9 전체 최소값 9 2 5 5
문제설명
이번 시간에 풀어볼 문제는 일별 자료 현황 목록과 함께 평균, 최대, 최소값을 출력하는 문제입니다.
[표 1]의 일별 자료 현황을 그대로 출력하면서 집계 현황을 추가로 출력하면 됩니다. 집계는 평균값, 최대값, 최소값을 각각의 행에 출력하는 방식이며, 상태 코드 Y에 해당하는 집계결과 3행과 전체 자료에 대한 집계결과 3행, 이렇게 총 6개 행의 결과를 추가로 출력하는 문제입니다.
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트 2] 정답 리스트
SELECT NVL(dt, NVL(yn, '전체') ||' '||
DECODE(gid, 0, '평균값', 1, '최대값', 2, '최소값')) dt
, v1, v2, v3, v4
, yn
FROM (SELECT gb, yn, dt
, DECODE(GROUP_ID(), 0, ROUND(AVG(v), 2), 1, MAX(v), 2, MIN(v)) v
, GROUP_ID() gid
FROM t
UNPIVOT (v FOR gb IN (v1, v2, v3, v4))
GROUP BY gb, 1, ROLLUP(1, 1, yn, 1, 1, dt)
HAVING NOT (yn = 'N' AND GROUPING_ID(yn, dt) = 1)
)
PIVOT (MIN(v) FOR gb IN ('V1' AS v1, 'V2' AS v2, 'V3' AS v3, 'V4' AS v4)) a
ORDER BY a.dt, a.yn, a.gid
;
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
이번 문제는 원본 리스트와 집계내역을 함께 보여주는 문제입니다. 이를 위해 사용할 수 있는 가장 간단한 방법은 GROUPING 함수인 ROLLUP입니다. 정답 쿼리를 살펴보기 전에 차근차근 단계별로 문제에 접근해 보겠습니다. 우선은 ROLLUP을 이용해 평균값을 함께 표시해 봅시다.
- [리스트 3] ROLLUP을 이용한 평균값 표시
-
SELECT dt , ROUND(AVG(v1), 2) v1 , ROUND(AVG(v2), 2) v2 , ROUND(AVG(v3), 2) v3 , ROUND(AVG(v4), 2) v4 , yn , GROUPING(yn) g1 , GROUPING(dt) g2 , GROUPING_ID(yn, dt) gid FROM t GROUP BY ROLLUP(yn, dt) --HAVING NOT (yn = 'N' AND GROUPING_ID(yn, dt) = 1) ORDER BY dt, yn ;
[리스트 3]의 쿼리를 이용해 [표 3]과 같은 결과를 얻었습니다. [리스트 3]의 쿼리의 가장 안쪽 인라인뷰만 실행한 결과는 [표 3]과 같습니다.
- [표 3] ROLLUP을 이용한 평균값 표시
-
DT V1 V2 V3 V4 YN G1 G2 GID --------- ------ ------ ------ ------ -- ------- ------- ------- 20140101 9 2 9 9 N 0 0 0 20140102 9.9 2.2 9 9 N 0 0 0 20140103 9.8 2.3 9 9 N 0 0 0 20140104 9.7 2.4 9 9 N 0 0 0 20140105 9.6 2.5 9 9 N 0 0 0 20140106 9.5 2.6 5 5 Y 0 0 0 20140107 9.4 2.7 5 5 Y 0 0 0 20140108 9.3 2.8 5 5 Y 0 0 0 20140109 9.2 2.9 5 5 Y 0 0 0 20140110 9.1 3 5 5 Y 0 0 0 9.6 2.28 9 9 N 0 1 1 9.3 2.8 5 5 Y 0 1 1 9.45 2.54 7 7 1 1 3
GROUP BY ROLLUP(yn, dt)는 ROLLUP의 구성항목들을 오른쪽 항목부터 차례로 없애나가면서 집계한 집합들의 합집합입니다. 1. yn, dt가 모두 있는 GROUP BY yn, dt 집합 2. yn, dt 중 dt가 제거된 GROUP BY yn 집합 3. yn, dt가 모두 제거된 전체 집합 총 세 가지 집합이 나오게 됩니다.
이렇게 구성된 집합들을 구별해 주는 함수가 GROUPING 과 GROUPING_ID입니다. GROUPING(yn)은 yn 값이 그룹핑됐는지 여부를 나타냅니다. yn이 그룹핑돼 사라진 전체 집합의 경우 1로 표시됩니다. GROUPING(dt)는 dt 값이 그룹핑됐는지 여부를 나타냅니다.
dt가 그룹핑돼 사라진 yn만 남은 집합과 전체 집합의 경우 1로 표시됩니다. 롤업에 의해 발생된 세 가지 집합을 구별하려면 GROUP ING(yn)이나 GROUPING(dt) 단독으로는 구별이 안되고 두 가지를 조합해야만 구별이 가능합니다.
GROUPING(yn) 값과 GROUPING(dt) 값을 문자열로 연결한 뒤, 이 값을 2진수에서 10진수로 변경한 것이 바로 GROUP ING_ID(yn, dt)입니다(2진수 11 → 10진수 3). GROUPING_ID(yn, dt) 값을 확인하면 앞선 세 가지 집합을 구별해낼 수 있습니다.
[표 3]의 gid를 보면 0, 1, 3 이렇게 세 가지로 구별되는 것이 확인됩니다. [표 3]의 결과 중 우리가 필요로 하는 결과는 Y의 평균과 전체 평균입니다. 여기서 N의 평균은 필요가 없습니다. N의 평균을 제거하기 위해서는 HAVING 절에서 다음 조건을 주면 됩니다.
- [리스트 4] N의 평균 제거
-
HAVING NOT (yn = 'N' AND GROUPING_ID(yn, dt) = 1)
N 값이면서 GROUPING_ID(yn, dt)가 1인 조건 전체에 부정의 의미인 NOT을 붙여 해당 조건이 아닌 다른 자료만 뽑아내는 방법입니다. ROLLUP을 이용해 평균값을 구했습니다만, 최대값과 최소값은 어떻게 추가할 수 있을까요?
- [리스트 5] ROLLUP의 응용(상수값 연속 사용)
-
SELECT dt , GROUP_ID() gid , DECODE(GROUP_ID(), 0, ROUND(AVG(v1), 2), 1, MAX(v1), 2, MIN(v1)) v1 , DECODE(GROUP_ID(), 0, ROUND(AVG(v2), 2), 1, MAX(v2), 2, MIN(v2)) v2 , DECODE(GROUP_ID(), 0, ROUND(AVG(v3), 2), 1, MAX(v3), 2, MIN(v3)) v3 , DECODE(GROUP_ID(), 0, ROUND(AVG(v4), 2), 1, MAX(v4), 2, MIN(v4)) v4 , yn FROM t GROUP BY 1, ROLLUP(1, 1, yn, 1, 1, dt) HAVING NOT (yn = 'N' AND GROUPING_ID(yn, dt) = 1) ORDER BY dt, yn, gid ;
[리스트 5]의 쿼리를 이용해 [표 4]라는 결과를 얻었습니다.
- [표 4] ROLLUP의 응용(상수값 연속 사용)
-
DT GID V1 V2 V3 V4 YN ------------ ---------- ---------- ---------- ---------- ---------- -- 20140101 0 9 2 9 9 N 20140102 0 9.9 2.2 9 9 N 20140103 0 9.8 2.3 9 9 N 20140104 0 9.7 2.4 9 9 N 20140105 0 9.6 2.5 9 9 N 20140106 0 9.5 2.6 5 5 Y 20140107 0 9.4 2.7 5 5 Y 20140108 0 9.3 2.8 5 5 Y 20140109 0 9.2 2.9 5 5 Y 20140110 0 9.1 3 5 5 Y 0 9.3 2.8 5 5 Y 1 9.5 3 5 5 Y 2 9.1 2.6 5 5 Y 0 9.45 2.54 7 7 1 9.9 3 9 9 2 9 2 5 5
[리스트 5]를 살펴보면, ROLLUP에 상수값인 1을 연속으로 사용했습니다. 앞서 설명했듯이 ROLLUP은 ROLLUP의 구성항목을 오른쪽 항목에서부터 차례로 지워나가면서 집계한 집합의 합집합입니다. [리스트 6]의 구문을 분석해 봅시다.
- [리스트 6] 일곱 가지 집합으로 구문 분석
-
GROUP BY 1, ROLLUP(1, 1, yn, 1, 1, dt) 1.1. GROUP BY 1, 1, 1, yn, 1, 1, dt 2.1. GROUP BY 1, 1, 1, yn, 1, 1 2.2. GROUP BY 1, 1, 1, yn, 1 2.3. GROUP BY 1, 1, 1, yn 3.1. GROUP BY 1, 1, 1 3.2. GROUP BY 1, 1 3.3. GROUP BY 1
[리스트 6]에는 총 일곱 가지 집합이 나와 있습니다. 2.1, 2.2, 2.3은 언뜻 보면 달라 보이지만 결국 같은 yn에 대한 집계입니다.GROUP BY yn의 집계는 1줄로 나오는 집계입니다. 이 yn의 집계를 3줄로 늘리기 위해 의미 없는 숫자 1을 중간에 두 번 끼워 넣은 것입니다.
이런 이유로 동일한 GROUP BY yn 집합이 세 번 나오게 됩니다. 마찬가지로 yn의 앞에 동일한 1을 두 번 끼워 넣음으로써 1줄로 나오던 전체 집합을 3줄로 나오게 하는 것입니다.
이렇게 나온 집합들을 구분하기 위해 사용한 것이 GROUP_ID()입니다. 이 함수는 ROLLUP이나 CUBE 사용 시 같은 항목에 대한 다른 집합이 발생할 경우 이 집합들을 구별해주는 역할을 합니다.
[표 4]를 보면 이 gid 값이 0, 1, 2 이렇게 세 가지로 나오는 것을 확인할 수 있습니다. 이 GROUP_ID()에 따라 구하는 집계방식(평균, 최대, 최소)을 다르게 하면 [리스트 7]과 같습니다.
- [리스트 7] 평균, 최대, 최소 집계방식
-
DECODE(GROUP_ID(), 0, ROUND(AVG(v1), 2), 1, MAX(v1), 2, MIN(v1)) v1
[리스트 5]를 실행해 원하는 결과물이 나오긴 했습니다. 그러나 똑같은 구문이 4개 항목에 각각 반복적으로 적용돼 복잡해 보입니다. 이를 개선하기 위해서는 어떻게 해야 할지 알아봅시다.
옆으로 나열된 4개의 항목을 구분 코드와 함께 아래로 나열된 하나의 항목으로 바꾼다면 어떨지 생각해 봅시다. 네 번 반복적으로 사용한 구문을 한 번만 사용해도 됩니다. 열을 행으로 바꿀 방법 중에는 UNPIVOT를 사용하는 방식도 있습니다.
- [리스트 8] 열을 행으로(UNPIVOT)
-
SELECT dt, gb, yn, v FROM t UNPIVOT (v FOR gb IN (v1, v2, v3, v4)) ORDER BY dt, gb, yn ;
[리스트 8]의 쿼리를 이용해 [표 5]와 같은 결과를 얻었습니다.
- [표 5] 열을 행으로(UNPIVOT)
-
DT GB YN V ------------ ---- -- ---------- 20140101 V1 N 9 20140101 V2 N 2 20140101 V3 N 9 20140101 V4 N 9 20140102 V1 N 9.9 20140102 V2 N 2.2 20140102 V3 N 9 20140102 V4 N 9 20140103 V1 N 9.8 20140103 V2 N 2.3 20140103 V3 N 9 20140103 V4 N 9 20140104 V1 N 9.7 20140104 V2 N 2.4 20140104 V3 N 9 20140104 V4 N 9 ...
- [리스트 9] 4개 항목을 v로 만드는 구문
-
UNPIVOT (v FOR gb IN (v1, v2, v3, v4))
[리스트 9]는 4개 항목(v1, v2, v3, v4)을 하나의 항목인 v로 만드는 구문입니다. 결과는 <표 5>와 같습니다. gb라는 새로운 항목이 생겨났으며, 이 항목은 v를 구별하는 역할을 하게 됩니다.
4개 항목을 하나의 항목으로 만듦으로써 네번 반목됐던 구문을 한 번만 사용할 수 있게 됐습니다.
- [리스트 10] 반복 구문 줄이기
-
SELECT gb, yn, dt , DECODE(GROUP_ID(), 0, ROUND(AVG(v), 2), 1, MAX(v), 2, MIN(v)) v , GROUP_ID() gid FROM t UNPIVOT (v FOR gb IN (v1, v2, v3, v4)) GROUP BY gb, 1, ROLLUP(1, 1, yn, 1, 1, dt) HAVING NOT (yn = ‘N’ AND GROUPING_ID(yn, dt) = 1) ;
PIVOT/UNPIVOT의 결과집합은 별도 인라인뷰로 감싸지 않고 바로 사용할 수 있습니다. [리스트 10]에서는 인라인뷰 없이 바로 GROUP BY gb의 형태로 사용했습니다. [리스트 5]에서 사용했던 GROUP BY 구문에 gb 항목만 추가된 형태입니다.
이제 열을 행으로(UNPIVOT) 바꿨던 [리스트 10]의 결과를 이용해 다시 행을 열로(PIVOT) 바꾼다면 최종 정답리스트 [리스트 2]가 완성됩니다.
이번 시간에 배운 팁을 정리하면 다음과 같습니다. 1. 롤업을 이용해 원본 자료에 집계자료를 추가하는 방법 2. 롤업에 상수값을 추가함으로써 집계결과행을 추가하는 방법 3. 반복되는 구문을 줄이기 위한 UNPIVOT/PIVOT 방법
- 강좌 URL : http://www.gurubee.net/lecture/2862
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
WITH t
as (
SELECT '20140101' dt, 9 v1, 2 v2, 9 v3, 9 v4, 'N' yn FROM dual
UNION ALL SELECT '20140102', 9.9, 2.2, 9, 9, 'N' FROM dual
UNION ALL SELECT '20140103', 9.8, 2.3, 9, 9, 'N' FROM dual
UNION ALL SELECT '20140104', 9.7, 2.4, 9, 9, 'N' FROM dual
UNION ALL SELECT '20140105', 9.6, 2.5, 9, 9, 'N' FROM dual
UNION ALL SELECT '20140106', 9.5, 2.6, 5, 5, 'Y' FROM dual
UNION ALL SELECT '20140107', 9.4, 2.7, 5, 5, 'Y' FROM dual
UNION ALL SELECT '20140108', 9.3, 2.8, 5, 5, 'Y' FROM dual
UNION ALL SELECT '20140109', 9.2, 2.9, 5, 5, 'Y' FROM dual
UNION ALL SELECT '20140110', 9.1, 3.0, 5, 5, 'Y' FROM dual
)
SELECT DT
, MAX(V1)
, MAX(V2)
, MAX(V3)
, MAX(V4)
, MAX(YN)
, (SELECT AVG(V1) FROM T WHERE YN = 'Y') Y평균값
, (SELECT MAX(V1) FROM T WHERE YN = 'Y') Y최대값
, (SELECT MIN(V1) FROM T WHERE YN = 'Y') Y최소값
FROM t
GROUP BY ROLLUP(DT)
ORDER BY dt;
이런식으로밖에 못해서 그냥 풀이 보고 공부합니다 GROUPING, PIVOT에 대해 새로 알고 갑니다.
) 버튼을 클릭하여 작성 하시면 됩니다.