과정 목표

본 과정은 함수에 대해서 더 배웁니다. 행의 그룹에 대해서 평균과 같은 통계 정보를 얻는데 초점을 둡니다. 한 테이블의 행들을 더 적은 단위의 집합으로 그룹화 시키는 방법과  행의 그룹에 대해서 조건을 명시하는 방법을 토의합니다.

 

그룹 함수

단일 행 함수와는 달리 그룹 함수는 그룹 당 하나의 결과가 주어지도록 행의 집합에 대해 연산합니다. 이러한 집합은 전체 테이블이거나 특정 그룹일 수 있습니다.

그룹 함수 (계속)

각각의 함수는 인수(argument)를 받습니다. 다음의 테이블은 구문형식에서 사용할 수 있는 옵션을 보여줍니다.

함  수

설  명

AVG([DISTINCT|ALL]n)

null 값을 무시한 n 의 평균 값

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

행의 수, expr은 null 값을 제외하고 계산합니다. *를 사용하여 중복되거나 null 인 행들을 포함하여 모든  행을 계산합니다.

MAX([DISTINCT|ALL]expr)

Null 값을 무시한 expr 의 최대 값

MIN([DISTINCT|ALL]expr)

Null 값을 무시한 expr 의 최소 값

STDDEV([DISTINCT|ALL]x)

Null 값을 무시한 n 의 표준편차

SUM([DISTINCT|ALL]n)

Null 값을 무시한 n 의 합계

VARIANCE([DISTINCT|ALL]x)

Null 값을 무시한 n 의 분산

 

그룹 함수

숫자 데이터를 저장할 수 있는 열에 대해서 AVG, SUM, MIN 그리고 MAX  함수를 사용할 수 있습니다. 위의 예는 모든 판매원에 대해서 급여의 평균, 최고액, 최저액 그리고 합계를 디스플레이 합니다.

 

그룹 함수 (계속)

모든 데이터형에 대해서 MAX와 MIN 함수를 사용할 수 있습니다. 위의 예는 가장 먼저 입사한 종업원과 가장 나중에 입사한 종업원을 디스플레이 합니다.
아래의 예는 모든 종업원에 대해서 알파벳순으로 제일 빠른 종업원의 이름과 제일 늦은 종업원을 디스플레이 합니다.

SQL> SELECT     MIN(ename), MAX(ename)
  2  FROM       emp;


MIN(ENAME) MAX(ENAME)
---------- ----------
ADAMS      WARD

주: AVG, SUM, VARIANCE 그리고 STDDEV 함수는 숫자 데이터형에서만 사용될 수 있습니다.

COUNT 함수

COUNT 함수는 두 가지 형식이 있습니다:

COUNT(*) 는 중복되는 행과 null 값을 포함하는 행을 포함하여 테이블 행의 수를 리턴합니다.
반대로, COUNT(expr) 는 expr 에 의해 인식된 열에서 null 이 아닌 행의 수를 리턴합니다.
위의 예는 부서 30의 종업원 수를 디스플레이 합니다.

 

COUNT 함수 (계속)

위의 예는 보너스를 받는 부서 30의 종업원 수를 디스플레이 합니다. 부서 30의 두 명의 종업원이 보너스를 받을 수 없고 COMM 열에 null 값을 포함하므로 행의 전체 수가 4가 되었다는 것을 주목하십시오.

예

EMP 테이블의 부서 수를 디스플레이 합니다.

SQL> SELECT     COUNT(deptno)
  2  FROM       emp;

COUNT(DEPTNO)
-------------
           14

EMP 테이블의 중복되지 않는 부서 수를 디스플레이 합니다.

SQL> SELECT     COUNT(DISTINCT (deptno))
  2  FROM       emp;

COUNT(DISTINCT(DEPTNO))
-----------------------
                      3

 

그룹 함수와 Null 값

