DB 기초 개념 정리_7)다중행 함수와 데이터 그룹화_DoIt!오라클로 배우는 데이터 베이스 입문

DB/Doit!오라클로 배우는 데이터베이스 입문_개념정리

DB 기초 개념 정리_7)다중행 함수와 데이터 그룹화_DoIt!오라클로 배우는 데이터 베이스 입문

김쟈워니 2024. 6. 13. 13:12

다중행 함수와 데이터 그룹화

하나의 열에 출력 결과를 담는 다중행 함수

다중행 함수(multiple-row function)는 그룹 함수 또는 복수행 함수로도 불림

여러 행이 입력되어 하나의 행으로 결과가 출력되는 특징이 있음

여러 행이 결과로 나올수 있는 열을 함께 사용할 수 없음

대표적인 다중행 함수 중 하나 SUM

SUM 함수는 SELECT문으로 조회된 행에 지정한 열 값을 모두 더한 값을 반환해주는 함수

SUM함수를 사용하여 급여 합계 출력

SELECT SUM(SAL)
    FROM EMP;

SUM 함수를 사용하여 사원 이름과 급여 합계 출력하기

SELECT ENAME,SUM(SAL)
FROM EMP;

ORA-00937 오류 발생

다중행 함수를 사용하여 결과 값이 한 행으로 나오는 데이터와 여러행이 나올수 있는 데이터를 함께 명시할 경우 발생

자주 사용하는 다중행 함수의 종류

함수	설명
SUM	지정한 데이터의 합 반환
COUNT	지정한 데이터의 개수 반환
MAX	지정한 데이터 중 최대값 반환
MIN	지정한 데이터 중 최솟값 반환
AVG	지정한 데이터의 평균값 반환

합계를 구하는 SUM 함수

데이터의 합을 구하는 함수

SUM 함수는 NULL 데이터는 제외하고 합계를 구함

DISTINCT,ALL 을 옵션으로 사용

ALL옵션 은 아무옵션을 지정하지 않았을 떄와 동일

DISTINCT 옵션은 중복 데이터는 제외, 즉 동일한 데이터 값을 가진 데이터들은 단 한번만 합계산

SUM 함수의 기본 형식

