5. [Oracle] 그룹함수를 사용해보자.

-그룹 함수

단일 행 함수와 달리 그룹 함수는 행 집합에 대해 실행되어 그룹당 하나의 결과를 산출합니다. 이러한 행 집합은 전체 테이블이나 그룹으로 분할된 테이블로 구성될 수 있습니다.

 

-그룹 함수 유형

함수	설명
AVG([DISTINCT|ALL]n)	n의 평균값. null 값은 무시합니다.
COUNT({*|[DISTINCT|ALL] expr})	행 개수. 여기서 expr은 null이 아닌 값을 평가합니다. (*를 사용하여 중복된 행과 null 값으로 된 행을 비롯하여 선택된 모든 행의 수를 셉니다.)
MAX([DISTINCT|ALL]expr)	expr의 최대값. null 값은 무시합니다.
MIN([DISTINCT|ALL]expr)	expr의 최소값. null 값은 무시합니다.
STDDEV([DISTINCT|ALL]n)	n의 표준 편차. null 값은 무시합니다.
SUM([DISTINCT|ALL]n)	n의 합계 값. null 값은 무시합니다.
VARIANCE([DISTINCT|ALL]n)	n의 분산. null 값은 무시합니다.

 

-그룹 함수 구문

SELECT group_function(column), ...

FROM table

[WHERE condition]

[ORDER BY column];

그룹 함수는 SELECT 키워드 뒤에 배치합니다. 여러 그룹 함수를 쉼표로 구분하여 함께 사용할 수 있습니다.

 

• DISTINCT는 함수가 중복되지 않는 값만 사용하도록 만듭니다. ALL은 중복된 값을 포함하여 모든 값을 사용하도록 만듭니다. 기본값은 ALL이므로 별도로 ALL을 지정할 필요는 없습니다.

• expr 인수를 사용하는 함수의 데이터 유형은 CHAR, VARCHAR2, NUMBER 또는 DATE가 될 수 있습니다.

• 모든 그룹 함수는 null 값을 무시합니다. null을 값으로 치환하려면 NVL, NVL2, COALESCE, CASE 또는 DECODE 함수를 사용합니다.

 

-AVG 및 SUM 함수

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)

FROM employees

WHERE job_id LIKE '%REP%';

숫자 데이터를 저장할 수 있는 열에 대해AVG, SUM, MIN 및 MAX 함수를 사용할 수 있습니다. 슬라이드의 예제는 모든 판매 담당자의 월급에 대해 평균, 최고값, 최저값 및 합계를 표시합니다.

 

-MIN 및 MAX 함수

SELECT MIN(hire_date), MAX(hire_date)

FROM employees;

 

-COUNT 함수

COUNT(*)는 테이블의 행 수를 반환합니다.

SELECT COUNT(*)

FROM employees

WHERE department_id = 50;

 

COUNT(expr)은 expr에 대해null이 아닌 값을 가진 행의 수를 반환합니다.

SELECT COUNT(commission_pct)

FROM employees

WHERE department_id = 80;

 

COUNT 함수에는 다음 세 가지 형식이 있습니다.

• COUNT(*)

• COUNT(expr)

• COUNT(DISTINCT expr)

 

COUNT(*)는 SELECT 문의 조건을 충족하는 테이블의 행 수를 반환하며 여기에는 중복 행과 열에 null 값을 포함한 행이 포함됩니다. SELECT 문에 WHERE 절이 포함된 경우 COUNT(*)는 WHERE 절의 조건을 충족하는 행 수를 반환합니다.

 

이와 반대로 COUNT(expr)은 expr에 의해 식별되는 열에 있는 null이 아닌 값의 수를 반환합니다.

 

COUNT(DISTINCT expr)은 expr에 의해 식별되는 열에 있는 고유하고 null이 아닌 값의 수를 반환합니다.

 

-DISTINCT 키워드

• COUNT(DISTINCT expr)은 expr의 null이 아닌 구분 값의 수를 반환합니다.

• 다음은 EMPLOYEES 테이블에서 부서의 구분 값 수를 표시합니다.

 

SELECT COUNT(DISTINCT department_id)

FROM employees;

 

열에서 중복 값을 세지 않으려면 DISTINCT 키워드를 사용합니다.

 

-그룹 함수 및 null 값

그룹 함수는 열에 있는null 값을 무시합니다.

SELECT AVG(commission_pct)

FROM employees;

 

NVL 함수는 강제로 그룹 함수에null 값이 포함되도록 합니다.

SELECT AVG(NVL(commission_pct, 0))

FROM employees;

 

-데이터 그룹 생성

지금까지의 설명에서는 모든 그룹 함수가 테이블을 하나의 커다란 정보 그룹으로 취급했습니다. 그러나 정보 테이블을 더 작은 그룹으로 나눠야 하는 경우도 있습니다. 이러한 작업은 GROUP BY 절을 사용하여 수행할 수 있습니다.

 

