[Oracle/SQL] 3. GROUP BY | HAVING | ROLLUP | CUBE | SET OPERATION

목차

1. ORDER BY
2. GROUP BY와 HAVING
   2-1. GROUP BY
   2-2. HAVING
3. 집계함수
   3-1. ROLLUP
   3-2. CUBE
4. GROUPING 함수
5. SET OPERATION
   5-1. UNION
   5-2. UNION ALL
   5-3. INTERSECT
   5-4. MINUS
6. GROUPING SETS
7. 사용 예제

---

 

학습점검 Check

✅ GROUP BY 절에 대해 이해할 수 있다.
✅ GROUP BY 절의 동작 원리와 순서에 대해 이해할 수 있다.
✅ GROUP BY 절을 이용하여 데이터를 조회할 수 있다.
✅ HAVING 절에 대해 이해할 수 있다.
✅ HAVING 절의 동작 원리와 순서에 대해 이해할 수 있다.
✅ HAVING 절을 이용하여 데이터를 조회할 수 있다.
✅ ROLLUP 함수에 대해 이해할 수 있다.
✅ ROLLUP 함수를 이용하여 데이터를 조회할 수 있다.
✅ CUBE 함수에 대해 이해할 수 있다.
✅ CUBE 함수를 이용하여 데이터를 조회할 수 있다.
✅ ROLLUP 함수와 CUBE 함수의 공통점과 차이점에 대해 이해할 수 있다.
✅ GROUPING 함수에 대해 이해할 수 있다.
✅ GROUPING 함수를 이용하여 데이터를 조회할 수 있다.

---

 

❗ SELECT구문 실행순서
　5 : SELECT 컬럼명 AS 별칭, 계산식, 함수식
　1 : FROM 참조할 테이블명
　2 : WHERE 컬럼명 | 함수식 비교연산자 비교값
　3 : GROUP BY 그룹을 묶을 컬럼명
　4 : HAVING 그룹함수식 비교연산자 비교값
　6 : ORDER BY 컬럼명 | 별칭 | 컬럼순번 정렬방식 [NULLS FIRST | LAST]

 

---

 

1. ORDER BY

✅ ORDER BY(컬럼명 | 별칭 | 컬럼순번 정렬방식  [NULLS FIRST | LAST])

• SELECT한 컬럼에 대해 정렬할 때 작성하는 구문이다.
• SELECT 구문의 가장 마지막에 작성하며, 실행순서 역시 가장 마지막에 수행된다.

ORDER BY 1;

• 컬럼명 | 별칭 | 컬럼순번으로 명시할 수 있다. 컬럼순번은 SELECT절에 나열된 순서를 말한다.

 

• 정렬방식의 경우 오름차순(ASC)이 기본이며, 키워드 생략 가능하다.
• 단, 내림차순 정렬 시에는 DESC 명시해야 한다.

 

NULLS FIRST | NULLS LAST

• NULL 값을 먼저 혹은 나중에, 어느 곳에 배치할지에 대해서도 작성할 수 있다.

 

---

 

2. GROUP BY와 HAVING

2-1. GROUP BY

✅ GROUP BY(컬럼명 | 함수식, ...)
그룹함수가 적용될 그룹의 기준을 소괄호 () 안에 작성한다. 이 기준은 여러 개 나열할 수도 있다.

• 그룹함수는 단 1개의 결과값만 산출한다는 특징이 있다. 이때 그룹함수를 이용하여 여러 개의 결과값을 산출하기 위해서는 적용될 그룹의 기준을 GROUP BY절에 기술하여 사용해야 한다.
• 즉 GROUP BY절은 그룹함수와 함께 사용하는 절이다.

❗ GROUP BY에서는 별칭 사용 불가하다. 실행순서상 SELECT절 이전에 산출되기 때문이다.

-- DEPT_CODE 기준 부서별 인원수 조회
SELECT
　　　　COUNT(*) 그룹함수
　　　 , DEPT_CODE
  FROM EMPLOYEE
 GROUP BY DEPT_CODE;

-- DEPT_CODE, JOB_CODE 기준 같은 부서, 같은 직급 조회
SELECT
　　　　DEPT_CODE
　　　 , JOB_CODE
　　　 , SUM(SALARY)
　　　 , COUNT(*)
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
　　　　   , JOB_CODE
 ORDER BY 1;

 

---

 

2-2. HAVING

✅ HAVING(컬럼명 | 함수식 비교연산자 비교값)

