[Database] - Group 함수 (그룹함수)

[Database] 함수 - 집합연산자와 분석함수

 

---

그룹함수 (Group 함수)

하나 이상의 행을 그룹으로 묶어 연산하여 하나의 결과를 나타내는 함수이다.
그룹함수는 일반 컬럼과 동시에 사용이 불가능하다.

Group 함수의 종류

• SUM
• AVG
• MIN
• MAX
• COUNT
• GROUP BY
• HAVING
• ROLL UP

---

1. SUM : 해당 컬럼 값들에 대한 총합을 구하는 함수

SELECT SUM(SALARY) FROM EMPLOYEES;

2. AVG : 해당 컬럼 값들에 대한 평균을 구하는 함수

SELECT AVG(SALARY) FROM EMPLOYEES;

3. MIN, MAX  : 해당 컬럼 값들에 대한 최소값, 최대값을 구하는 함수

SELECT MIN(SALARY), MAX(SALARY) FROM EMPLOYEES;

4. COUNT : 테이블에서 조건을 만족하는 행(ROW)의 개수를 반환하는 함수

※ COUNT 함수는 두 가지 형식이 존재한다.
1. COUNT(*) : 중복되는 행과 NULL 값을 포함한 테이블 행의 수를 리
2. COUNT(expr) : expr에 의해 인식된 열에서 Null이 아닌 행의 수를 리턴

SELECT COUNT(*) FROM EMPLOYEES;

SELECT COUNT(SALARY) FROM EMPLOYEES;

---

※ 그룹 함수와 컬럼을 동시에 검색 하는 방법

그룹함수 뒤에 OVER() 함수를 같이 작성한다.
▶ 이렇게 할시, 검색은 되지만 프로그램에 많은 부하와 지연을 주어 악영향을 끼치기 때문에 추천하지 않는다.

SELECT FIRST_NAME, AVG(SALARY) OVER(),
       MAX(SALARY) OVER(), 
       COUNT(*) OVER()
FROM EMPLOYEES;

---

5. GROUP BY 

• 같은 값을 가진 행을 그룹짓는 SQL 명령어
• 어떤 컬럼 값을 기준으로 그룹 함수를 적용하는 경우 GROUP BY 절 뒤에 기준이 되는 컬럼을 적는다.
• GROUP BY 절은 각 그룹의 하나만을 리턴한다.
• FROM 절과 WHERE 절 뒤에 위치한다. 

GROUP BY 실행 순서

FROM

WHERE

GROUP BY

SELECT

DISNICT

ORDER BY

LIMIT

 

GROUP BY절은 주로 집계함수와 많이 사용된다. 

1. COUNT()
2. AVG()
3. MIN()
4. MAX()
5. SUM()

주의 할 점

● GROUP BY 절에 지정되지 않은 컬럼은 SELECT 절에 사용할 수 없다.
    GROUP BY 절에 언급되지 않은 열이 존재한다면 오류가 발생한다. 

● GROUP BY가 SELECT보다 먼저 실행되지만, SELECT의 alias를 사용할 수 있다. 
   GROUP BY, HAVING, ORDER BY에서 SELECT의 alias를 사용할 수 있다.

SELECT DEPARTMENT_ID, SUM(SALARY), AVG(SALARY), MAX(SALARY), MIN(SALARY), COUNT(*)
FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

SELECT DEPARTMENT_ID, JOB_ID, SUM(SALARY), COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY DEPARTMENT_ID;

---

6. HAVING 절

GROUP BY 절의 조건 절이다.
HAVING 절과 WHERE 절의 차이점은 HAVING 절은 GROUP BY절과 함께 사용해야 한다.
▶ 집계함수를 사용하여 조건절을 작성하거나 GROUP BY 컬럼만 조건절에 사용 가능하다.

SELECT JOB_ID, AVG(SALARY) -- 조회할 컬럼
FROM EMPLOYEES             -- 테이블
WHERE JOB_ID NOT LIKE 'SA%' -- JOB_ID 중 SA가 포함된 직업을 제외
GROUP BY JOB_ID             -- 그룹화 
HAVING AVG(SALARY) > 8000   -- 그룹의 조건 
ORDER BY AVG(SALARY);       -- 정렬

---

7. ROLL UP

ROLL UP 구문은 GROUP BY절과 같이 사용되며, GROUP BY절에 의해서 그룹 지어진 집합결과에 대해서 좀 더 상세한
정보를 반환하는 기능을 수행한다.
▶ SELECT 절에 ROLLUP을 사용함으로써 보통의 SELECT된 데이터와 그 데이터의 총계를 구할 수 있다.

• ROLLUP 미사용

SELECT DEPARTMENT_ID, SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

• ROLLUP 사용

SELECT DEPARTMENT_ID, SUM(SALARY)
FROM EMPLOYEES
GROUP BY ROLLUP(DEPARTMENT_ID);

위 두 검색 결과의 차이를 보면, 
ROLLUP을 사용한 경우, 자동 내림차순이 적용되고, 최하단에 총합이 나온다.

이해가 되지 않는 다면 아래 추가로 검색한 구문을 확인한다.

• ROLLUP 사용전

SELECT DEPARTMENT_ID, JOB_ID, AVG(SALARY) AS 평균, SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY DEPARTMENT_ID, JOB_ID;

• ROLLUP 사용후

SELECT DEPARTMENT_ID, JOB_ID, AVG(SALARY) AS 평균, SUM(SALARY)
FROM EMPLOYEES
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID, JOB_ID;

ROLLUP으로 묶는 DEPARTMENT_ID, JOB_ID 별 평균과 SUM 값의 합계를 
각 행에 추가하여 보여준다.

---

8. CUBE

CUBE는 서브(하위) 그룹에 대한 Summary를 추출하는데 사용한다.
▶ 즉, ROLLUP에 의해 나타난 소계값(SUB TOTAL)과 총계값(GRAND TOTAL)을 나타낸다.
▶ GROUP BY 항목들간 다차원적인 소계를 계산 가능(모든 그룹케이스를 계산)

SELECT DEPARTMENT_ID, JOB_ID, ROUND(AVG(SALARY),2) AS 평균, SUM(SALARY)
FROM EMPLOYEES
GROUP BY CUBE(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID, JOB_ID;

▶ 위에 까지는 ROLLUP과 동일하지만, 각 각의 소계값들이 추가로 들어간다.

---

9. GROUPING SETS

GROUPING SETS는 위의 ROLLUP과 CUBE와는 다르게 계층이 나타지않고, 그룹핑된 결과값만 보여준다. 
GROUPING SETS는 괄호로 묶은 집합별로의 집계 역시 구할 수 있다.
ROLLUP, CUBE에 모두 사용 가능하다.

• ROW가 GROUP BY에 의해서 산출된 ROW인 경우에는 0을 반환
• ROW가 CUBE에 의해서 산출된 ROW인 경우에는 1이 반환 

▶ 따라서 해당 ROW가 결과 집합에 의해 산출된 DATA인지, ROLLUP이나 CUBE에 이해서 산출된 DATA인지
    알 수 있도록 지원하는 함수이다.

SELECT DECODE( GROUPING(DEPARTMENT_ID), 1, '총계', DEPARTMENT_ID) AS A, 
       DECODE( GROUPING(JOB_ID), 1, '소계', JOB_ID) AS B,
       ROUND(AVG(SALARY),2),
       GROUPING (DEPARTMENT_ID),
       GROUPING (JOB_ID)
FROM EMPLOYEES
GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)
ORDER BY DEPARTMENT_ID