GROUP BY와 HAVING
5. SELECT 컬럼명 AS 별핑, 계산식, 함수식
1. FROM 참조할 테이블명
2. WHERE 컬럼명 | 함수식 비교연산자 비교값
3. GROUP BY 그룹을 묶을 컬럼명
4. HAVING 그룹함수식 비교연산자 비교값
6. ORDER BY 컬럼명 | 별칭 | 컬럼순번 정렬방식 [NULLS FIRST | LAST]
쿼리문 실행 순서 : FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
1. GROUP BY
같은 값들이 여러 개 기록 된 컬럼을 가지고 같은 값들을 하나의 그룹으로 묶음
GROUP BY 컬럼명 | 함수식, ...
그룹으로 묶은 값에 대해서 SELECT절에서 그룹함수를 사용한다.
-- DEPT_CODE 기준으로 그룹핑
SELECT
COUNT(*)
, DEPT_CODE
FROM EMPLOYEE
GROUP BY DEPT_CODE;
-- DEPT_CODE , JOB_CODE 기준으로 그루핑
SELECT
JOB_CODE
, DEPT_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;
-- 직원 테이블에서 직급코드, 보너스를 받는 사원 수를 조회하여
-- 직급코드 순으로 오름차순 정렬하세요
SELECT
JOB_CODE
, COUNT(BONUS)
FROM EMPLOYEE
GROUP BY JOB_CODE
ORDER BY JOB_CODE;
-- 직원 테이블에서 직급코드, 보너스를 받는 사원 수를 조회하여
-- 직급코드 순으로 오름차순 정렬하세요
-- 단, 보너스를 받는 사람이 없는 직급코드의 겨우 RESULT SET에서 제외한다.
SELECT
JOB_CODE
, COUNT(*)
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,'여') 성별
, FLOOR(AVG(SALARY)) 평균
, SUM(SALARY) 합계
, COUNT(*) 인원수
FROM EMPLOYEE
--GROUP BY 성별 (GROUP BY절레는 SELECT절의 별칭 사용 불가)
GROUP BY DECODE((SUBSTR(EMP_NO, 8, 1)), 1, '남', 2,'여')
ORDER BY 인원수 DESC;
2. HAVING절
그룹함수로 구해올 그룹에 대해 조건을 설정할 때 사용
HAVING 컬럼명 | 함수식 비교연산자 비교값
-- 300만원 이상의 월급을 받는 사원을 대상으로 부서별 그룹 월급 평균 계산
SELECT
DEPT_CODE
, FLOOR(AVG(SALARY)) 평균
FROM EMPLOYEE
WHERE SALARY > 3000000
GROUP BY DEPT_CODE
ORDER BY 1;
-- 모든 직원을 대상으로 부서별 월급 평균을 구한 뒤 평균이 300만원 이상인 부서 조회
SELECT
DEPT_CODE
, FLOOR(AVG(SALARY)) 평균
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING FLOOR(AVG(SALARY)) > 3000000
ORDER BY 1;
-- 급여 합계가 가장 많은 부서의 부서코드와 급여 합계를 구하세요
SELECT
DEPT_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE
);
1. 집계 함수
GROUP BY 절에서만 사용하는 함수
그룹별로 묶어진 값에 대한 중간 집계와 총 집계를 구할 때 사용한다
그룹별로 계산 된 결과값들에 대한 총 집계가 자동으로 추가됨
1-1. ROLLUP
그룹별로 중간 집계 처리를 하는 함수
SELECT
JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY ROLLUP(JOB_CODE)
ORDER BY 1;
인자로 전달한 그룹 중에서 가장 먼저 지정한 그룹별 합계와 총 함계를 구하는 함수
SELECT
DEPT_CODE
, JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY ROLLUP(DEPT_CODE, JOB_CODE)
ORDER BY 1;
1-2. CUBE
: 그룹별 산출한 결과를 집계하는 함수
SELECT
JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY CUBE(JOB_CODE)
ORDER BY 1;
그룹으로 지정된 모든 그룹에 대한 집계와 총 합계를 구하는 함수
SELECT
DEPT_CODE
, JOB_CODE
, SUM(SALARY)
FROM EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1;
1-3. GROUPING 함수
ROLLUP이나 CUBE에 의한 산출물이 인자로 전달받은
컬럼 집합의 산출물이면 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;
CASE문으로도 작성 가능하다.
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;
2. SET OPERATION (집합 연산)
2-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-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;
2-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;
2-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;
2-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)
);
'백엔드 과정 > Oracle(SQL)' 카테고리의 다른 글
[Oracle] SQL문제풀이 / JOIN 연습문제 (1) | 2022.01.19 |
---|---|
[Oracle] Chap04 JOIN 조인 (0) | 2022.01.19 |
[Oracle] SQL문제풀이 / 함수 연습 문제 (0) | 2022.01.18 |
[Oracle] Chap02 그룹함수와 단일행함수 (0) | 2022.01.18 |
[Oracle] Chap01 SELECT 기본문법 및 연산자 (0) | 2022.01.17 |