[210915] 그룹 함수, ORDER BY , GROUP BY, HAVING, 절 실행 순서

그룹함수(집계함수)

하나 이상의 행을 그룹으로 묶어 연산하며 총합, 평균 등을 하나의 컬럼으로 반환하는 함수

 

xxx 별로 결과 값을 구함

ex) 부서별 급여 총합을 구함, 부서별 급여가 가장 높은 금액 구함

 

구분	설명
SUM	그룹의 누적 합계 반환
AVG	그룹의 평균 반환
COUNT	그룹의 총 개수 반환
MAX	그룹의 총 개수 반환
MIN	그룹의 총 개수 반환

그룹함수 ex)

-- 집계 함수의 특징 : 전체 행에 대한 실행 결과 하나 반환. -- 따라서 GROUP BY 함수 없이는 JOB_ID와 함께 출력이 불가 not a single-group group function, -- SUM은 결과 행이 하나고 JOB_ID는 여러 줄이기 때문에  SELECT JOB_ID          , SUM(SALARY)   FROM EMPLOYEES  GROUP BY JOB_ID;  -- JOB_ID 컬럼의 값을 사용하여 그룹화 시켜야 가능, 그룹화한 데이터들에 대한 각각의 합계를 구함  SELECT SUM(SALARY)   FROM EMPLOYEES  GROUP BY JOB_ID;  -- 가능, SUM한 결과 행들만 나옴 SELECT JOB_ID         , SUM(SALARY)         , AVG(SALARY) -- SUM / COUNT 한 값일 것임         , COUNT(*) -- 그룹화한 데이터들의 각각의 행의 갯수들 구함   FROM EMPLOYEES  GROUP BY JOB_ID; SELECT JOB_ID          , SUM(SALARY)          , AVG(SALARY)          , COUNT(*)          , MAX(SALARY)  --그룹화한 데이터 중 (직무별) 최대 SALARY          , MIN(SALARY)  --그룹화한 데이터 중 (직무별) 최소 SALARY   FROM EMPLOYEES  GROUP BY JOB_ID;

* SELECT JOB_ID, SALARY FROM EMPLOYEES ORDER BY JOB_ID;  --JOB_ID를 기준으로(JOB_ID 내림차순) JOB_ID와 SALARY를 정렬하겠다.

---

ORDER BY

SELECT한 컬럼에 대해 정렬을 할 때 작성하는 구문

SELECT 구문의 가장 마지막에 작성하며 실행 순서 역시 가장 마지막

 

SELECT 컬럼 명 [, 컬럼명, …] AS 별칭   FROM 테이블 명 WHERE 조건식 ORDER BY 컬럼명 | 별칭 | 컬럼 순번 정렬방식 [NULLS FIRST | LAST]; -- 컬럼명 아닌 컬럼 별칭, 컬럼 순번(SELECT 절에 작성 된 기준) 으로 선택해서도 정렬 가능 -- [NULLS FIRST | LAST] : 정렬 중 NULL이 있을 때 처음으로 정렬할 것인지 마지막으로 정렬할 것인지 * 정렬 방식 - ASC : 오름차순 (기본설정) - DESC : 내림차순

ex) SELECT DEPARTMENT_ID AS 부서코드            , COUNT(*) AS 인원수            , AVG(SALARY) AS 평균급여 FROM EMPLOYEES GROUP BY DEPARTMENT_ID ORDER BY 1 DESC NULLS FIRST; -- 컬럼 순번, 내림차순 , 널이 가장 앞으로 오게 정렬

ex) 동일 값에 대한 정렬 (정렬 안의 정렬) SELECT EMPLOYEE_ID          , FIRST_NAME || ' ' || LAST_NAME AS NAME          , JOB_ID   FROM EMPLOYEES   ORDER BY JOB_ID, EMPLOYEE_ID; -- JOB_ID의 동일한 값에 대한 정렬을 위해 또 정렬 기준 컬럼명을 뒤에 적어주면 된다.

---

SELECT 구문에서 사용되는 절들의 실행 순서

1. FROM 절

2. WHERE 절

3. GROUP BY 절

4. HAVING 절

5. SELECT 절

6. ORDER BY 절

 