COUNT (*) 를 제외한 모든 그룹 함수는 열에 있는 null 값을 무시합니다. 위의 예에서 평균은 테이블의 COMM 열에 저장되어 있는 타당한 값의 행에 대해서만 계산됩니다. 평균은 모든 종업원에게 지급되는 보너스를  보너스를 받는 종업원의 수(4)로 나누어 계산합니다.

 

그룹 함수와 Null 값 (계속)

NVL 함수는 그룹 함수가 null 값을 포함하는 것을 가능하게 합니다. 위의 예에서 평균은 테이블의 COMM 열에 저장된 값이 null 인지에 상관 없이 모든 행을 계산합니다. 평균은 모든 종업원에게 지급되는 보너스를 전체 종업원의 수(14)로 나누어 계산됩니다.

 

데이터 그룹

지금까지 모든 그룹 함수는 테이블을 하나의 큰 그룹으로 다루었습니다. 여기서는 테이블의 데이터를 보다 작은 그룹으로 나누는 것이 필요합니다. 이것은 GROUP BY 절을 사용하여 수행할 수 있습니다.

 

GROUP BY 절

테이블의 행을 그룹으로 나누기 위해서 GROUP BY 절을 사용할 수 있습니다. 그런 다음에 각각의 그룹에 대해서 통계정보를 리턴하기 위해서 그룹 함수를 사용할 수 있습니다.

구문형식에서:

        group_by_expression     행을 그룹핑 하기 위한 기준이 될 열 값을 명시합니다.

지침

 

GROUP BY 절 (계속)

GROUP BY 절을 사용할 때, 그룹 함수에 포함되지 않는 SELECT 절의 모든 열들은  GROUP BY 절에 포함되도록 합니다. 위의 예는 각각의 종업원에 대해서 부서 번호와 평균 급여를 디스플레이 합니다. 다음은 GROUP BY 절을 포함하는 위의 SELECT 문장을 계산하는 방법입니다.

 

GROUP BY 절 (계속)

GROUP BY 열은 SELECT 절에 꼭 있어야 하는 것은 아닙니다. 예를 들면, 위의 SELECT 문장은 디스플레이 하지 않는 각각의 부서에 대해서 평균 급여를 디스플레이 합니다. 그러나 부서번호가 없는 결과는 의미가 없어 보입니다.
ORDER BY 절에 그룹 함수를 사용할 수 있습니다.
 

SQL> SELECT     deptno, AVG(sal)
  2  FROM       emp
  3  GROUP BY   deptno
  4  ORDER BY   AVG(sal);


    DEPTNO     AVG(SAL)
---------- ------------
        30    1566.6667
        20         2175
        10    2916.6667

 

그룹내의 그룹

때때로 그룹내의 그룹에 대한 결과를 알아야 할 필요가 있습니다. 위의 예는 각 부서내의 업무별로 급여 합계를 디스플레이 하는 리포트를 보여 줍니다.
EMP 테이블은 먼저 부서번호로 그룹화한 다음에 업무로 그룹화합니다. 예를 들면, 부서 20에 있는 두 개의 Clerk은 함께 그룹화 되었고, 단일 결과(급여 합계)는 그룹내의 모든 판매원에 대해서 산출되었습니다.

 

그룹내의 그룹 (계속)

GROUP BY 절에 하나 이상의 열을 사용하여 그룹과 서브 그룹에 대한 통계 결과를 리턴할 수 있습니다. GROUP BY 절에 있는 열의 순서에 의해서 결과의 디폴트 정렬 순서를 결정할 수 있습니다. 다음은 GROUP BY 절을 포함하는 위의 SELECT 문장이 계산되는 단계를 보여 줍니다.

SUM 함수는 각각의 부서번호 그룹 내의 모든 업무에 대한 급여 열에 적용됩니다.

 

그룹 함수를 잘못 사용한 질의

