[Chapter 2] 07 다중행 함수와 데이터 그룹화

희진·2024년 4월 3일
Databasedbdoit데이터베이스오라클로배우는데이터베이스입문

Do it! 오라클로 배우는 데이터베이스 입문

목록 보기
6/8
post-thumbnail

07-1 다중행 함수

여러 행을 바탕으로 하나의 결과 값을 도출해내기 위해 사용하는 함수

  • SUM 함수를 사용하여 급여 합계 출력하기
SELECT SUM(SAL)
FROM EMP;
SUM(SAL)
29025
  • SUM 함수를 사용하여 사원 이름과 급여 합계 출력하기 (에러)
SELECT ENAME, SUM(SAL)
FROM EMP;

1행에 오류: ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

  • SUM : 지정한 데이터의 합 변환
  • COUNT : 지정한 데이터의 개수 반환
  • MAX : 지정한 데이터 중 최댓값 반환
  • MIN : 지정한 데이터 중 최솟값 반환
  • AVG : 지정한 데이터의 평균값 반환

SUM

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

COUNT

결과 행의 개수를 출력

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

MAX, MIN

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

AVG

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

GROUP BY절

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

집합 연산자를 사용하여 각 부서별 평균 급여 출력하기

SELECT AVG(SAL), '10' AS DEPTNO FROM EMP WHERE DEPTNO = 10
UNION ALL
SELECT AVG(SAL), '20' AS DEPTNO FROM EMP WHERE DEPTNO = 20
UNION ALL
SELECT AVG(SAL), '30' AS DEPTNO FROM EMP WHERE DEPTNO = 30;
AVG(SAL)DEPTNO
2916.6666710
217520
1566.6666730

GROUP BY절의 기본 사용법

별칭 사용 가능

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY [그룹화할 열을 지정(여러 개 지정 가능)]
ORDER BY [정렬하려는 열 지정]
  1. GROUP BY를 사용하여 부서별 평균 급여 출력하기
SELECT AVG(SAL), DEPTNO
  2  FROM EMP
  3  GROUP BY DEPTNO;
AVG(SAL)DEPTNO
1566.6666730
217520
2916.6666710
  1. 부서 번호 및 직책별 평균 급여로 정렬하기
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;
DEPTNOJOBAVG(SAL)
10CLERK1300
10MANAGER2450
10PRESIDENT5000
20ANALYST3000
20CLERK950
20MANAGER2975
30CLERK950
30MANAGER2850
30SALESMAN1400

GROUP BY절을 사용할 때 유의점

다중행 함수를 사용하지 않은 일반 열은 GROUP BY절에 명시하지 않으면 SELECT절에서 사용할 수 없음

  • GROUP BY절에 없는 열을 SELECT절에 포함했을 경우 (에러)
SELECT ENAME, DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

1행에 오류: ORA-00979: GROUP BY 표현식이 아닙니다.

07-3 GROUP BY절과 HAVING절

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

SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL) >= 2000
ORDER BY DEPTNO, JOB;
DEPTNOJOBAVG(SAL)
10MANAGER2450
10PRESIDENT5000
20ANALYST3000
20MANAGER2975
30MANAGER2850

HAVING절의 기본 사용법

별칭 사용 가능

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

HAVING절을 사용할 때 유의점

출력 결과를 제한하기 위해 HAVING을 사용하지 않고 조건식을 WHERE에 명시하면 에러 발생

  • HAVING절 대신 WHERE절을 잘못 사용했을 경우 (에러)
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
WHERE AVG(SAL) >= 2000
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;

3행에 오류: ORA-00934: 그룹 함수는 허가되지 않습니다

WHERE절과 HAVING절의 차이점

  • WHERE절과 HAVING절을 모두 사용한 경우
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
WHERE SAL <= 3000 -- 먼저 실행
GROUP BY DEPTNO, JOB
HAVING AVG(SAL) >= 2000 -- 나중에 실행
ORDER BY DEPTNO, JOB;
DEPTNOJOBAVG(SAL)
10MANAGER2450
20ANALYST3000
20MANAGER2975
30MANAGER2850

07-4 그룹화 (관련) 함수

ROLLUP, CUBE

그룹화 데이터의 합계를 출력할 때 유용하게 사용

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY ROLLUP [그룹화 열 지정(여러 개 지정 가능)];
SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY CUBE [그룹화 열 지정(여러 개 지정 가능)];
  1. 기존 GROUP BY절만 사용한 그룹화
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;

GROUP BY

  1. ROLLUP/CUBE 함수를 적용한 그룹화
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;
ROLLUP CUBE
  1. DEPTNO를 먼저 그룹화한 후 ROLLUP 함수에 JOB 지정하기
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY DEPTNO, ROLLUP(JOB);
  1. JOB을 먼저 그룹화한 후 ROLLUP 함수에 DEPTNO 지정하기
SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY JOB, ROLLUP(DEPTNO);
DEPTNO를 먼저 그룹화 JOB을 먼저 그룹화

GROUPING SETS

