[Oracle] Oracle Function(오라클 함수) - GROUP BY절, HAVING절, 기타 그룹화 관련 함수

들어가기 전에

이 포스팅은 오라클로 배우는 데이터베이스 입문(이지훈 저)을 읽으면서 기억하고자 하는 내용 및 추가적으로 알아본 내용을 담았습니다. 책 전체 내용을 보시고 싶으신 분은 책을 구매하시어 읽어보시길 바랍니다.

오라클 함수

오라클 함수란, 특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어를 의미합니다. 오라클 함수는 함수를 제작한 주체를 기준으로 크게 2가지로 나뉩니다.

• 내장 함수(built-in function): 오라클에서 기본으로 제공하고 있는 함수
  • 내장 함수는 입력 방식에 따라 데이터 처리에 사용하는 행이 나뉩니다.
  • 단일행 함수(single-row function): 데이터가 한 행씩 입력되고, 입력된 각 행별로 결과가 하나씩 나오는 함수
  • 다중행 함수(multiple-row function): 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수
• 사용자 정의 함수(user-defined function): 사용자가 필요에 의해 직접 정의한 함수

단일행 함수와 다중행 함수(오라클로 배우는 데이터베이스 입문, 129p)

이번 시간에는 Oracle에서 제공하는 내장 함수 중 다중행 함수를 좀 더 응용해서 사용하는 방법에 대해 알아보도록 하겠습니다.

기본적으로, 다중행 함수는 조회 결과 하나의 결과만을 출력해야 합니다. 만약, 아래와 같이 특정 조건에 따라 각각 다중행 함수를 사용하고 싶다면, 조회를 따로 따로 하거나 집합 연산자를 사용해야 합니다.

• 의미 있는 하나의 결과를 특정 열 값별로 묶어서 출력할 때 데이터를 그룹화한다고 표현합니다.

각 조건에 따라 다중행 함수 여러 번 사용 예시

• 집합 연산자를 사용한 예시를 보면, '10', '20'과 같이 DEPTNO를 직접 하드 코딩한 것을 볼 수 있습니다. 이는, 다중행 함수인 AVG를 사용하고 있어 여러 행이 나올 수 있는 DEPTNO 컬럼을 함께 조회하지 못하여 위와 같이 작성한 것입니다.
• 집합 연산자를 사용해서 원하는 결과는 얻었지만, 추후 DEPTNO가 추가되거나 삭제될 경우 SQL문을 매번 수정해주어야 하기 때문에 바람직하지 않습니다.

GROUP BY

SELECT   [조회할 열 이름1], [조회할 열 이름2], ...
FROM     [조회할 테이블 이름]
WHERE    [조회할 행을 선별하는 조건식]
GROUP BY [그룹화할 열을 지정(여러 개 지정 가능)]
ORDER BY [정렬하려는 열 지정(여러 개 지정 가능)];

GROUP BY 사용 예시

• GROUP BY절에 명시하는 그룹핑할 대상은 여러 컬럼이 될 수 있으며, 먼저 적힌 컬럼으로 그룹핑한 후 다음으로 적힌 대상으로 그룹핑합니다.
• ORDER BY절의 경우, GROUP BY에 의해 그룹핑된 후 정렬을 어떻게 할 지 정합니다. ORDER BY 역시 여러 열을 지정할 수 있습니다.

GROUP BY 유의점

GROUP BY 유의할 부분은 GROUP BY절에 작성한 그룹화하려는 열 외에 다른 일반 열은 SELECT절에서 사용할 수 없다는 점입니다.

GROUP BY에 작성되지 않은 일반열 SELECT절에 사용 불가

• GROUP BY를 통해 DEPTNO와 JOB에 대해 각각 그룹핑을 했는데, ENAME은 그룹핑하지 않아 여러 행으로 존재하여 사용이 불가합니다.

GROUP BY절에 조건을 주는 HAVING

