[ SQL ] 오라클 다중행함수(그룹함수) 와 GROUP_BY절

 

하루입니다.

 

 

---

 

오라클 내장함수란?

SQR 작성에 유용한 기능을 제공하는 함수. DBMS 제품마다 조금씩 차이가 있다. 단일행함수와 다중행함수로 나뉜다.

 

다중행함수(그룹함수)

• 행그룹 (테이블의 모든 행, 조회된 모든 행, 특정 컬럼의 값이 같은 것끼리 그룹핑된 것)에 대해서 연산결과를 하나 반환하는 함수다.
• MIN(컬럼 혹은 표현식) : 행그룹에서 지정된 컬럼의 최소값을 반환한다. (NULL은 무시)
• MAX(컬럼 혹은 표현식) : 행그룹에서 지정된 컬럼의 최대값을 반환한다. (NULL은 무시)
• SUM(컬럼 혹은 표현식) : 행그룹에서 지정된 컬럼의 합계를 반환한다. (NULL은 무시)
• AVG (컬럼 혹은 표현식) : 행그룹에서 지정된 컬럼의 평균을 반환한다. (NULL은 무시)
• COUNT (컬럼 혹은 표현식) : 행그룹에서 지정된 컬럼의 값이 NULL이 아닌 행의 갯수를 반환한다.
• COUNT(*) : 행그룹에서 모든 행의 갯수를 반환한다.

 

 

• 직원 테이블에서 급여를 10000 이하로 받는 직원 수

SELECT COUNT(*)
FROM EMPLOYEES
WHERE SALARY < 10000;

 

• 부서 테이블 데이터를 장소 아이디에 따라 묶고 갯수를 조회한다.

SELECT LOCATION_ID, COUNT(*)
FROM DEPARTMENTS
GROUP BY LOCATION_ID;

 

• SELECT에서는 그루핑 된 LOCATION_ID만 사용 가능하다. 
• 행그룹을 생성하기 위해서 GROUP_BY절에 사용했던 컬럼명 혹은 표현식만 SELECT절에서 그룹함수와 같이 사용할 수 있다.

 

ORA-00979 오류

 

 

• 급여등급별 사원수 조회하기
• 사원 테이블과 급여등급 테이블을 조인하고, 급여등급 테이블의 등급이 같은 행끼리 행그룹을 생성한다.

SELECT S.GRADE, COUNT(*) GRADE_CNT
FROM EMPLOYEES E, SALARY_GRADE S
WHERE E.SALARY >= S.MIN_SALARY AND E.SALARY <= S.MAX_SALARY
GROUP BY S.GRADE
ORDER BY S.GRADE;

코드해석

FROM        사원테이블과 급여등급 테이블을 조인하고

WHERE       E.급여는 S.최소급여 이상 S.최대급여 이하여야 한다.

GROUP BY   S.등급에 따라 그룹핑하며

SELECT       등급, 등급에 따른 사원수 센다.

ORDER BY   등급에 따라 조회한다.

 

 

이런 결과가 나옴.

 

 

• 그런데, 등급은 어디부터 어디까지 있는 거지? A부터 E까지? A부터 F까지? 아니면 S부터 F까지?
• SALARY_GRADE의 GRADE와 GRADE_CNT를 연결시키면 되지 않을까? 그런데 어떻게?
• 가상의 테이블을 만든다.

 

SELECT Y.GRADE, NVL(X.GRADE_CNT, 0) CNT
FROM (SELECT S.GRADE, COUNT(*) GRADE_CNT -- 이게 이 가상테이블 이름.
      FROM EMPLOYEES E, SALARY_GRADE S
      WHERE E.SALARY >= S.MIN_SALARY AND E.SALARY <= S.MAX_SALARY
      GROUP BY S.GRADE)X, SALARY_GRADE Y
WHERE X.GRADE(+) = Y.GRADE
ORDER BY Y.GRADE ASC;

코드해석

FROM 직원들을 급여등급에 따라 나누고 수를 센 GRADE_CNT 가상테이블과 급여등급 테이블을 조인하고

WHERE X.GRADE에 (+). 왜냐면 Y.GRADE에 행이 더 있다면 X에 NULL행을 추가해야 하니까. 

SELECT Y.GRADE(모든 등급을 보기 위해), X.GRADE_CUT에 값이 있다면 그게 나오게 하고 없다면 0을 반환한다. 

ORDER BY Y.GRADE

 

 

결과

시각적 참고자료

 

 

 

 

GROUP BY, ROLLUP, GROUPING SETS

• 집계할 때 사용된다.
• 부서아이디 50, 80인 직원들을 GROUP BY 부서아이디, 직업아이디로 묶고, COUNT했다.
• 결과는 50번 부서의 SH직업은 20명, ST직업은 20명, STMAN직업은 5명 ... 이런 식으로 나온다.

SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (50, 80)
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY DEPARTMENT_ID, JOB_ID;

GROUP BY 결과

 

 

• 부서아이디 50, 80인 직원들을 GROUP BY ROLLUP 부서아이디, 직업아이디로 묶고, COUNT했다.
• 위와 같은 결과지만 소계가 추가됐다. 부서아이디 50번의 직업아이디가 있는 사람의 수는 45, 부서아이디 80번의 직업아이디가 있는 사람의 수는 34, 총합 79.

SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (50, 80)
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID, JOB_ID;

ROLLUP 결과

 

 

• 부서아이디 50, 80인 직원들을 GROUP BY GROUPING SETS 부서아이디, 직업아이디로 묶고, COUNT했다.​
• 부서아이디 50/80 직원의 수가 나오고, 직종아이디별로 몇 명인지 나온다. 

SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (50, 80)
GROUP BY GROUPING SETS(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID, JOB_ID;

 

GROUPING SETS 결과

 

 

시각적으로 보자면 이러하다.

 

 

 

 

---

 

 

HAVING

• 그룹함수의 결과를 조건식에 사용할 수도 있다.
• 입사년도별 사원수를 조회했을 때 사원수가 20명 이상으로 입사한 해와 그 해에 입사한 사원수를 조회하기

SELECT TO_CHAR(HIRE_DATE, 'YYYY') YEAR, COUNT(*) YEAR_HIRED_CNT
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')
HAVING COUNT(*) >= 20
ORDER BY YEAR;

코드해석

FROM 직원 테이블에서

GROUP BY 고용일이 NNNN년인 사람들을 묶어서

HAVING 그룹 인원이 20명 이상인 것만 나오게 할 거야

SELECT 고용년도, 인원수

ORDER BY 고용년도

 

 

• 질문 : HAVING COUNT(*) >= 20 대신 HAVING YEAR_HIRED_CNT >= 20은 안 되나요?
• 대답 : 네, 안 됩니다. 아래의 그림을 봐 주세요.

 

이건 SQL의 실행순서. YEAR_HIRED_CNT은 SELECT에서 정의된 것이기에 HAVING에서 사용될 수 없다.

 

 

이건 우리가 해 온 과정

 

 

 

---

 

 

 

잘못된 정보에 대한 지적은 언제나 환영입니다. 

 

복습 끝!