[Oracle] GROUP BY, HAVING, 집합 연산자

주재완·2024년 2월 20일
dboracle
0

[Old] Oracle

목록 보기
4/10
post-thumbnail

Introduction

기존 포스팅까지 사용했던 SELECT문의 구조는 다음과 같습니다.

SELECT 컬럼1, 컬럼2, ...
FROM 테이블
WHERE 조건식
ORDER BY 컬럼 | 별칭 | 순서

이런 구조에서 GROUP BY와 HAVING절을 추가하고자 합니다. 또 RDBMS의 꽃인 JOIN을 하기 전 여러개의 쿼리문을 하나로 만들어주는 연산자인 집합 연산자에 대해서도 알아봅니다.

GROUP BY

그룹기준을 제시할 수 있는 구문입니다(그룹 조건을 어럿 지정하는 것도 가능합니다).

여러 개의 값들을 하나의 그룹으로 묶어서 처리하는 목적으로 사용됩니다.

사용 예시

전체사원을 하나의 그룹으로 묶어서 총 합을 구한 결과

SELECT SUM(SALARY)
FROM EMPLOYEE;

각 부서별 총 급여

SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

각 부서별 사원 수

SELECT DEPT_CODE, COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

-- 진행순서
SELECT DEPT_CODE, COUNT(*), SUM(SALARY) ------- 3
FROM EMPLOYEE ------- 1
GROUP BY DEPT_CODE ------- 2
ORDER BY DEPT_CODE; ------- 4

각 직급별 총사원수, 보너스를 받는 사원 수, 급여합, 평균급여, 최저급여, 최고급여 (정렬 : 직급 오름차순)

SELECT 
    JOB_CODE, 
    COUNT(*) AS "사원수",
    COUNT(BONUS) AS "보너스", 
    SUM(SALARY) AS "급여", 
    ROUND(AVG(SALARY)) AS "급여평균", 
    MIN(SALARY) AS "최저급여", 
    MAX(SALARY) AS "최고급여"
FROM EMPLOYEE
GROUP BY JOB_CODE
ORDER BY JOB_CODE;

GROUP BY 절에 함수식 역시 사용 가능합니다.

SELECT DECODE(SUBSTR(EMP_NO, 8, 1),
    '1', '남',
    '2', '여'), COUNT(*)
FROM EMPLOYEE
GROUP BY SUBSTR(EMP_NO, 8, 1);

GROUP BY 절에 여러 컬럼 기술도 가능합니다.

SELECT DEPT_CODE, JOB_CODE, COUNT(*), SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE, JOB_CODE
ORDER BY DEPT_CODE ASC, JOB_CODE ASC;

HAVING

우선 예시를 살펴보면서 익혀보도록 합니다.

각 부서별 평균 급여 조회(부서코드, 평균급여)를 할 때 쿼리문은 아래와 같습니다.