SELECT   [조회할 열 이름1], [조회할 열 이름2], ...
FROM     [조회할 테이블 이름]
WHERE    [조회할 행을 선별하는 조건식]
GROUP BY [그룹화할 열을 지정(여러 개 지정 가능)]
HAVING   [그룹화된 대상의 출력을 제한하는 조건식]
ORDER BY [정렬하려는 열 지정(여러 개 지정 가능)];

기본적으로 GROUP BY를 사용하면, 그룹핑 대상 열에 대해 모두 그룹핑이 됩니다. 이때, 특정 그룹에 대해서만 조회하고자 한다면 HAVING절을 사용할 수 있습니다.

HAVING절 사용 예시

HAVING? WHERE?

HAVING절은 WHERE절과 마찬가지로 지정된 조건식이 참인 결과에 대해서만 출력합니다. 다만, WHERE절은 출력 대상 행을 제한하는 것이고, HAVING절은 그룹화된 대상 중 조건에 맞지 않는 그룹의 출력을 제한한다는 점이 다릅니다.

WHERE절과 HAVING절 사용 예시

• WHERE절과 HAVING절을 함께 사용하면, 먼저 WHERE절이 실행되어 출력 대상을 제한 한 후 남은 대상을 이용해 그룹핑합니다.
• 그룹핑을 진행할 때, HAVING절을 통해 출력될 대상 그룹이 제한됩니다.
• 즉, WHERE절이 GROUP BY절과 HAVING절보다 먼저 실행됩니다.

위 내용까지 알면 기본적인 그룹핑 작업을 처리할 수 있습니다. 이제는, 좀 더 난이도가 있는 그룹화 관련 함수에 대해 알아보겠습니다.

ROLLUP, CUBE, GROUPING SETS 함수

ROLLUP, CUBE, GROUPING SETS 함수는 GROUP BY절에 사용할 수 있는 특수 함수입니다.

ROLLUP 함수

SELECT   [조회할 열 이름1], [조회할 열 이름2], ...
FROM     [조회할 테이블 이름]
WHERE    [조회할 행을 선별하는 조건식]
GROUP BY ROLLUP([그룹화할 열을 지정(여러 개 지정 가능)]);

ROLLUP 출력 과정

ROLLUP(A, B, C)
1. A 그룹별 B 그룹별 C 그룹에 해당하는 결과 출력
2. A 그룹별 B 그룹에 해당하는 결과 출력
3. A 그룹에 해당하는 결과 출력
4. 전체 데이터 결과 출력

ROLLUP 함수 사용 예시

• ROLLUP 함수를 사용하면 기존 GROUP BY를 사용할 때보다 많은 레코드가 출력됩니다.
  • 위 예시의 경우 그룹핑을 할 때에 두 가지 열을 기준으로 그룹핑을 하도록 작성하였습니다.
  • 이 경우, ROLLUP 함수를 사용하면 소그룹(동일 DEPTNO를 가진 JOB별 그룹들)들의 총 결과를 출력하고, 마지막으로 모든 그룹들의 대한 총 결과를 출력합니다.

GROUP BY 절에 부분 ROLLUP 역시 사용 가능합니다.

GROUP BY에 부분 ROLL UP 적용 예시

• 먼저, DEPTNO로 그룹핑을 한 후, ROLLUP(JOB)을 통해 JOB별로 그룹핑 결과 출력하고, 전체 JOB에 대한 결과 추출

CUBE 함수

SELECT   [조회할 열 이름1], [조회할 열 이름2], ...
FROM     [조회할 테이블 이름]
WHERE    [조회할 행을 선별하는 조건식]
GROUP BY CUBE([그룹화할 열을 지정(여러 개 지정 가능)]);

CUBE 출력 과정

