4. ORACLE 함수(Function) :: 그룹 함수 정리

그룹 함수

테이블의 데이터를 집계하는 함수, 집계 결과는 1개만 출력이 되기 때문에 컬럼 선택에 제한이 있다.

(1) 기본 그룹함수

구분	설명(그룹=선택한 컬럼)	비고
SUM(컬럼)	합계
AVG(컬럼)	평균
COUNT(컬럼/*)	총 개수(ROW=데이터수)	- 컬럼 : null인 row를 제외하고 계산  - * : 컬럼에 한개라도 값이 있으면 포함해서 계산
MAX(컬럼)	최대값	없는 데이터면 null 출력
MIN(컬럼)	최소값

• sum, avg

**@ 다른 컬럼을 추가할 수 없다: 한번에 한개만 조회 가능**
select sum(salary), **emp_name**
from employee;
-- 에러발생: "ORA-00937: 단일 그룹의 그룹 함수가 아닙니다."

**@ 단, 그룹합수끼리는 추가조회가 가능하다**
select sum(salary), avg(salary)
from employee
where dept_code = 'D5';

@ **where 절을 사용하면 필터된 row만으로 계산할 수 있다
		-> 조건에 맞는 데이터를 선별해서 계산 진행**
-- 부서가 D5인 사원 급여 합계를 구하자
select sum(salary)
from employee
**where dept_code = 'D5';

@ null 값은 그냥 제외하고 계산하므로 예외처리를 해줘야한다.**
select sum(bonus), avg(nvl(bonus,0))
from employee;

• count

@ (컬럼명) 입력시 부서 null인 데이터는 아예 제외됨
select count(*) as 사원수, count(dept_code)
from employee;

-- 보너스를 받는 사원의 수
SELECT COUNT(*)
FROM EMPLOYEE
WHERE BONUS IS NOT NULL;
-- 같은 값
SELECT COUNT(BONUS)
FROM EMPLOYEE;

 

(2) 그룹을 새로 만드는 방법 :: GROUP BY

여러개의 결과값을 산출하기 위해 ROW를 특정 컬럼을 기준으로 한개 그룹으로 설정하는 것

from 뒤에 작성 → GROUP BY 칼럼명

@ 작성법
SELECT 컬럼명
FROM 테이블명
[WHERE 조건식]
[**GROUP BY 컬럼명[, 컬럼명,...]**]

-- 부서별 급여합계 조회하기
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;
-- 부서별 평균 급여, 합계, 사원수 조회
SELECT NVL(DEPT_CODE, '인턴'), AVG(SALARY), SUM(SALARY), COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

@ 다수의 컬럼 설정도 가능하다: 직책코드와 부서코드가 같은 사람이 하나의 그룹으로 설정됨
SELECT JOB_CODE,  DEPT_CODE, COUNT(*)
FROM EMPLOYEE
GROUP BY JOB_CODE, DEPT_CODE;

 

(3) GROUP BY의 조건문 :: HAVING

• WHERE절에는 그룹함수를 쓸 수 없음 → WHERE SALARY = MAX(SALARY); ❌
  → 그룹 설정시 추가 조건을 HAVING을 통해 기술

@ 작성위치: GROUP BY 밑
GROUP BY 칼럼명
HAVING 조건식

-- 부서 **인원수가 3명 이상**인 부서
SELECT DEPT_CODE, **COUNT(*)**
FROM EMPLOYEE
GROUP BY DEPT_CODE
**HAVING COUNT(*) >= 3;**

-- 매니저가 관리하는 사원이 2명 이상인 아이디
****SELECT MANAGER_ID, COUNT(*)
FROM EMPLOYEE
WHERE MANAGER_ID IS NOT NULL
GROUP BY MANAGER_ID
HAVING COUNT(*) >= 2;
-- 같은 결과
SELECT MANAGER_ID, COUNT(MANAGER_ID)
FROM EMPLOYEE
GROUP BY MANAGER_ID
HAVING COUNT(MANAGER_ID) >= 2;

 

(4) ROLLUP과 CUBE

특별히 지정한 칼럼에 한해 추가적으로 해당 칼럼의 집계 결과 반환

즉, 그룹을 나누지 않았을 때의 집계까지 한번에 진행할 때 사용

• 매개변수가 하나일 때는 롤업과 큐브의 실행 면에서는 차이 없음
  → A, 전체 결과 출력
  • 롤업은 테이블 하단, 큐브는 테이블 상단에 결과 출력

  -- 기본 GROUP일 때
  -- 부서별 인원수
  select dept_code, count(*)
  from employee
  where dept_code is not null
  group by dept_code;
  -- 부서가 있는 모든 사원수
  select count(*)
  from employee
  where dept_code is not null;
  
  -- 위 두개를 한번에 출력하고자 할 때 사용한다.
  -- ROLLUP
  select dept_code, count(*)
  from employee
  where dept_code is not null
  group by rollup(dept_code);
  -- CUBE
  select dept_code, count(*)
  from employee
  where dept_code is not null
  group by cube(dept_code);
  

• 매개변수가 두개 이상일 때
  • ROLLUP(A, B) → A && B, A, 전체 결과를 맨 아래에 출력
  • CUBE(A,B) → A && B, A, B, 전체 결과를 맨 위에 출력

@ ROLLUP
select dept_code, job_code, count(*)
from employee
where dept_code is not null
group by rollup(dept_code, job_code);
=> dept && job, dept, all

--학번 A112113인 학생의 년도, 학기별 평점
select nvl(substr(term_no, 1, 4), ' ') 년도, nvl(substr(term_no, 5, 2), ' ') 학기, 
				round(avg(point), 1) 평점 --그룹으로 묶었기 때문에 일반 컬럼은 사용이 어렵다
from tb_grade
where student_no = 'A112113'
group by rollup (substr(term_no, 1, 4), substr(term_no, 5, 2))
order by substr(term_no, 1, 4);

@ CUBE
select dept_code, job_code, count(*)
from employee
where dept_code is not null
group by CUBE(dept_code, job_code);
=> dept && job, dept, job, all

 

결과값에 별칭 붙이기 :: GROUPING

null일때 1, 아닐 때 0를 의미

해당 그룹으로 분류된 것인지 확인할 때 사용

• 예시 sql문

SELECT 
    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) = 1 AND GROUPING(JOB_CODE) = 1 THEN '총 인원수'
        ELSE '직책부서별 인원수'
    END AS 구분,
    COUNT(*), DEPT_CODE, JOB_CODE
FROM EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1;

• 출력 결과
  • 지정한 그룹별로 모여 가상 컬럼이 만들어져 인원수를 세는 것을 확인할 수 있다.

GROUPING SETS

여러 select문을 하나로 합친 결과를 원할 때 사용하는 것

• 조금더 다양한 그룹화 조합을 계산할 수 있다.
  => 다양한 집계 결과가 필요한 분석 수행시 사용한다.
• 예시 sql

SELECT
    column1,
    column2,
    SUM(measure_column) AS total
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (column1, column2), --column1과 column2로 그룹화
        (column1), --column1만으로 그룹화
        (column2),--column2만으로 그룹화
        () --전체 합계
    );