FROM 테이블에서 WHERE 지정해서 범위 줄고 거기서 그룹화해서 또 줄어들고 그 그룹에대한 조건인 HAVING 을 적용해서 최종 결과를 SELECT하고 마지막으로 ORDER BY 정렬

 

---

---

GROUP BY

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

 

 그룹함수들을 제외한 일반 컬럼의 일반 함수를 사용하고 있다면 SELECT 절에 명시한 컬럼을 그룹 바이에 그대로 옮겨서 작성해 주면 됨 적혀진 내용 기준으로 묶고(그룹화), 그룹함수로 평균, 합계, 인원수를 구한다. 

 

ex1) - EMPLOYEE에서 부서코드, 그룹 별 급여의 합계, 그룹 별 급여의 평균(정수처리), 인원 수를 조회하고 부서 코드 순으로 정렬 SELECT DEPT_CODE 부서코드,             SUM(SALARY) 합계,             FLOOR(AVG(SALARY)) 평균,             COUNT(*) 인원수 FROM EMPLOYEE GROUP BY DEPT_CODE ORDER BY DEPT_CODE ASC;

 

ex2) - EMPLOYEE테이블에서 성별과 성별 별 급여 평균(정수처리), 급여 합계, 인원 수 조회하고 인원수로 내림차순 정렬 SELECT DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') 성별,  --민증 번호 자른것 기준으로, 1이면 남자 2면 여자            FLOOR(AVG(SALARY)) 평균,             SUM(SALARY) 합계,             COUNT(*) 인원수 FROM EMPLOYEE GROUP BY DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') ORDER BY 4 DESC;

 

---

HAVING

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

 

 그룹을 묶기 전 테이블에 존재하는 데이터를 검색할 때에는 WHERE 절에 조건을 명시를 하는데 그룹으로 묶이고 난 이후의 조건은 HAVING 절에 기술하여야 한다. WHERE와 HAVING의 조건 의미가 다르므로 WHERE절에서는 그룹에 대한 조건을 사용할 수 없다.

따라서 그룹함수를 사용해서 조건 부여하고 싶을 때에는 반드시 HAVING절이 필요하다. 

HAVING 절은 GROUP BY절 다음에 나와야 한다.

 

ex) - 부서 코드와 급여 3000000 이상인 직원의 그룹별 평균 조회 SELECT DEPT_CODE, FLOOR(AVG(SALARY)) 평균 FROM EMPLOYEE WHERE SALARY >= 3000000 GROUP BY DEPT_CODE ORDER BY 1; - 부서 코드와 급여 평균이 3000000 이상인 그룹 조회 SELECT DEPT_CODE, FLOOR(AVG(SALARY)) 평균 FROM EMPLOYEE GROUP BY DEPT_CODE HAVING FLOOR(AVG(SALARY)) >= 3000000 ORDER BY DEPT_CODE;

 

ex1) -- HAVING 절로 그룹의 조건 설정 (만일 WHERE로 쓰면 오류) SELECT DEPARTMENT_ID AS 부서코드            , COUNT(*) AS 인원수            , AVG(SALARY) AS 평균급여 FROM EMPLOYEES GROUP BY DEPARTMENT_ID HAVING AVG(SALARY) >= 9000; ORDER BY 1 DESC NULLS FIRST;  -- 그룹함수의 조건으로 쓰지 않고 그룹화 전에 그룹함수 AVG 수식 떼어버리고 WHERE절로 쓰면 조건 뜻이 완전히 바뀌어버려서 결과값이 달라진다. SELECT DEPARTMENT_ID AS 부서코드            , COUNT(*) AS 인원수            , AVG(SALARY) AS 평균급여 FROM EMPLOYEES WHERE SALARY >= 9000 GROUP BY DEPARTMENT_ID ORDER BY 1 DESC NULLS FIRST;

ex2) -- EMPLOYEES 테이블에서 평균 급여가 8000 이상인 년대를 조회 (80, 90, 2000년대) SELECT TRUNC(EXTRACT(YEAR FROM HIRE_DATE), -1) AS 고용년대            , AVG(SALARY) AS 평균급여            , COUNT(SALARY) AS 인원수 FROM EMPLOYEES GROUP BY TRUNC(EXTRACT(YEAR FROM HIRE_DATE), -1) HAVING AVG(SALARY) >= 8000;