CUBE(A, B, C)
1. A 그룹별 B 그룹별 C 그룹에 해당하는 결과 출력
2. A 그룹별 B 그룹에 해당하는 결과 출력
3. B 그룹별 C 그룹에 해당하는 결과 출력
4. A 그룹별 C 그룹에 해당하는 결과 출력
5. A 그룹에 해당하는 결과 출력
6. B 그룹에 해당하는 결과 출력
7. C 그룹에 해당하는 결과 출력
8. 전체 데이터 결과 출력

CUBE 함수 사용 예시

• 기존 ROLLUP 함수를 사용했을 때보다 CUBE 함수를 사용하면 더 많은 레코드가 출력됩니다.
  • ROLLUP 함수는 소그룹들의 총결과를 출력하고, 전체 그룹들에 대한 결과를 출력하였다면, CUBE 함수는 한 단계 더 나아가서 다른 소그룹(DEPTNO 상관없이 JOB별 그룹)의 결과를 각각 출력합니다.

GROUPING SETS 함수

SELECT   [조회할 열 이름1], [조회할 열 이름2], ...
FROM     [조회할 테이블 이름]
WHERE    [조회할 행을 선별하는 조건식]
GROUP BY GROUPING SETS [그룹화 열 지정(여러 개 지정 가능)];

GROUPING SETS 함수는 같은 수준의 그룹화 열이 여러 개일 때 각 열별 그룹화를 통해 결과 값을 출력할 때 사용합니다.

GROUPING SETS 함수 사용 예시

• GROUPING SETS 함수 사용시, 그룹화에 사용되는 열이 각각 따로 그룹화되어 연산되었음을 알 수 있습니다.

그룹화 함수(GROUPING 함수와 GROUPING_ID 함수)

그룹화 함수는 특별한 연산 기능을 수행하지는 않지만, 그룹화 데이터의 식별이 쉽고 가독성을 높이기 위한 목적으로 사용됩니다.

GROUPING 함수

SELECT   [조회할 열 이름1], [조회할 열 이름2], ...
         GROUPING([GROUP BY절에 ROLLUP 또는 CUBE로 명시한 그룹화 대상 열 이름])
FROM     [조회할 테이블 이름]
WHERE    [조회할 행을 선별하는 조건식]
GROUP BY ROLLUP 또는 CUBE([그룹화할 열을 지정(여러 개 지정 가능)]);

GROUPING 함수 예시

• GROUPING 함수 결과는 0 또는 1로 출력됩니다.
  • 0: GROUPING 함수에 지정된 열이 그룹화되었음을 의미
  • 1: GROUPING 함수에 지정된 열이 그룹화되지 않았음을 의미

GROUPING_ID 함수

SELECT   [조회할 열 이름1], [조회할 열 이름2], ...
         GROUPING_ID([그룹화 여부 확인할 열(여러 개 지정 가능) - ROLLUP 또는 CUBE에 명시한 열 이름])
FROM     [조회할 테이블 이름]
WHERE    [조회할 행을 선별하는 조건식]
GROUP BY ROLLUP 또는 CUBE([그룹화할 열을 지정(여러 개 지정 가능)]);

GROUPING_ID 함수는 GROUPING 함수와 마찬가지로 ROLLUP 또는 CUBE 함수로 연산할 때 특정 열이 그룹화되었는지를 출력하는 함수입니다.

• GROUPING_ID 함수는 그룹화 여부를 검사하는 열에 하나씩만 지정하지 않고 여러 개 지정이 가능하다는 특징이 있습니다.

GROUPING_ID 함수 사용 예시

GROUPING_ID 결과는 그룹화 비트 벡터 값으로 나타내며, GROUPING_ID 대상이 2개일 경우 그룹화 비트 벡터는 아래와 같습니다.

[ GROUPING_ID(a,b) 그룹화 비트 벡터 ]

그룹화 된 열	그룹화 비트 백터	최종 결과
a, b	0 0	0
a	0 1	1
b	1 0	2
a와 b 모두 그룹화되지 않음	1 1	3