SELECT DEPT_CODE, ROUND(AVG(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE;

여기서 각 부서별 평균 급여가 300만원 이상인 부서들만 조회(부서코드 평균급여)한다고 할 때 아래와 같이 작성하기 쉽습니다.

SELECT DEPT_CODE, ROUND(AVG(SALARY))
FROM EMPLOYEE
WHERE AVG(SALARY) >= 3000000
GROUP BY DEPT_CODE;

하지만 실제 실행하면 다음과 같은 결과를 얻게 됩니다.

ORA-00934: group function is not allowed here

AVG 같은 그룹 함수는 사용할 수 없다고 에러가 뜹니다. 즉, 그룹함수를 쓰기 위한 다른 것을 사용해야 된다는 것을 알 수 있습니다. 이때 사용하는 것이 바로 HAVING이고, 다음과 같이 사용하면 됩니다.

SELECT DEPT_CODE, ROUND(AVG(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING AVG(SALARY) >= 3000000;

물론 WHERE 절 조건식 내부에 그룹함수를 사용하지만 않는다면, 혼용이 가능합니다. 단 순서를 잘 지켜주어야 하고(WHERE - GROUP BY - HAVING 순), 정리 부분에서 그 순서를 다시한번 보도록 하겠습니다.

예시

직급별 직급코드, 총 급여합(단, 직급별 급여합이 1000만원 이상인 직급만 조회)

SELECT JOB_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY JOB_CODE
HAVING SUM(SALARY) >= 10000000;

부서별 보너스를 받는 사원이 없는 부서의 코드

SELECT DEPT_CODE
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING COUNT(BONUS) = 0;

순서 정리

집합 연산자를 들어가기 전, 지금까지 포스팅한 절들의 순서는 아래와 같이 정해집니다.

SELECT * | 조회하고싶은 컬럼 AS 별칭 | 함수식 | 산술연산식	------ 5
FROM 조회하고자하는 테이블 | DUAL		------ 1
WHERE 조건식 (연산자들을 활용하여 기술)	------ 2
GROUP BY 그룹기준이 되는 컬럼 | 함수식	------ 3
HAVING 조건식(그룹함수를 가지고 기술)	------ 4
ORDER BY 컬럼 | 별칭 | 순서 [ASC | DESC] [NULLS FIST | NULLS LAST]	------ 6

작성 순서는 아래와 같습니다.

SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

실행 순서는 아래와 같습니다.

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

집합 연산자(Set Operation)

여러개의 쿼리문을 하나의 쿼리문으로 만드는 연산자입니다.

(공통)주의할 점

  • 컬럼의 개수는 똑같아야합니다.
  • 컬럼자리마다 동일한 타입으로 기술해야 합니다.
  • 정렬하고 싶다면 모든 집합 연산 이후 ORDER BY는 마지막 줄에 기술합니다.

UNION

쿼리문에 대한 OR 연산과 비슷합니다. 합집합으로 두 쿼리문을 수행한 결과값을 더합니다.

부서코드가 D5인 사원 또는 급여가 300만원 초과인 사원들의 사번, 이름, 부서코드, 급여 조회를 하는 하는 쿼리를 작성할 때, 일반적으로 아래와 같이 작성합니다.

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' OR SALARY > 3000000;

이를 UNION으로 작성해봅니다. 아래와 같은 과정으로 작성이 가능합니다.

  1. 부서코드가 D5인 사원의 사번, 이름, 부서코드, 급여 조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';
  1. 급여가 300만원 초과인 사원들의 사번, 이름, 부서코드, 급여 조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000;
  1. UNION으로 합하기
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
UNION
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000;

INTERSECT

쿼리문에 대한 AND 연산과 비슷합니다. 교집합 개념으로 두 쿼리문을 수행한 결과값에 중복된(공통) 결과값을 반환합니다. 예시를 보도록 하겠습니다.

부서코드가 D5이면서 급여도 300만원 초과인 사원들의 사번, 이름, 부서코드, 급여 조회하려면 아래와 같습니다.

  1. AND
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' AND SALARY > 3000000;
  1. INTERSECT
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
INTERSECT
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000;

UNION ALL

합집합 + 교집합의 개념으로 UNION과 다르게 중복되는게 두번 표현될 수 있습니다. 즉 여러개의 쿼리 결과를 그냥 무조건 다 더하는 연산자입니다.

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
UNION ALL
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000
ORDER BY EMP_ID;

MINUS

선행 결과값에 후행 결과값을 뺀 나머지로 차집합을 의미합니다.

부서코드가 D5인 사원들 중 급여가 300만원 초과인 사원들을 제외하고 조회한다면 아래와 같습니다.

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
MINUS
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000
ORDER BY EMP_ID;
profile
주재완
안녕하세요! 언제나 탐구하고 공부하는 개발자, 주재완입니다.
이전 포스트

[Oracle] FUNCTION(2)

다음 포스트

[Oracle] JOIN

0개의 댓글