7일차 # 4-3: Group Functions = Multiple-Row Functions

Group Functions는 group 당 하나의 결과를 주기 위해 로우의 세트에 작용한다. 

이들 세트는 테이블 전체이거나 그룹으로 나누어진 테이블이 될 수 있다.

 

Types of Group Functions

AVG([DISTINCT|ALL]n)

          ## Average value of n, ignoring null values

COUNT({*|DISTINCT|ALL]expr})

          ## Number of rows, where expr evaluates to something other than null

              (count all selected rows using *, including duplicates and rows with nulls)

MAX([DISTINCT|ALL]expr)

          ## Maximum value of expr, ignoring null values

MIN([DISTINCT|ALL]expr)

          ## Minimum value of expr, ignoring null values

STDDEV([DISTINCT|ALL]x)

          ## Standard deviation of n, ignoring null values

SUM([DISTINCT|ALL]n)

          ## Sum values of n, ignoring null values

VARIANCE([DISTINCT|ALL]x)

          ## Variance of n, ignoring null values

 

Group Functions Syntax

SELECT      [column,] group_function(column), ...

FROM         table

[WHERE      condition]

[GROUP BY column]

[GROUP BY column];

 

DISTINCT는 중복된 값은 하나만을 사용한다. ALL은 중복된 값을 포함한

모든 값을 사용한다.

expr 인수의 데이터 타입은 CHAR, VARCHAR2, NUMBER, DATE가 될 수 있다.

모든 group functions는 null value를 무시한다. null value를 치환하기 위해서는

NVL, NVL2, COALESCE functions를 사용한다.

Oracle Server는 GROUP BY절이 사용될 때 묵시적으로 오름차순으로 결과를

정렬한다.

 

COUNT function

COUNT function은 세가지 포맷을 갖는다.

- COUNT(*)

- COUNT(expr)

- COUNT(DISTINCT expr)

COUNT(*)은 값이 로우의 컬럼들의 값이 중복되거나, null 값을 포함하고 있는

테이블의 모든 로우의 수를 반환한다. 만약 WHERE 절이 포함되어 있으면, 그

조건을 만족하는 로우의 수를 반환한다.

COUNT(expr)은 expr로 기술된 컬럼에서 null 값이 아닌 로우의 수만을 반환한다.

COUNT(DISTINCT expr)은 expr로 기술된 컬럼에서 null이 아니고, 중복된 값은

하나로 처리한 로우의 수를 반환한다.

 

Group Functions는 null을 무시한다

SELECT AVG(commission_pct)

FROM employees;

이 샘플에서 averagesms COMMITION_PCT 컬럼에 유효한 값이 저장된 로우에

대해서만 계산을 수행한다. 즉 average는 commition을 받는 직원의 수로 직원들

에게 지불된 commition의 총합을 나누어 계산된다.

 

SELECT AVG(NVL(commission_pct, 0))

FROM employees;

NVL function은 group functions가 null value를 포함하도록 한다. 이 샘플에서

average는 COMMISSION_PCT 컬럼에 저장된 null 값을 무시하고 모든 로우에

대해서 계산을 수행한다. 즉 average는 회사의 모든 직원들의 수로 모든 직원들

에게 지급된 commission의 총합을 나누어 계산된다.

 

Group functions를 사용한 잘못된 질의

SELECT list에서 Group function이 아닌 column이나 expression은 반드시

GROUP BY 절에 있어야 한다.

SELECT department_id, COUNT(last_name)

FROM employees;

이렇게 하면 오라클은 '아악~, 안돼, 안돼!'라는 메시지를 날린다.

 

WHERE 절에서 그룹을 제한할 수 없다.

그룹을 제한하기 위해서는 HAVING 절을 사용해야 한다.

WHERE 절에 group functions를 사용할 수 없다.

SELECT department_id, AVG(salary)

FROM    employees

WHERE  AVG(salary) > 8000

GROUP BY department_id;

이렇게 하면 오라클은 역시 '아악~, 안돼, 안돼!'라는 메시지를 날린다.

 

Group Results를 제외하기(Excluding Group Results) : HAVING 절

Groups를 제한하기 위해 HAVING 절을 사용한다.

1. Rows가 분류된다.

2. Group function이 적용된다.

3. HAVING 절에 맞는 groups가 표시된다.

WHERE절이 선택된 rows를 제한하듯이, HAVING 절은 groups를 제한한다.

SELECT          column, group_function

FROM             table

[WHERE          condition]

[GROUP BY     group_by_expression]

[HAVING         group_by_condition]

[ORDER BY     column];

예)

SELECT        department_id, MAX(salary)

FROM           employees

GROUP BY    department_id

HAVING        MAX(salary)>10000;

출처:http://blog.naver.com/john1227?Redirect=Log&logNo=60010245414

[출처] Oracle - Group Functions|작성자 태양