-데이터 그룹 생성: GROUP BY 절

GROUP BY 절을 사용하여 테이블의 행을 더 작은 그룹으로 나눌 수 있습니다.

SELECT column, group_function(column)

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[ORDER BY column];

 

그룹 함수에 속하지 않는 SELECT 리스트의 모든 열은 GROUP BY 절에 있어야 합니다.

SELECT department_id, AVG(salary)

FROM employees

GROUP BY department_id ;

 

GROUP BY 절을 사용할 경우 그룹 함수가 아닌 SELECT 리스트의 모든 열은GROUP BY 절에 포함되어야 합니다. 슬라이드의 예제에서는 각 부서의 부서 번호와 평균 급여를 표시합니다.

 

GROUP BY 절을 포함한 이 SELECT 문이 평가되는 방식은 다음과 같습니다.

• SELECT 절은 다음과 같이 검색할 열을 지정합니다.

- EMPLOYEES 테이블의 부서 번호 열

- GROUP BY 절에 지정한 그룹의 모든 급여 평균

• FROM 절은 데이터베이스가 액세스해야 하는 테이블, 즉 EMPLOYEES 테이블을 지정합니다. • WHERE 절 은 검색할 행을 지정합니다. WHERE 절이 없기 때문에 기본적으로 모든 행이 검색됩니다.

• GROUP BY 절은 행을 그룹화하는 방법을 지정합니다. 행은 부서 번호별로 그룹화되므로 salary 열에 적용 되는 AVG 함수는 각 부서의 평균 급여를 계산합니다.

 

GROUP BY 열은 SELECT 리스트에 없어도 됩니다.

SELECT AVG(salary)

FROM employees

GROUP BY department_id ;

 

GROUP BY 열은 SELECT 절에 없어도 됩니다. 예를 들어, 슬라이드의 SELECT 문은 개별 부서 번호를 표시하지 않고 각 부서의 평균 급여를 표시합니다. 그러나 부서 번호가 없으면 결과가 의미가 없습니다.

 

-두 개 이상의 열로 그룹화

때때로 그룹 내 그룹에 대해 결과를 확인해야 할 경우가 있습니다. 슬라이드는 각 부서에서 각 직책에 지급된 총 급여를 표시하는 보고서를 보여줍니다.

 

-여러 열에서 GROUP BY 절 사용

SELECT department_id, job_id, SUM(salary)

FROM employees

WHERE department_id > 40

GROUP BY department_id, job_id

ORDER BY department_id;

 

여러 개의 GROUP BY 열을 나열하여 그룹과 하위 그룹에 대한 요약 결과를 반환할 수 있습니다. GROUP BY 절은 행을 그룹화하지만 결과 집합의 순서를 지정하지는 않습니다. 그룹화 순서를 지정하려면 ORDER BY 절을 사용합니다.

 

-그룹 함수를 사용한 잘못된 Query

집계 함수가 아닌 SELECT 리스트의 열이나 표현식은 GROUP BY 절에 있어야 합니다.

 

개별 항목(DEPARTMENT_ID)과 그룹 함수 (COUNT)를 동일한 SELECT 문에서 함께 사용할 경우 개별 항목(이 경우DEPARTMENT_ID)을 지정하는 GROUP BY 절을 포함시켜야 합니다. GROUP BY 절이 누락된 경우 "not a single-group group function"이라는 오류 메시지가 나타납니다. 별표(*)는 오류가 발생한 열을 가리킵니다.

 

• WHERE 절은 그룹을 제한하는 데 사용할 수 없습니다.

• 그룹을 제한하려면 HAVING 절을 사용합니다.

• WHERE 절에서 그룹 함수를 사용할 수 없습니다.

 

-HAVING 절을 사용하여 그룹 결과 제한

HAVING 절을 사용할 경우 Oracle 서버는 다음과 같이 그룹을 제한합니다.

1. 행이 그룹화됩니다.

2. 그룹 함수가 적용됩니다.

3. HAVING 절과 일치하는 그룹이 표시됩니다.

 

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

 

HAVING 절을 사용하여 표시할 그룹을 지정하면 집계 정보를 기초로 그룹을 추가로 제한할 수 있습니다. 구문에서 group_condition은 반환되는 행 그룹을 지정된 조건이 참인 그룹으로 제한합니다.

 

-HAVING 절

SELECT department_id, MAX(salary)

FROM employees

GROUP BY department_id

HAVING MAX(salary)>10000 ;

 

예제에서는 최고 급여가 $10,000가 넘는 부서에 대해 부서 번호와 최고 급여를 표시합니다. SELECT 리스트에 그룹 함수를 사용하지 않은 경우에도 GROUP BY 절을 사용할 수 있습니다. 그룹 함수의 결과를 기반으로 행을 제한하는 경우 GROUP BY 절은 물론 HAVING 절도 있어야 합니다.