SUM([DISTINCT, ALL 중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
[합계를 구할 열이나 연산자, 함수를 사용한 데이터(필수)])

OVER 절과 함께 사용하는 SUM 함수

SUM([DISTINCT,ALL 중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
	[합계를 구할 열이나 연산자, 함수를 사용한 데이터(필수)])
OVER(분석을 위한 여러 문법을 지정)(선택)

DISTINCT,ALL 옵션을 사용하여 합계산 출력하기

SELECT SUM(DISTINCT SAL),
    SUM(ALL SAL),
    SUM(SAL)
    FROM EMP;

데이터 개수를 구해 주는 COUNT 함수

COUNT 함수는 데이터 개수를 출력하는 데 사용

COUNT 함수에 * 을 사용하면 SELECT 문의 결과 값으로 나온 행 데이터의 개수를 반환\

DISTINCT, ALL 옵션 사용

DISTINCT 는 중복 제거, ALL 은 중복 허용

옵션을 지정하지 않았을 때는 ALL 과 동일하게 중복 허용하여 결과값 반환

WHERE 절의 조건식을 함께 사용하기도 함.

NULL 데이터로 포함되어 있을 경우, NULL 데이터는 반환 개수에서 제외 됨.

COUNT함수의 기본형식

COUNT([DISTINCT,ALL 중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
	[개수를 구할 열이나 연산자, 함수를 사용한 데이터(필수)])
OVER(분석을 위한 여러 문법 지정)(선택)

COUNT 함수를 통 테이블의 데이터 개수 출력하기

SELECT COUNT(*)
FROM EMP;

WHERE 절을 이용하여 해당 조건식에 맞는 데이터 개수 출력

SELECT COUNT(*)
FROM EMP
WHERE DEPTNO=30;

DISTINCT 와 ALL 옵션 사용하여 COUNT 함수 사용하기

SELECT COUNT(DISTINCT SAL),
        COUNT(ALL SAL),
        COUNT(SAL)
        FROM EMP;

NULL데이터 반환 개수에서 제외되는 것을 확인 하기위한 COUNT 함수 사용

--NULL 값을 제외하는 조건을 따로 처리하지 않음
SELECT COUNT(COMM)
FROM EMP;

--NULL 값을 제외하는 조건을 IS NOT NULL을 통해 따로 처리
SELECT COUNT(COMM)
FROM EMP
WHERE COMM IS NOT NULL;

NULL 값을 제외하는 조건을 따로 처리하지 않았을 떄와 NULL 값을 제외하는 조건 IS NOT NULL을 통해 처리했을 경우에 결과 값은 동일
COUNT 함수는 NULL 데이터의 경우 반환 개수에 포함하지 않음을 확인할 수 있음

최댓값과 최솟값을 구하는 MAX,MIN 함수

MAX 함수와 MIN 함수는 최댓값과 최솟값을 반환하는 함수

날짜 및 문자데이터 역시 MAX,MIN 함수를 사용할 수 있음

MAX 함수의 기본형식

MAX([DISTINCT,ALL 중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
	[최댓값을 구할 열이나 연산자, 함수를 사용한 데이터(필수)]
OVER(분석을 위한 여러 문법 지정)(선택)

MIN 함수의 기본형식

MIN([DISTINCT,ALL 중 하나를 선택하거나 아무 값도 지정하지 않음(선택)]
	[최댓값을 구할 열이나 연산자, 함수를 사용한 데이터(필수)]
OVER(분석을 위한 여러 문법 지정)(선택)

최댓값 함수 MAX 를 사용하여 특정 조건을 가진 열의 최댓값 구하기

SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO=10;

최솟값 함수 MIN을 사용하여 특정 조건을 가진 열의 최솟값 구하기

SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO=10;

MAX함수를 활용하려 입사일이 가장 최근인 데이터의 날짜 출력하기

SELECT MAX(HIREDATE)
FROM EMP
WHERE DEPTNO=20;

MIN함수를 활용하여 입사일이 가장 오래된 데이터의 날짜 출력하기

SELECT MIN(HIREDATE)
FROM EMP
WHERE DEPTNO=20;

평균 값을 구하는 AVG 함수

AVG 함수는 입력 데이터의 평균 값을 구하는 함수. 숫자 또는 숫자로 암시적 형 변환이 가능한 데이터만 사용할 수 잇음

AVG 함수의 기본형식

AVG[DISTINCT,ALL 중 하나를 선택하거나 아무값도 지정하지 않음(선택)]
	[평균 값을 구할 열이나 연산자, 함수를 사용한 데이터(필수)])
   OVER (분석을 위한 여러문법을 지정)(선택)

AVG 함수를 통해 특정 데이터 열의 평균 구하기

SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO=30;

DISTINCT로 중복을 제거하여 평균 값 구하기

SELECT AVG(DISTINCT SAL)
FROM EMP
WHERE DEPTNO=30;

결과 값을 원하는 열로 묶어 출력하는 GROUP BY 절

GROUP BY 절의 기본 사용법

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

SELECT 문에서는 GROUP BY를 작성하여 데이터를 GROUP BY절을 작성하여 데이터를 그룹화할 수 있음

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

GROUP BY 절에 명시하는 열은 여러개 지정할 수 있음

먼저 지정한 열로 대그룹을 나누고 그 다음 지정한 열로 소그룹을 나눔

GROUP BY를 사용하여 특정 데이터의 열값 별로 묶어 출력하기

SELECT AVG(SAL),DEPTNO
FROM EMP
GROUP BY DEPTNO;

GROUP BY로 특정 데이터의 열값 별로 묶을 데이터 두개 이상을 사용하고, ORDER BY로 정렬하여 출력하기

SELECT DEPTNO, JOB , AVG(SAL)
FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY DEPTNO,JOB;

GROUP BY 절을 사용할 떄 유의점

GROUP BY 절을 사용하여 출력 데이터를 그룹화할 경우 다중행 함수를 사용하지 않은 일반 열은 GROUP BY절에 명시하지 않으면 SELECT절에서 사용할 수 없음

즉, 다중행 함수를 사용하지 않은 열을 GROUP BY에서 사용하지 않으면 각 열별 데이터 개수가 달라져 출력이 불가능함

다중행 함수를 사용하지 않은 열을 GROUP BY에서 사용하지 않아 오류가 발생하는 경우

SELECT ENAME,DEPTNO,AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

GROUP BY절에 조건을 줄 때 사용하는 HAVING 절

HAVING 절은 SELECT 문에 GROUP BY 절이 존재할 때만 사용할 수 있음

GROUP BY 절을 통해 그룹화된 결과 값의 범위를 제한하는 데 사용

GROUP BY 절과 HAVING 절을 사용하여 출력

SELECT DEPTNO,JOB,AVG(SAL)
FROM EMP
GROUP BY DEPTNO,JOB
HAVING AVG(SAL)>=2000
ORDER BY DEPTNO,JOB;

HAVING 절의 기본 사용법

HAVING 절을 사용하는 SELECT 문의 기본 형식

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

HAVING 절은 GROUP BY 절이 존재할 경우 GROUP BY절 바로 다음에 작성

GROUP BY 절과 마찬가지로 별칭 사용 불가

HAVING 절을 사용할 때 유의점

조건식을 지정한다는 점에서 HAVING 절이 WHERE절과 비슷하게 보일 수 있지만

WHERE절은 출력 대상 행을 제한하고, HAVING 절은 그룹화된 대상을 출력에서 제한하므로 쓰임새가 다름

만약 출력 결과를 제한하기 위해 HAVING 절을 사용하지 않고 조건식을 WHERE 절에 명시하면 SELECT 문 오류 발생

HAVING 절대신 WHERE 절을 잘못사용하여 오류가 발생하는 예시

SELECT DEPTNO,JOB,AVG(SAL)
FROM EMP
WHERE AVG(SAL) >=2000
GROUP BY DEPTNO,JOB
ORDER BY DEPTNO,JOB;

WHERE절과 HAVING 절의 차이점

WHERE 절을 사용하지 않고 HAVING 절만 사용한 경우

SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL)>=2000
ORDER BY DEPTNO,JOB;

WHERE절과 HAVING 절 모두 사용한 경우

SELECT DEPTNO,JOB,AVG(SAL)
FROM EMP
WHERE SAL<=3000
GROUP BY DEPTNO,JOB
HAVING AVG(SAL)>=2000
ORDER BY DEPTNO,JOB;

WHERE 절은 GROUP BY 절과 HAVING 절을 사용한 데이터 그룹화보더 먼저 출력 대상이 될 행을 제한.

즉, GROUP BY 절을 수행하기 전에 WHERE 절의 조건식으로 출력 행의 제한이 먼저 이루어짐.

7-4 그룹화와 관련된 여러 함수 부분은 고급기능으로 기초문법을 다 다진 이후에 다시 공부하기

07-4 그룹화와 관련된 여러 함수

ROLLUP, CUBE, GROUPING SETS 함수

ROLLUP,CUBE 함수

ROLLUP,CUVE,GROUPING SETS함수는 GROUP BY 절에 지정할 수 있는 특수 함수

ROLLUP 함수, CUBE함수는 그룹화 데이터의 합계를 출력할 때 유용하게 사용할 수 있음

ROLLUP 기본 형식

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

CUBE 기본형식

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

기존 GROUP BY 절만 사용한 그룹화

SELECT DEPTNO, JOB, COUNT(*),MAX(SAL),SUM(SAL),AVG(SAL)
FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY DEPTNO,JOB;

ROLLUP 함수를 적용한 그룹화

SELECT DEPTNO,JOB,COUNT(*),MAX(SAL),SUM(SAL),AVG(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB);

ROLLUP 함수 명시한 열을 소그룹부터 대그룹의 순서로 각 그룹별 결과를 출력하고, 마지막에 총 데이터의 결과를 출력
- 각 부서의 직책별 사원수, 최고 급여, 급여 합, 평균 급여를 출력
ROLLUP 함수에는 그룹함수를 지정할 수 없음

CUBE 함수를 적용한 그룹화

SELECT DEPTNO,JOB,COUNT(*),MAX(SAL),SUM(SAL),AVG(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO,JOB)
ORDER BY DEPTNO,JOB;

ROLLUP함수를 사용할 때 보다 많은 결과가 나옴
지정한 열에서 가능한 조합의 결과를 모두 출력

ROLLUP 함수는 그룹화 순서대로 출력, 지정한 열 수에 따라 출력 순서

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

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. 전체 데이터 결과

ROLLUP 함수는 N개의 열을 지정하면 N+1개의 조합이 출력

CUBE 함수는 N개의 열을 지정하면 2^N개의 조합이 출력

CUBE 함수는 지정한 열이 많으면 조합이 너무 많아지기 때문에 그룹화 열 중 일부만 지정할 수 있음.

ROLLUP 함수 이용 2

SELECT DEPTNO,JOB,COUNT(*)
FROM EMP
GROUP BY DEPTNO,ROLLUP(JOB);

ROLLUP 함수 이용 3

SELECT DEPTNO,JOB,COUNT(*)
FROM EMP
GROUP BY JOB,ROLLUP(DEPTNO);

GROUPING SETS 함수

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

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

지정한 모든 열을 각각 대그룹으로 처리하여 출력을 도와줌

GROUPING SETS 함수를 사용하여 열별로 그룹으로 묶어 출력하기

SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO,JOB)
ORDER BY DEPTNO,JOB;

그룹화를 위해 지정한 열이 계층적으로 분류되지 않고 각각 따로 그룹화한 후 연산을 수행

그룹화 함수

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

GROUPING 함수

GROUPING 함수는 ROLLUP 또는 CUBE 함수를 사용한 GROUP BY 절에 그룹화 대상으로 지정한 열이 그룹화 된 상태로 결과가 집계되었는지 확인하는 데 사용.

GROUP BY 절에 명시된 열 중 하나를 지정할 수 있음

SELECT [조회할 열 1 이름], [열 2 이름], ...,[열 N이름]
GROUPING [GROUP BY 절에 ROLLUP 또는 CUBE에 명시한 그룹화 할 열 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식
GROUP BY ROLLUP 또는 CUBE[그룹화할 열];

'DB > Doit!오라클로 배우는 데이터베이스 입문_개념정리' 카테고리의 다른 글

DB 기초 개념 정리_6)데이터 처리와 가공을 위한 오라클 함수_DoIt!오라클로 배우는 데이터 베이스 입문 (0)	2024.06.02
DB 기초 개념 정리_5)더 정확하고 다양하게 결과를 출력하는 WHERE절과 연산자_DoIt!오라클로 배우는 데이터 베이스 입문 (0)	2024.05.27
DB 기초 개념 정리_4)SELECT문의 기본형식_DoIt!오라클로 배우는 데이터 베이스 입문 (0)	2024.05.26
DB 기초 개념 정리_2)관계형 데이터베이스와 오라클 데이터베이스_DoIt!오라클로 배우는 데이터 베이스 입문 (0)	2024.05.24
DB 기초 개념 정리_1)데이터베이스 개념 잡기_DoIt!오라클로 배우는 데이터 베이스 입문 (0)	2024.05.23

현재글DB 기초 개념 정리_7)다중행 함수와 데이터 그룹화_DoIt!오라클로 배우는 데이터 베이스 입문

Today :
Yesterday :

일	월	화	수	목	금	토
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

kimjw94