[DB] SELECT문 해석 순서 | GROUP BY, ORDER BY,HAVING, 집계 함수

⭐ SELECT문 해석 순서 ⭐

5: SELECT 컬럼명 | 계산식 | 함수 AS 별칭
1: FROM 참조할 테이블명
2: WHERE 컬럼명 | 함수식 비교연산자 비교값
3: GROUP BY 그룹으로 묶을 컬럼명 
4: HAVING 그룹함수식 비교연산자 비교값
6: ORDER BY 컬럼명 | 별칭 | 컬럼순서 [ASC|DESC] [NULLS FIRST | LAST];

 

확인필요) SELECT, WHERE, GROUP BY, HAVING 에는 서브쿼리가 들어갈 수 있다. 

예제. EMPLOYEE 테이블에서 부서별 급여 합, 급여 평균(소수점 아래 둘째 자리 반올림), 인원 수를 부서코드 오름차순으로 조회

SELECT DEPT_CODE,
       SUM(SALARY) "급여 합",
       ROUND( AVG(SALARY) ,1 ) "급여 평균",
       COUNT(*) "인원 수"
FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY DEPT_CODE;

---

🚩 GROUP BY

그룹 함수는 단 한 개의 결과 값만 산출하기 때문에 그룹이 여러 개일 경우
오류 발생 여러 개의 결과 값을 산출하기 위해
그룹 함수가 적용될 그룹의 기준을 GROUP BY절에 기술하여 사용

같은 값들이 여러 개 기록된 컬럼을 가지고 
같은 값들을 하의 그룹으로 묶는 방법

작성법

GROUP BY 컬럼명 | 함수식 [,컬럼명 | 함수식 .... ] 

➡ GROUP BY가 포함된 SELECT문의 SELECT절에는
GROUP BY에 작성된 컬럼명 + 그룹 함수만을 작성할 수 있다.

 

예제 1. 부서별로 급여 합

➡ 에러가 나는 이유 ❗ 

테이블은 2차원 배열처럼 표 모양을 가지기 때문에 빈 칸에 NULL이 들어가면 아무런 칸이 없기때문에 두개가 조합이 되지 않는다.

단일행과 그룹함수가 같이 쓰일 수 없다.

DEPT_CODE : 단일행 / SUM(SALARY) : 그룹

 

 

➡ GROUP BY를 통해 DEPT_CODE가 같은 부서끼리 그룹을 지어준다. 

그래서 DEPT_CODE : 그룹 / SUM(SALARY) : 그룹

 