• 그룹함수로 값을 구해올 대상 그룹에 대해 조건을 설정할 때 쓰인다.
• WHERE는 SELECT에 대한 조건, HAVING은 GROUP BY에 대한 조건을 다룬다.

• WHERE와 HAVING의 차이는 아래 예시에서 살펴볼 수 있다.

-- 300만원 이상의 월급을 받는 사원들 대상으로
-- 부서별 그룹 월급 평균 계산 WHERE 예시
SELECT
　　　　DEPT_CODE
　　　 , FLOOR(AVG(SALARY)) 평균
  FROM EMPLOYEE
 WHERE SALARY > 3000000
 GROUP BY DEPT_CODE;
 ORDER BY 1;

-- 모든 직원을 대상으로 부서별 월급 평균을 구한 뒤
-- 평균이 300만원 이상인 부서 조회 HAVING 예시
SELECT
　　　　DEPT_CODE
　　　 , FLOOR(AVG(SALARY)) 평균
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
HAVING FLOOR(AVG(SALARY)) > 3000000;
 ORDER BY 1;

 

---

 

3. 집계함수

❗ GROUP BY절에서만 사용하는 함수이다.

• 집계함수는 그룹별로 산출한 결과값의 집계를 계산한다.
• 종류로는 ROLLUP과 CUBE 두 가지가 있다.

 

3-1. ROLLUP 첫 번째 인자에 대한 집계

• 인자로 전달 받은 그룹 중 가장 먼저 지정한 그룹별 중간 집계 및 총 집계를 구한다.

SELECT
　　　　DEPT_CODE
　　　 , JOB_CODE
　　　 , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY ROLLUP(DEPT_CODE, JOB_CODE)
 ORDER BY 1;

ROLLUP

• ROLLUP은 첫 번째 인자에 대해 집계 진행한다.
• DEPT_CODE마다 중간 집계가 나온다. NULL값 두 개에 대해서도 합산한 모습을 확인할 수 있다.
• 가장 마지막 21번 행에는 총 집계가 자리한다.

 

3-2. CUBE 모든 인자에 대한 집계

• 그룹으로 지정된 모든 그룹에 대해 중간 집계 및 총 집계를 구한다.

SELECT
　　　　DEPT_CODE
　　　 , JOB_CODE
　　　 , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY CUBE(DEPT_CODE, JOB_CODE)
 ORDER BY 1;

CUBE

• CUBE는 앞선 ROLLUP과 비교했을 때 출력된 행의 개수가 더 많아진 것을 볼 수 있다.
• 각 DEPT_CODE마다 중간 집계가 자리한 것은 동일하다.
• ROLLUP과의 차이라면, JOB_CODE별 집계 또한 이어져 나온다: 18번~28번 행.
• 즉 CUBE는 전달 받은 모든 인자에 대해 집계를 진행한다.
• 가장 마지막 21번 행에는 역시 총 집계가 자리한다.

 

---

 

4. GROUPING 함수

❗ 집계함수 ROLLUP/CUBE와 함께 쓰인다.

• ROLLUP 또는 CUBE에 의한 집계 산출물이 인자로 전달 받은 컬럼 집합의 산출물이면 0을 반환하고, 아니면 1을 반환하는 함수이다.
• 즉 그룹이 된 상태면 0, 아니면 1을 반환한다.

SELECT
　　　　DEPT_CODE
　　　 , JOB_CODE
　　　 , SUM(SALARY)
　　　 , GROUPING(DEPT_CODE) "부서별그룹묶인상태"
　　　 , GROUPING(JOB_CODE) "직급별그룹묶인상태"
  FROM EMPLOYEE
 GROUP BY CUBE(DEPT_CODE, JOB_CODE)
 ORDER BY 1;

GROUPING, CUBE

SELECT
　　　　DEPT_CODE
　　　 , JOB_CODE
　　　 , SUM(SALARY)
　　　 , CASE
　　　　 WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 1 THEN '부서별합계'
　　　　 WHEN GROUPING(DEPT_CODE) = 1 AND GROUPING(JOB_CODE) = 0 THEN '직급별합계'
　　　　 WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 0 THEN '그룹별합계'
　　　　 ELSE '총합계'
　　　  END 구분
  FROM EMPLOYEE
 GROUP BY CUBE(DEPT_CODE, JOB_CODE)
 ORDER BY 1;

