D27~28 오라클3 Group By
20241101 ~1104 D27 D28
<GROUP BY 절>
- 그룹을 묶어줄 기준을 제시 할 수 있는 구문
=> 그룹합수와 함께 자주 사용- 제시된 기준별로 그룹을 묶을 수 있다.
[표현법]
GROUP BY 칼럼명
-- 각 부서별 급여의 합계
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE --그룹화를 진행 SELECT에서 그룹함수와 함께 사용이 가능해진다.
;
-- D1 부서의 총 급여 합
SELECT SUM(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D1';
--각 부서별 사원수
SELECT DEPT_CODE , COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_CODE;
-- 각 부서별 총 급여 합을 부서별 오름차순 정렬하여 조회
SELECT DEPT_CODE, SUM(SALARY) --4번실행
FROM EMPLOYEE --1번실행
WHERE 1 = 1 --3번실행
GROUP BY DEPT_CODE --2번실행
ORDER BY DEPT_CODE DESC; --5번실행
-- 각 직급별 직급코드 , 급여의 합, 사원수, 보너스를 받는 사원의 수, 평균급여, 최고급여,최소급여를 구하시오
SELECT JOB_CODE
, SUM(SALARY) 합
, COUNT(*) 총인원
, AVG(SALARY) 급여평균
,MIN(SALARY) 최소급여
,MAX(SALARY) 최대급여
FROM EMPLOYEE
GROUP BY JOB_CODE;
-- 성별 분류 사원 수
SELECT COUNT(*), DECODE(SUBSTR(EMP_NO, 8, 1), '1','남자','3','남자','여자')
FROM EMPLOYEE
GROUP BY DECODE(SUBSTR(EMP_NO, 8, 1), '1','남자','3','남자','여자')
ORDER BY 2;
-- 성별 기준으로 평균 급여를 구하시고
-- 성별은 남자, 여자 2개의 그룹으로 나누시오
SELECT AVG(SALARY), DECODE(SUBSTR(EMP_NO, 8, 1), '1','남자','3','남자','여자')
FROM EMPLOYEE
GROUP BY DECODE(SUBSTR(EMP_NO, 8, 1), '1','남자','3','남자','여자')
ORDER BY 2;
SELECT AVG(SALARY), DECODE(SUBSTR(EMP_NO, 8, 1), '1','남자','3','남자','여자')
FROM EMPLOYEE
GROUP BY
CASE SUBSTR(EMP_NO, 8, 1)WHEN 1 THEN '남자'
WHEN 3 THEN '남자'
ELSE '여자'
END 성별
FROM EMPLOYEE
GROUP BY
CASE SUBSTR(EMP_NO, 8, 1)WHEN 2 THEN '여자'
WHEN 4 THEN '여자'
ELSE '남자'
END;
-- 부서별로 사수가 존재하는 사원의 수
-- 단, 부서내 모든 사람이 사수가 없다면 0이 출력되게 하시오
SELECT DEPT_CODE, COUNT(MANAGER_ID)
FROM EMPLOYEE
-- WHERE MANAGER_ID IS NOT NULL
GROUP BY DEPT_CODE;HAVING
- 그룹에 대한 조건을 제시 하고자 할 때 사용되는 구문
(GROUP함수를 가지고 조건을 제시)- 단독으로는 사용이 불가능, GROUP BY절과 함께 사용된다.
- GROUP BY절 앞, 뒤에 넣어서 사용할수 있다.
--HAVING 절을 사용 하지 않는 경우의 쿼리문 예시
--부서별 // 월급 200만원 이하인 사원의 수
SELECT DEPT_CODE
,COUNT(CASE WHEN SALARY <=2000000 THEN 1 ELSE NULL END) AS COUNT
FROM EMPLOYEE
--WHERE SALARY <= 2000000 --그룹 바이 절 이후 실행 할수 있게 한다 위 CASE WHEN THEN 사용
GROUP BY DEPT_CODE;
-- 각 부서별 평균 급여가 300만원 이상인 부서들 조회
SELECT DEPT_CODE, AVG(SALARY) 평균
FROM EMPLOYEE
--WHERE AVG(SALARY) >= 3000000 --오류
GROUP BY DEPT_CODE;
-- 문법상 그룹함수를 WHERE절에 사용 할수 없다
============================HAVING 구문 활용====================================
-- HAVING 절을 이용하여 조건 제시 쿼리문
SELECT DEPT_CODE, AVG(SALARY) 평균
FROM EMPLOYEE --1번실행
WHERE 1 = 1 --2번 실행
GROUP BY DEPT_CODE --3번실행
HAVING AVG(SALARY) >= 3000000 ; --4번실행
--각 직급별 총 급여합이 1000만원 이상인 직급 코드 , 급여 합을 구하시오
SELECT JOB_CODE, SUM(SALARY) 급여합
FROM EMPLOYEE
GROUP BY JOB_CODE
HAVING SUM(SALARY) >= 10000000;
-- 각 부서별 보너스 받는 사원이 없는 부서를 조회
SELECT DEPT_CODE, COUNT(*), COUNT(BONUS)
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING COUNT(BONUS) = '0' ;
-- 각 부서별 평귭 급여가 350만원 이하인 부서만을 조회
SELECT DEPT_CODE, FLOOR(AVG(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING AVG(SALARY) <= 3500000;<SELECT문 구조 및 실행순서>
5실행. SELECT 조회하고자 하는 칼럼명들 , *, 리터럴, 산술연산식, 별칭 1실행 .FROM 조회하고자 하는 테이블명, 가상테이블(DUAL) 2실행. WHERE 조건식, 그룹함수는 사용불가 3실행. GROUP BY 그룹 기준에 해당하는 칼럼 / 함수식 4실행. HAVING 그룹함수식에 대한 조건식 (그룹핑 완료된 후 실행) 6실행. ORDER BY [정렬기준에 해당하는 칼럼/ 별칭/ 칼럼의 순번] [ASC / DESC] [NULL FIRST/ NULLS LAST] NULL 값을 가장 큰값으로 생각한다(오라클)
<집합 연산자 SET OPERATOR>
여러개의 쿼리문(RESULT SET)을 가지고 하나의 쿼리문(RESULT SET)으로 만드는 연산자.
*결과값 = RESULT SET
- UNION(합집합) : 두 쿼리문을 수행한 결과값을 하나로 더한 후 중복되는 부분은 제거한 집합
- UNION ALL(합집합) : 두 쿼리문을 수행한 결과값을 하나로 더한 집합, 중복제거를 하지 않는다
|||||||||||||||||||||||||||||||||||||||| UNION + INTERSECT와 결과값이 같음- INTERSECT(교집합) : 두 쿼리문을 수행 결과값에서 중복된 결과 집합을 반환.
- MINUS(차집합) : 선행 쿼리문의 결과값에서 후행 쿼리문 결과값을 뺀 나머지 부분을 반환
주의점
두 쿼리문의 결과를 합쳐서 하나의 RESULT SET 으로 보여줘야 하기
때문에 두 쿼리문의 SELECT절이 같아야 한다
--1. UNION (합집합) : 두 결과값을 합한 후 중복을 제거.
-- 부서코드가 D5 이거나 , 급여가 300만원 초과인 사원들의 사번, 사원명, 부서코드 조회
--1번 쿼리문
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'; --박나라, 하이유, 김해술, 심봉선, 윤은해, 대북혼
--2번 쿼리문
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE SALARY > 3000000; --선동일, 송종기, 노옹철, 유재식, 정중하, 심봉선, 대북혼
--심봉선, 대북혼 중복값
-- UNION 사용 1번쿼리와 2번쿼리의 중복값을 제외하고 출력
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
UNION
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE SALARY > 3000000; --13명 (중복값 제거)
-- UNION ALL : 여러개의 쿼리문의 결과를 하나로 더해서 보여주는 연산자(중복값 제거 안함)
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
UNION ALL
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE SALARY > 3000000;
-- INTERSECT : 교집합, 중복되는 결과값만 조회;
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
INTERSECT
SELECT EMP_ID, EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE SALARY > 3000000; --중복값만 조회 (심봉선 대북혼)
-- MINUS : 차집합, 선행 쿼리문에서 후행쿼리문를 뺀 나머지 값 반환
-- 직급코드가 J6인 사원들중 부서코드가 D1인 사원들을 뺀 나머지 사원을 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, DEPT_CODE
FROM EMPLOYEE
WHERE JOB_CODE = 'J6' -- 전형돈 장쯔위 하동운 차태연(D1) 전지연(D1) 이태림
MINUS
SELECT EMP_ID, EMP_NAME, JOB_CODE, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D1'; -- 박면수 차태연(J6) 전지연(J6) 빠진 값
-- 결과값 = 전형돈 장쯔위 하동운 이태림그룹별 집계함수
- GROUP BY 로 계산된 그룹별 산출 결과물을 소그룹별로 나워서 집계 해주는 함수
1. ROLL UP
ROLLUP (칼럼1, 칼럼2) : GROUP BY로 묶은 소그룹 간의 합계 , 전체 합계 , 칼럼1번 기준의 합계를 산출하는 매서드
2. CUBE
CUBE (칼럼1, 칼럼2) : GROUP BY로 묶은 소그룹간의 합계 ,전체합계 , 칼럼1번 기준의 합계 , 칼럼2번 기준의 합계를 반환하는 매서드 (산출가능한 모든 결과값을 계산하여 반환하는 함수)
3. GROUPING SETS
GROUPING SETS(칼럼1, 칼럼2) : 칼럼1번 기준의 합계, 칼럼2번 기준의 합계를 반환하는 매서드
기본형
-- 각 부서 내부에서 직급별 급여의 합. SELECT DEPT_CODE, JOB_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE, JOB_CODE --ORDER BY 1, 2; UNION ALL --총합 SELECT NULL, NULL, SUM(SALARY) FROM EMPLOYEE UNION ALL --(칼럼1 기준 집계결과) SELECT DEPT_CODE, NULL, SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE; ORDER BY 1, 2;
GROUP BY ROLLUP (칼럼1, 칼럼2)
GROUP BY ROLLUP (칼럼1, 칼럼2) = GROUP BY 칼럼1, 칼럼2 UNION ALL GROUP BY 칼럼1 UNION ALL 모든 집합 그룹 결과를 반환
ROLLUP
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY ROLLUP(DEPT_CODE, JOB_CODE) ORDER BY 1, 2;
GROUP BY CUBE (칼럼1, 칼럼2)
GROUP BY CUBE (칼럼1, 칼럼2) = GROUP BY 칼럼1, 칼럼2 --> 소그룹 집계결과 UNION ALL GROUP BY 칼럼1 -- 칼럼1 기준 집계결과 UNION ALL GROUP BY 칼럼2 -- 칼럼2 기준 집계결과 UNION ALL 모든 집합 그룹 결과 --> 전체 집계 결과
큐브 함수
--기본형
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE, JOB_CODE
--(전체 집계결과)
UNION ALL
SELECT NULL, NULL, SUM(SALARY)
FROM EMPLOYEE
UNION ALL
--(칼럼1 기준 집계결과)
SELECT DEPT_CODE, NULL , SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE
UNION ALL
--칼럽 2기준 집계 결과
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1, 2;
--CUBE(거의 모든 조건별 결과값 반환)
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY CUBE(DEPT_CODE, JOB_CODE)
ORDER BY 1, 2;GROUP BY GROUPING SETS(칼럼1. 칼럼2)
GROUP BY GROUPING SETS(칼럼1. 칼럼2) = GROUP BY 칼럼1 UNION ALL GROUP BY 칼럼2
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY GROUPING SETS(DEPT_CODE, JOB_CODE);
--GROUPING
--그룹화된 결과가 NULL일때 다른 값을 넣기 위한 용도로 사용
SELECT
CASE WHEN GROUPING(DEPT_CODE) = 1 THEN '모든 부서 코드'
WHEN DEPT_CODE IS NULL THEN '부서 코드 없음'
ELSE DEPT_CODE
END AS 부서코드,
CASE WHEN GROUPING(JOB_CODE) = 1 THEN ' ' ELSE JOB_CODE END AS 직급코드,
SUM(SALARY)
FROM EMPLOYEE
GROUP BY ROLLUP(DEPT_CODE, JOB_CODE);
