03_OracleDB SQL : GROUP BY와 HAVING, 집계함수, 집합연산

 

 

 

 

 

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

 

 

 

 

---

ORDER BY 절

SELECT 한 컬럼을 정렬할 때 작성하는 구문

SELECT 구문의 가장 마지막에 작성하며, 실행순서도 가장 마지막에 수행됨.

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

 

 

---

 

 

 

---

GROUP BY 절

 

그룹함수를 이용하여 여러 개의 결괏값을 산출하기 위해서는

그룹함수가 적용될 그룹의 기준을 GROUP BY 절에 기술하여 사용.

GROUP BY 절에서는 SELECT 절의 별칭 사용 불가

 

 

---

 

• 그룹함수가 적용될 컬러명을 GROUP BY절에 기술

ex) DEPT_CODE 기준으로 그루핑
SELECT
       COUNT(*)
     , DEPT_CODE
  FROM EMPLOYEE
 GROUP BY DEPT_CODE;


ex) DEPT_CODE, JOB_CODE 기준으로 그루핑
SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY)
     , COUNT(*)
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
        , JOB_CODE
 ORDER BY 1;

 

 

 

• 부서 코드별 그룹을 지정하여 부서코드, 그룹별 급여의 합계, 그룹별 급여의 평균(정수 처리) 인원수를 조회하고 부서코드 순으로 오름차순 정렬하기

SELECT
       DEPT_CODE
     , SUM(SALARY) 합계
     , FLOOR(AVG(SALARY)) 평균
     , COUNT(*)
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
 ORDER BY DEPT_CODE;

 

1) 부서 코드별 그룹 지정을 위해 SELECT절에 부서코드 컬러명 기술
2)  그룹별 급여의 합계는 함수 SUM을 이용하여 작성

3) 그룹별 급여 평균은 평균을 구하는 AVG함수를 사용하고 정수처리를 위해 FLOOR함수와 같이 사용
4) 인원수를 조회하기 위해서 COUNT(*)작성
5)  FROM절에 테이블명 작성 후 GROUP BY절에 부서 코드 그룹 지정
6) 부서코드 순으로 오름차순 정렬을 위해 ORDER BY절도 사용

 

 

---

 

• 직급코드별로 보너스를 받는 사원 수를 조회하여 직급코드 순으로 오름차순 정렬하기.

SELECT
       JOB_CODE
     , COUNT(BONUS)
  FROM EMPLOYEE
 GROUP BY JOB_CODE
 ORDER BY JOB_CODE;

 

 

 

• 직급코드별로 보너스를 받는 사원 수를 조회하여 직급코드 순으로 오름차순 정렬하기. 단, 보너스를 받는 사람이 없는 직급코드의 경우 RESULT SET에서 제외한다.

SELECT
       JOB_CODE
     , COUNT(BONUS)
  FROM EMPLOYEE
 WHERE BONUS IS NOT NULL
 GROUP BY JOB_CODE
 ORDER BY JOB_CODE;

 

 

반응형

 

• 주민번호의 8번째 자리를 조회하여 1이면 남, 2이면 여로 결과 조회하고, 성별별 급여 평균(정수 처리), 급여 합계, 인원수를 조회한 뒤 인원수로 내림차순 정렬하세요.

SELECT
       DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') AS 성별
     , FLOOR(AVG(SALARY)) 평균
     , SUM(SALARY) 합계
     , COUNT(*) 인원수
  FROM EMPLOYEE
  GROUP BY DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여')
  ORDER BY 인원수 DESC;

 

1) 문제의 조건으로 주민번호 조회하기 위해  선택함수인 DECODE와 문자함수인 SUBSTR을 사용하여 작성

2) 급여 평균과 정수처리를 위해 FLOOR와 AVG사용

3) 급여 합계는 SUM으로 계산

4) 행의 갯수를 헤아려서 리턴하는 COUNT(*) 그룹함수를 이용하여 인원수 출력

5) FROM절에 테이블명 작성

6) SELECT절에 그룹 함수 외 컬럼명이 GROUP BY절에 작성되지 않으면 오류 발생하기 때문에 

     GROUP BY절에 DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여')을 작성한다.

     GROUP BY절에는 별칭사용이 불가하므로 별칭 제외하여 작성하기!