GROUPING, CUBE

---

 

5. SET OPERATION

• 집합연산(SET OPERATION)은 여러 SELECT 결과물을 하나의 쿼리로 만드는 연산자이다.
• 종류로는 UNION, UNION ALL, INTERSECT, MINUS가 있다.

 

5-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;

 

---

 

5-2. 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;

---

 

5-3. INTERSECT

• 여러 개의 SELECT 결과에서 공통 부분만 추출한다.
• 수학에서 교집합과 비슷하다.

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;

 

---

 

5-4. MINUS

• 선행 SELECT 결과에서 그 다음 SELECT 결과와 겹치는 부분을 제외한 나머지 부분만 추출한다.
• 수학에서 차집합과 비슷하다.

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;

 

---

 

6. GROUPING SETS

✅ GROUP BY GROUPING SETS()

• 그룹별로 처리된 여러 SELECT문을 하나의 결과로 합칠 때 사용한다.
• 집합연산자(SET OPERATOR), 즉 집합연산(SET OPERATION, UNION-UNION ALL-INTERSECT-MINUS) 결과와 동일한 결과를 얻을 수 있다.

SELECT
　　　　DEPT_CODE
　　　 , JOB_CODE
　　　 , MANAGER_ID
　　　 , FLOOR(AVG(SALARY))
  FROM EMPLOYEE
  GROUP BY GROUPING SETS((DEPT_CODE, JOB_CODE, MANAGER_ID)
　　　　　　　　　　　　　, (DEPT_CODE, MANAGER_ID)
　　　　　　　　　　　　　, (JOB_CODE, MANAGER_ID)
                                      );

 

---

 

7. 사용 예제

 

▶소속 직원이 없는 D3, D4, D7은 목록에서 제외된 모습이다.

▶DEPT_CODE가 NULL인 직원 2명에 대해서도 따로 출력되었다.

-- 직원 테이블에서 부서코드별 그룹을 지정하여
-- 부서코드, 그룹별 급여의 합계, 그룹별 급여의 평균(정수처리), 인원수
-- 조회하고 부서코드순으로 오름차순 정렬 ✅
SELECT
　　　　DEPT_CODE
　　　 , SUM(SALARY) 합계
　　　 , FLOOR(AVG(SALARY)) 평균 ROUND 반올림처리도 무방하다.
　　　 , COUNT(*) 인원수
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
 ORDER BY 1;

 

-- 직원 테이블에서 직급코드, 보너스를 받는 사원수를 조회하여
-- 직급코드순으로 오름차순 정렬 ✅
SELECT
　　　　JOB_CODE
　　　 , COUNT(BONUS)
  FROM EMPLOYEE
 GROUP BY JOB_CODE
 ORDER BY 1;

 

-- 직원 테이블에서 직급코드, 보너스를 받는 사원수를 조회하여
-- 직급코드순으로 오름차순 정렬
-- 단, 보너스를 받는 사람이 없는 직급코드의 경우 RESULT SET에서 제외 ✅✅
SELECT
　　　　JOB_CODE
　　　 , COUNT(*)
  FROM EMPLOYEE
 WHERE BONUS IS NOT NULL BONUS 안 받는 행은 모두 제외하는 조건!
 GROUP BY JOB_CODE
 ORDER BY 1;

 

▶ORDER BY는 가장 마지막에 실행되므로 SELECT절에서 명시한 별칭 사용 가능하다.

-- 직원 테이블에서 주민번호의 8번째 자리를 조회하여
-- 1이면 남, 2이면 여로 결과 조회하고
-- 성별별 급여 평균(정수처리), 급여 합계, 인원수를 조회한 뒤
-- 인원수로 내림차순 정렬 ✅✅
SELECT
　　　　DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') 성별
　　　 , FLOOR(AVG(SALARY)) 급여 평균
　　　 , SUM(SALARY) 급여 합계
　　　 , COUNT(*) 인원수
  FROM EMPLOYEE
 GROUP BY DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여')
 ORDER BY COUNT(*) 인원수 DESC;

 

-- 급여 합계가 가장 많은 부서의 부서코드와 급여 합계 조회 ✅✅✅
-- 메인 쿼리, 서브 쿼리
SELECT
　　　　DEPT_CODE
　　　 , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
　　　　　　　　　　　　 FROM EMPLOYEE
　　　　　　　　　　　  GROUP BY DEPT_CODE
　　　　　　　　　　　  );