같은 SELECT 문장에 개별적인 열(DEPTNO)과 그룹 함수(COUNT)를 혼합해서 사용할 때, 개별적인 열(이 경우에는 DEPTNO)을 명시하는 GROUP BY 절을 포함해야 합니다. GROUP BY 절이 없으면 “not a single-group group function” 이라는 에러 메시지가 나타나고 ‘*’로 잘못 된 열을 가리킵니다. 위의 에러는 GROUP BY 절을 추가하여 정정할 수 있습니다.

SQL> SELECT     deptno, COUNT(ename)
  2  FROM       emp
  3  GROUP BY   deptno;


    DEPTNO     COUNT(ENAME)
----------     ------------
        10                3
        20                5
        30                6

주: 그룹 함수가 아닌 SELECT 절의 어떤 열이나 표현식은 GROUP BY 절에 있어야 합니다.

그룹 함수를 잘못 사용한 질의 (계속)

WHERE 절을 사용하여 그룹을 제한할 수 없습니다. 위의 SELECT 문장은 평균 급여가 $2000 이상인 부서의 평균 급여를 제한하기 위해서 WHERE 절을 사용했기 때문에 에러가 발생합니다.
위의 에러는 HAVING 절을 사용하여 정정할 수 있습니다.

SQL>    SELECT  deptno, AVG(sal)
  2     FROM    emp
  3     GROUP BY deptno
  4     HAVING  AVG(sal) > 2000;


    DEPTNO       AVG(SAL)
---------- --------------
        10      2916.6667
        20           2175

 

그룹 결과 제한

WHERE 절을 사용하여 검색하는 행을 제한하는 것과 똑같은 방법으로 HAVING 절을 사용하여 그룹을 제한합니다. 최대 급여가 $2900 이상인 부서만을 나타내기 위해서 각 부서의 최대 급여를 알고자 한다면 다음의 두 가지 일을 해야 합니다:

    1.  부서번호로 그룹핑하여 각 부서의 최대 급여를 알아냅니다.
    2.  최대 급여가 $2900 이상인 부서들로 그룹을 제한합니다.
 

HAVING 절

HAVING 절을 사용하여 디스플레이될 그룹을 명시합니다. 그러므로, 그룹 정보를 바탕으로 그룹을 한층 더 제한합니다.

구문형식에서:

        group_condition         
  행의 그룹을 제한하여 명시된 조건이 TRUE인 그룹들만 리턴되도록 합니다.

오라클 서버는 HAVING 절을 사용할 때 다음의 단계를 수행합니다:

HAVING 절은 GROUP BY 절 앞에 올 수 있지만, GROUP BY 절을 먼저  두는 게 더 논리적이므로 권장됩니다. HAVING 절이 SELECT 절에 있는 그룹에 적용되기 전에 그룹은 구성되고 그룹 함수는 계산됩니다.

HAVING 절 (계속)

위의 예는 최대 급여가 $2900 이상인 부서에 대해서 부서번호와 최대 급여를 디스플레이  합니다.
SELECT 절에 그룹 함수를 사용하지 않고 GROUP BY 절만 사용할 수 있습니다.
그룹 함수의 결과를 근거로 행을 제한한다면, HAVING 절 뿐만 아니라 GROUP BY 절을 사용해야 합니다.

다음의 예는 최대 급여가 $2900 이상인 부서에 대해서 부서번호와 평균 급여를 디스플레이 합니다.

SQL> SELECT     deptno, AVG(sal)
  2  FROM       emp
  3  GROUP BY   deptno
  4     HAVING  MAX(sal) > 2900;


   DEPTNO  AVG(SAL)
--------- ---------
       10 2916.6667
       20      2175

 

HAVING 절 (계속)

위의 예는 전체 수입이 $5000을 초과하는 각 업무에 대해서 업무와 월 급여 합계를 디스플레이 합니다. 예는 판매원은 제외하고 월 급여 합계로 결과를 정렬합니다.

 SELECT 문의 결과에서 group을 제한하기 위해 사용하는 절은?
A. WHERE
B. HAVING
C. ORDER BY
D. GROUP BY
답

X 정답:B


X 정답:A


X 정답:C


X 정답:C


X 정답:A


X 정답:BCD