예제 1. EMPLOYEE 테이블에서 각 성별 별로 인원 수, 급여 합, 급여 평균을 인원수 내림 차순으로 조회
SELECT DECODE( SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여') 성별,
    COUNT(*) "인원 수", SUM(SALARY) "급여 합", FLOOR(AVG(SALARY)) "급여 평균"
FROM EMPLOYEE
GROUP BY DECODE( SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여') -- GROUP BY에 함수식 사용
ORDER BY "인원 수" DESC;

---

예제 2. EMPLOYEE 테이블에서 부서 코드가 'D5', 'D6' 인 부서의 인원 수, 평균 급여(소수점 내림) 조회
SELECT DEPT_CODE, COUNT(*) "인원 수" , FLOOR(AVG(SALARY)) "평균 급여"
FROM EMPLOYEE
WHERE DEPT_CODE IN ('D5', 'D6')
GROUP BY DEPT_CODE; 

➡ SELECT절에 쓴 컬럼을 그룹으로 쓰고싶다면 GROUP BY절에 그대로 쓰면 된다. 

DEPT_CODE를 그룹으로 묶겠다.

 

만약, DEPT_CODE의 일부만 그룹으로 묶고싶다면 

GROUP BY DEPT_CODE IN ('D5', 'D6')

 

➡ SELECT절에 단일행, 그룹함수가 혼용되어 있는 경우 단일행 부분을 모두 GROUP BY절에 그대로 작성해야 한다.

---

예제 3. 직급 코드별 2000년도 이후 입사자들의 인원 수, 급여 합 조회 (직급 코드 오름차순)
SELECT JOB_CODE, COUNT(*)"인원수" ,SUM(SALARY)"급여 합"
FROM EMPLOYEE
WHERE HIRE_DATE >= TO_DATE(20000101, 'YYYYMMDD')
GROUP BY JOB_CODE
ORDER BY JOB_CODE;

➡ 
 TO_DATE :  날짜만 적어도 되지만 정확하게 하려면 패턴을 함께 작성한다.
GROUP BY 절: 직급 코드를 그룹으로 묶는다.
ORDER BY 절 : 직급코드 오름차순

---

예제 3-1. EXTRACT 사용 
SELECT JOB_CODE, COUNT(*)"인원수" ,SUM(SALARY)"급여 합"
FROM EMPLOYEE
WHERE EXTRACT(YEAR FROM HIRE_DATE) >= 2000
GROUP BY JOB_CODE
ORDER BY JOB_CODE;

➡  자바 같은 프로그래밍 언어에서는 시간 관련 데이터를 다루는 것이 쉽지 않음
  + 정렬도 자바에 구현되어있는 정렬보다, DBMS를 이용한 정렬(ORDER BY)가 쉽고 효율적임
  
  추후 JAVA + DB 구현 시, 시간/정렬 부분은 DB에서
  복잡한 로직은 JAVA에서 처리

 

 

2.  HAVING절

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

그룹 함수로 값을 구해올 그룹에 대해 조건을 설정할 때 HAVING절에 기술 (WHERE절은 각 컬럼 값에 대한 조건)

작성법

HAVING 컬럼명 | 그룹함수식 비교연산자 비교값

예제 1.  부서별 급여 평균이 3백만 이상인 부서를 조회하여 부서코드 오름차순으로 정렬
SELECT DEPT_CODE, FLOOR(AVG(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE
HAVING FLOOR(AVG(SALARY)) >= 3000000
ORDER BY DEPT_CODE;

---

🚩 집계함수(ROLLUP, CUBE)

그룹 별로 산출한 결과 값의 집계를 처리하는 함수
인자로 전달받은 그룹 중 가장 먼저 지정한 그룹별로 추가적 집계 결과 반환

 

예제 : 부서코드가 'D5'이거나 급여가 300만 초과인 직원의 사번,이름, 부서코드, 급여 조회

---

1. ROLLUP 함수

그룹별로 중간 집계를 처리하는 함수

그룹별로 묶여진 값에 대한 중간집계와 총 집계를 계산하여 자동으로 추가함

예제 1. 각 부서에 소속된 직급별 급여 합, 부서별 급여 합, 전체 급여 합 조회
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY ROLLUP( DEPT_CODE, JOB_CODE )
ORDER BY 1;

결과) 중간 집계와 총 집계 계산

 

2. CUBE 함수

그룹별 산출 결과를 집계하는 함수

그룹으로 지정된 모든 그룹에 대한 중간 집계와, 총 집계를 구함.

예제 1.각 부서에 소속된 직급별 급여 합,부서별 급여 합,전체 급여 합 조회
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY CUBE( DEPT_CODE, JOB_CODE )
ORDER BY 1;

결과) 중간 집계와 총 집계 계산
부서 상관없이 J1직급의 합

 

---

🚩 집합 연산자 (SET OPERATOR)

여러 개의 SELECT의 결과(RESULT SET)을 하나의 RESULT SET으로 합치는 연산자
(주의사항) 집합 연산에 사용되는 SELECT문의 SELECT절은 모두 동일해야 한다.

 

예제 : 부서코드가 'D5'이거나 급여가 300만 초과인 직원의 사번,이름, 부서코드, 급여 조회

 

---

1.  UNION (합집합)

여러 SELECT문의 결과를 하나로 합치는 연산자
중복되는 영역의 값은 한번 만 조회

여러 개의 쿼리 결과를 합치는 연산자로 중복된 영역은 제외하여 합침

예제 1. 부서코드가 'D5'이거나 급여가 300만 초과인 직원의 사번,이름, 부서코드, 급여 조회
-- 부서코드가 '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;

 

2. INTERSECT (교집합)

SELECT 조회 결과에서 공통된 부분만 조회

여러 개의 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;

 

3.  UNION ALL (합집합 + 교집합)

여러 SELECT 결과를 하나로 합침(중복 영역도 모두 포함)

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 결과에서 중복되는 부분을 제거하여 조회

예제 1. 4번 문자부터 검색해서 두 번째 나오는 B의 위치
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;