[Oracle] DAY22_GROUP BY / HAVING

ON/Oracle

[Oracle] DAY22_GROUP BY / HAVING

박도비 2023. 5. 18. 16:53

728x90

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';

728x90

저작자표시 (새창열림)