7) 인원수 내림차순 정렬은 ORDER BY에 작성 (별칭사용 가능)

     DESC(내림차순), ASC(오름차순)

 

 

 

 

 

 

---

HAVING 절

 

그룹 함수로 구해올 그룹에 대해 조건을 설정할 때 사용한다.

HAING 컬럼명 | 함수식 비교연산자 비교값

 

---

 

• 모든 직원을 대상으로 부서별 월급 평균을 구한 뒤 평균이 300만원 이상인 부서 조회

SELECT
       DEPT_CODE
     , FLOOR(AVG(SALARY)) 평균
  FROM EMPLOYEE
 GROUP BY DEPT_CODE
HAVING FLOOR(AVG(SALARY)) >= 3000000
 ORDER BY 1;

 

 

 

 

 

---

집계 함수

GROUP BY 절에서만 사용하는 함수

ROLLUP 함수 , CUBE 함수 

 

 

---

 

1 .  ROLLUP 함수

• 그룹별로 중간 집계 처리를 하는 함수
• 그룹별로 묶인 값에 대한 중간 집계와 총집계를 구할 때 사용
• 그룹별로 계산된 결과 값들에 대한 총집계가 자동으로 추가됨.
• 인자로 전달한 그룹 중에서 가장 먼저 지정한 그룹별 합계와 총합계를 구함.

작성예시
SELECT
       JOB_CODE
     , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY ROLLUP(JOB_CODE)
 ORDER BY 1;
 

DEPT_CODE의 합계와 총 합계만 구함.
 SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY ROLLUP(DEPT_CODE, JOB_CODE)
 ORDER BY 1;

 

 

 

2 . CUBE 함수

• 그룹별 산출한 결과를 집계하는 함수
• 그룹으로 지정된 모든 그룹에 대한 집계와 총합계를 구함.

작성 예시
SELECT
       JOB_CODE
     , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY CUBE(JOB_CODE)
 ORDER BY 1; 


DEPT_CODE, JOB_CODE의 집계와 총 합계를 구함.
SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY)
  FROM EMPLOYEE
 GROUP BY CUBE(DEPT_CODE, JOB_CODE)
 ORDER BY 1;

 

 

3 . GROUPING 함수

• ROLLUP이나 CUBE에 의한 산출물이 인자로 전달받은 컬럼 집합의 산출물이면 0을 반환하고, 아니면 1을 반환하는 함수

SELECT
       DEPT_CODE
     , JOB_CODE
     , SUM(SALARY)
     , COUNT(*)
     , GROUPING(DEPT_CODE) "부서별 그룹 묶인 상태"
     , GROUPING(JOB_CODE) "직급별 그룹 묶인 상태"
  FROM EMPLOYEE
 GROUP BY CUBE(DEPT_CODE, JOB_CODE)
 ORDER BY 1;
 
 SELECT
       NVL(DEPT_CODE, '부서없음')
     , JOB_CODE
     , SUM(SALARY)
     , CASE
         WHEN GROUPING(NVL(DEPT_CODE, '부서없음')) = 0 AND GROUPING(JOB_CODE) = 1 THEN '부서별합계'
         WHEN GROUPING(NVL(DEPT_CODE, '부서없음')) = 1 AND GROUPING(JOB_CODE) = 0 THEN '직급별합계'
         WHEN GROUPING(NVL(DEPT_CODE, '부서없음')) = 0 AND GROUPING(JOB_CODE) = 0 THEN '그룹별합계'
         ELSE '총합계'
       END 구분
  FROM EMPLOYEE
 GROUP BY CUBE(NVL(DEPT_CODE, '부서없음'), JOB_CODE)
 ORDER BY 1;

 

 

 

 

 

---

집합 연산 (SET OPERATION )

UNION, UNION ALL, INTERSECT, MINUS 

 

 

---

 

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;

 

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;

 

 

 

 

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;

 

 

 

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;

 

5 . GROUPING SETS 

• 그룹별로 처리된 여러 개의 SELECT문을 하나로 합칠 때 사용함.
• SET OPERATION과 결과 동일

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