LISTAGG 함수

SELECT [조회할 열 이름1], [조회할 열 이름2], ...,
       LISTAGG([나열할 열(필수)], [각 데이터를 구분하는 구분자(선택) - default NULL]) 
       WITHIN GROUP(ORDER BY 나열할 열의 정렬 기준(선택) - default ASC)
FROM   [조회할 테이블 이름]
WHERE  [조회할 행을 선별하는 조건식];

LISTAGG 함수는 오라클 11g 버전부터 사용할 수 있는 함수입니다. 그룹에 속한 데이터를 한 열에 나열할 때 사용합니다.

예를 들어, 아래 왼쪽 사진과 같이 DEPTNO별로 사원 이름을 나열할 수 있지만 이 경우 한 눈에 어떤 부서에 어떤 사원이 존재하는지 보기 어렵습니다. 이럴 때, 오른쪽 사진과 같이 LISTAGG 함수를 사용할 수 있습니다.

LISTAGG 함수 사용 예시(오른쪽)

PIVOT, UNPIVOT 함수

SELECT *
FROM   ([PIVOT 대상 쿼리문])
PIVOT  ([집계 함수(집계할 열 이름)]
       FOR [PIVOT 대상 컬럼 이름] IN ([PIVOT 대상 컬럼 값1 AS 별칭1, PIVOT 대상 컬럼 값2 AS 별칭 2...]);

SELECT *
FROM   ([UNPIVOT 대상 쿼리문])
PIVOT  ([UNPIVOT 대상 컬럼(열이 될 컬럼)]
       FOR [UNPIVOT 대상 컬럼 이름(행이 될 컬럼)] IN ([UNPIVOT 대상 컬럼 값1 AS 별칭1, UNPIVOT 대상 컬럼 값2 AS 별칭 2...]);

PIVOT과 UNPIVOT 함수 역시 오라클 11g부터 사용할 수 있는 함수입니다.

• PIVOT 함수는 기존 테이블 행을 열로 바꾸어 출력
• UNPIVOT 함수는 기존 테이블 열을 행으로 바꿔서 출력

[ PIVOT 함수 사용 예시 ]

• 왼쪽 사진의 경우, 기본형으로 DEPTNO와 JOB이 세로로 나열되어 있음을 확인할 수 있습니다.
• 중간 사진의 경우, 세로로 나열되어 있던 DEPTNO와 JOB 열 값을 각각 DEPTNO는 가로로, JOB은 세로로 나열한 것입니다.
  • 기존 테이블의 행(DEPTNO)을 열로 바꿔야 하므로, PIVOT에서 DEPTNO를 FOR로 명시하고, 실제 출력하려는 데이터인 10, 20, 30을 IN 안에 지정했습니다.

[ UNPIVOT 함수 사용 예시 ]

기존에 PIVOT 함수를 사용한 것을 UNPIVOT 함수를 사용하여 원래 상태로 되돌리는 예시입니다.

PIVOT 결과를 UNPIVOT 함수를 통해 되돌리기

• 앞서 UNIPIVOT 함수는 기존 테이블의 열을 행으로 바꾸는 것이라고 했습니다. 위 예시에서는 중간 사진에서 보이는 TEN/TWENTY/THIRTY 열을 각각 행으로 바꾸어 DEPTNO라는 컬럼의 값으로 들어가야 합니다.
  • 따라서, FOR 뒤에 UNPIVOT 함수로 인해 컬럼이 될 DEPTNO를 넣어주고, 해당 컬럼의 값으로는 TEN/TWENTY/THIRTY가 들어가야 함을 IN절로 나타냅니다.
  • 그리고, 기존에 행으로 나타났던 SAL을 열로 바꿔주기 위해 UNPIVOT 시작 위치에 SAL을 넣어주었습니다.

참고 자료

• 오라클로 배우는 데이터베이스 입문(이지훈 저)