같은 수준의 그룹화 열이 여러 개일 때 각 열별 그룹화를 통해 결과 값을 출력

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

GROUPING SETS

그룹화 함수

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

GROUPING

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
GROUPING [GROUP BY절에 ROLLUP 또는 CUBE에 명시한 그룹화 할 열 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY ROLLUP 또는 CUBE [그룹화할 열];
  1. DEPTNO, JOB열의 그룹화 결과 여부를 GROUPING 함수로 확인하기
SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL),
GROUPING(DEPTNO),
GROUPING(JOB)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

GROUPING

  • 0 : GROUPING 함수에 지정한 열이 그룹화되었음
  • 1 : GROUPING 함수에 지정한 열이 그룹화되지 않았음
  1. DECODE문으로 GROUPING 함수를 적용하여 결과 표기하기
SELECT DECODE(GROUPING(DEPTNO), 1, 'ALL_DEPT', DEPTNO) AS DEPTNO,
       DECODE(GROUPING(JOB), 1, 'ALL_JOB', JOB) AS JOB,
       COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

GROUPING

GROUPING_ID

SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
GROUPING_ID [그룹화 여부를 확인할 열(여러 개 지정 가능)]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY ROLLUP 또는 CUBE [그룹화할 열];
그룹화 된 열그룹화 비트 벡터최종 결과
A, B0 00
A0 11
B1 02
없음1 13
SELECT DEPTNO, JOB, COUNT(*), SUM(SAL),
       GROUPING(DEPTNO),
       GROUPING(JOB),
       GROUPING_ID(DEPTNO, JOB)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

GROUPING_ID

LISTAGG

  • 오라클 11g 버전부터 사용 가능
  • 그룹에 속해 있는 데이터를 가로로 나열할 때 사용
SELECT [조회할 열1 이름], [열2 이름], ..., [열N 이름]
LISTAGG([나열할 열(필수)], [각 데이터를 구분하는 구분자(선택)])
WITHIN GROUP(ORDER BY 나열할 열의 정렬 기준 열(선택)
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
  1. GROUP BY절로 그룹화하여 부서 번호화 사원 이름 출력하기
SELECT DEPTNO, ENAME
FROM EMP
GROUP BY DEPTNO, ENAME;
  1. 부서별 사원 이름을 나란히 나열하여 출력하기
SELECT DEPTNO,
	   LISTAGG(ENAME, ', ')
       WITHIN GROUP(ORDER BY SAL DESC) AS ENAMES
FROM EMP
GROUP BY DEPTNO;
BEFORE LISTAGG AFTER LISTAGG

PIVOT, UNPIVOT

  • 오라클 11g 버전부터 사용 가능
  • PIVOT : 기존 테이블 행을 열로 바꿈
  • UNPIVOT : 기존 테이블 열을 행으로 바꿈
  1. 부서별/직책별로 그룹화하여 최고 급여 데이터 출력하기
SELECT DEPTNO, JOB, MAX(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;

  1. PIVOT 함수를 사용하여 직책별/부서별 최고 급여를 2차원 표 형태로 출력하기(1)
SELECT *
FROM(SELECT DEPTNO, JOB, SAL
     FROM EMP)
PIVOT(MAX(SAL)
      FOR DEPTNO IN (10, 20, 30)
      )
ORDER BY JOB;

  1. PIVOT 함수를 사용하여 부서별/직책별 최고 급여를 2차원 표 형태로 출력하기(2)
SELECT *
FROM(SELECT JOB, DEPTNO, SAL
     FROM EMP)
PIVOT(MAX(SAL)
     FOR JOB IN ('CLERK' AS CLERK,
                 'SALESMAN' AS SALESMAN,
                 'PRESIDENT' AS PRESIDENT,
                 'MANAGER' AS MANAGER,
                 'ANALYST' AS ANALYST)
                 )
ORDER BY DEPTNO;

  1. DECODE문을 활용하여 PIVOT 함수와 같은 출력 구현하기
SELECT DEPTNO,
       MAX(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
       MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
       MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
       MAX(DECODE(JOB, 'MANAGER', SAL)) AS "MANAGER",
       MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST"
 FROM EMP
 GROUP BY DEPTNO
 ORDER BY DEPTNO;

  1. UNPIVOT 함수를 사용하여 열로 구분된 그룹을 행으로 출력하기
SELECT *
FROM(SELECT DEPTNO,
            MAX(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
            MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
            MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
            MAX(DECODE(JOB, 'MANAGER', SAL)) AS "MANAGER",
            MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST"
     FROM EMP
     GROUP BY DEPTNO
     ORDER BY DEPTNO)
UNPIVOT(SAL FOR JOB IN (CLERK, SALESMAN, PRESIDENT, MANAGER, ANALYST))
ORDER BY DEPTNO, JOB;

profile
희진
열심히 살겠습니다
이전 포스트

[Chapter 2] 06 데이터 처리와 가공을 위한 오라클 함수

다음 포스트

[Chapter 2] 08 여러 테이블을 하나의 테이블처럼 사용하는 조인

0개의 댓글