[Oracle] DAY22_GROUP BY / HAVING

GROUP BY / HAVING

 SELECT문 해석 순서
  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절에서 그룹 함수를 사용한다. 

 

--EMPLOYEE 테이블에서 
--부서코드와 부서별 보너스를 받는 사원 수를 조회하고 
--부서코드 순으로 정렬

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

--EMPLOYEE 테이블에서 
--성별과 성별 별 급여 평균(정수처리),급여합계, 인원 수 조회
--인원수로 내림차순 정렬 

SELECT DECODE( SUBSTR(EMP_NO,8,1),'1','남','2','여') 성별,
    FLOOR(AVG(SALARY)) "급여 평균",
    SUM(SALARY) "급여 합계",
    COUNT(*) AS "인원 수"
FROM EMPLOYEE 
GROUP BY DECODE( SUBSTR(EMP_NO,8,1),'1','남','2','여')--별칭 사용불가(아직 해석 안됨)
ORDER BY "인원 수" DESC;

--EMPLOYEE테이블에서 

--직급 별 2000년도 이후 입사자들의 급여 합을 조회 

SELECT JOB_CODE, SUM(SALARY) --직급별 / 급여 합 
FROM EMPLOYEE
WHERE  HIRE_DATE >='2000/01/01' 
GROUP BY JOB_CODE;

--EMPLOYEE 테이블에서 부서별로 급여등급이 같은직원의 수를 조회하고
--부서코드,급여등급 오름차순으로 정렬 

SELECT DEPT_CODE, SAL_LEVEL, COUNT(*)
FROM EMPLOYEE
GROUP BY DEPT_CODE, SAL_LEVEL
ORDER BY DEPT_CODE, SAL_LEVEL;

2) HAVING 절 

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

 

-HAVING 칼럼명 | 함수식 비교연산자 비교값

 

--부서별 평균 급여가 3000000원 이상인 부서를

 조회하여 부서코드 오름차순으로 정렬 

 

WHERE절로 작성하게 되면,

급여가 300만 이상인 직원의 부서별 급여 평균  

SELECT DEPT_CODE, FLOOR(AVG(SALARY))
FROM EMPLOYEE
WHERE SALARY >=300000 
GROUP BY DEPT_CODE
ORDER BY DEPT_CODE;

HAVING 절로 작성하여, 

부서별 평균 급여가 300만 이상 

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

 

---

(이런게 있구나~ 딱히 쓰진 않지만 자격증 시험에 나온 적 있다. )

 

집계합수(ROLLUP,CUBE) 
그룹 별 산출한 결과 값의 집계를 계산하는 함수
GROUP BY 절에만 작성하는 함수 

1) ROLLUP 함수

그룹별로 중간 집계 처리를 하는 함수 
그룹별로 묶어진 값에 대한 '중간집계'와 '총집계'를 계산하여 자동으로 추가하는 함수 
* 인자로 전달받은 그룹중에서 가장 먼저 지정한 그룹별 합계와 총 합계를 구하는 함수 

--EMPLOYEE 테이블에서 
--각 부서에 소속된 직급별 급여합,
--부서 별 급여합,
--전체 직원 급여 총합 조회 
SELECT DEPT_CODE,JOB_CODE,SUM(SALARY)
FROM EMPLOYEE
GROUP BY ROLLUP (DEPT_CODE,JOB_CODE)
ORDER BY 1;

D1 NULL 7820000은 D1 J1/J7을 더한 값이며,

NULL J6는 J6값에 대한 합계이며.

NULL NULL은 전체 합계이다.

 

 

2) CUBE함수

그룹별 산출한 결과를 집계하는 함수이다. 
*그룹으로 지정된 모든 그룹에 대한 집계와 총 합계를 구하는 함수 (순서에 영향을 받지않음)

-- EMPLOYEE 테이블에서 각 부서마다 직급별 급여 합 
-- 부서 전체 급여합
SELECT DEPT_CODE,JOB_CODE,SUM(SALARY)
FROM EMPLOYEE
GROUP BY CUBE(DEPT_CODE,JOB_CODE)
ORDER BY 1;

---

SET OPERATION(집합 연산)

 

 

-여러개의 SELECT 결과물을 하나의 쿼리로 만드는 연산자
-여러가지 조건이 있을 때 그에 해당하는 여러개의 결과값을 결합시키고 싶을 때 사용
-초보자들이 사용하기 쉽다. 
-(주의) 집합연산에 사용되는 SELECT문은 SELECT절이 동일해야함 

UNION

OR 같은 개념 (합집합) --> 중복을 제거 

INTERSECT

 AND 같은 개념 (교집합) 

UNION ALL

 OR 결과 값에 AND 결과값이 더해진것 (합집합 + 교집합) -> 중복 미제거 

 

MIUNUS

 차집합 개념 

1) UNION

여러개의 쿼리 결과를 하나로 합치는 연산자
중복된 영역을 제외하여 하나로 합친다. 

-- 부서 코드가 'D5'인 사원의 사번,이름, 부서코드, 급여, 조회 
SELECT EMP_ID,EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
UNION
--급여가 300만 초과인 사원의 사번,이름, 부서코드, 급여, 조회 
SELECT EMP_ID,EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000;

--UNION은 OR 연산의 결과와 같다 
SELECT EMP_ID,EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
OR SALARY >3000000;

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

--INTERSECT은 AND 연산의 결과와 같다 
SELECT EMP_ID,EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
OR SALARY >3000000;

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

4) MINUS

선행 SELECT 결과에서 다음 SELECT 결과와 겹치는 부분을 제외한 나머지 부분만 추출 (차집합)

-- 부서코드가 D5 중에서 급여가 300만 초과인 직원을 제외한다. 
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;

--MINUS는 AND 연산 + 비교연산 반대로 작성하면 된다.
SELECT EMP_ID,EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE DEPT_CODE ='D5'
AND SALARY <=3000000;

여러 SELECT 집합 연산 기능

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 DEPT_CODE = 'D6'

UNION

SELECT EMP_ID,EMP_NAME,DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';