[SQL] 그룹함수

그룹함수는 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수이다. 그룹함수는 select절이나 having절에서 사용할 수 있다. 그룹함수는 그룹별로 합계, 평균, 최대, 최소, 갯수 등을 구하기 위해 주로 사용한다.

SQL> select column, group_function(column)
   from table_name
   [where condition]
   [group by group_by_expression]
   [having group_condition];

◇ 그룹함수의 종류

종류	의미
COUNT	행의 갯수 출력
MAX	널을 제외한 모든 행에서 최대값 출력
MIN	널을 제외한 모든 행에서 최소값 출력
SUM	널을 제외한 모든 행의 합계
AVG	널을 제외한 모든 행의 평균값
STDDEV	널을 제외한 모든 행의 표준편차
VARIANCE	널을 제외한 모든 행의 분산
GROUPING	해당 컬럼이 그룹에 사용되었는지 여부를 1 또는 0으로 반환
GROUPING SET	한번의 질의로 여러개의 그룹화 가능

◆ COUNT 함수
테이블에서 조건을 만족하는 행의 갯수를 반환.

SQL> select count({* | [distinct | all] expr})
   from table_name;

SCOTT> select count(comm)
  2   from professor;

COUNT(COMM)
-----------
   4

SCOTT> select count(*)
  2  from professor

  COUNT(*)
----------
  8

SCOTT> select count(1)
  2  from professor

  COUNT(1)
----------
  8

count 함수 인자에 일반컬럼을 넣으면 해당 컬럼 중 널이 아닌 값의 로우수를 카운트하고, *나 숫자를 넣으면 널값을 포함한 로우수를 카운트한다.


◆ AVG, SUM, MIN, MAX, STDDEV, VARIANCE 함수
함수의 인자로 들어오는 컬럼의 데이터타입이 NUMBER인 것만 가능함.
위 함수들을 사용시 주의사항은 반평균을 구한다고 가정했을 때 결석자를 포함할 것인지 포함하지 않을 것인지에 대해서 고민해보아야 한다.
반평균 계산시 결석자를 포함하지 않는다면 원하는 결과값이 나오지 않을 수가 있다. 결석자는 결석이므로 널로 처리하지만 인원수에는 포함해야 하므로 avg(nvl(jumsu, 0))과 같은 방법으로 처리해야 한다.
모든 그룹함수는 지정한 컬럼 값에서 널값을 빼고 계산하기 때문에 이에 꼭 주의해서 써야 한다.


◆ GROUP BY절
특정 컬럼 값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절.
select절에 나온 일반컬럼(그룹함수를 뺀 나머지 컬럼)은 무조건 group by절에 포함되어야 한다.
그러나 반대로 group by절에 있는 일반컬럼은 select절에 포함되지 않아도 된다.
group by절에는 컬럼이름을 써야지 앨리어스를 쓰면 안된다.
그룹별로 출력 순서는 오름차순으로 정렬된다.(9i기준. 10g는 옵티마이저 마음임)


◆ HAVING절
select 명령문의 where 절과 비슷한 기능을 하는 것으로 group by절에서 조건 검색을 할 경우 반드시 having절을 사용해야 한다.
having절은 group by절 다음에 위치한다.


◆ ROLLUP 연산자
group by절의 그룹 조건에 따라 전체 행을 그룹화하고 각 그룹에 대해 부분합을 구하는 연산자.
group by절에 기술된 컬럼 수가 n개인 경우, rollup 연산자의 그룹핑 조합 : n+1

[문제] 학과 및 직급별 교수 수, 학과별 교수 수, 전체 교수 수를 출력하시오.
SQL> select deptno, position, count(*)
  2   from professor
  3   group by rollup(deptno, position);

   DEPTNO POSITION                 COUNT(*)
---------- -------------------- ----------
       101     교수                                      1
       101     부교수                                   1
       101     조교수                                   1
       101     전임강사                                1
       101                                                 4
       102     교수                                      1
       102     전임강사                                1
       102                                                 2
       201     조교수                                   1
       201                                                 1
       202     부교수                                   1
       202                                                 1
                                                            8

13 rows selected.

◆ CUBE 연산자
rollup에 의한 그룹 결과와 group by절에 기술된 조건에 따라 그룹 조합을 만드는 연산자.
group by절에 기술된 컬럼 수가 n개인 경우, cube 연산자의 그룹핑 조합 : 2n

[문제] 학과 및 직급별 교수 수, 학과별 교수 수, 직급별 교수 수, 전체 교수 수를 출력하시오.
SQL> select deptno, position, count(*)
  2   from professor
  3   group by cube(deptno, position);

   DEPTNO POSITION                 COUNT(*)
---------- -------------------- ----------
                                                            8
                교수                                      2
                부교수                                   2
                조교수                                   2
                전임강사                                2
       101                                                4
       101     교수                                     1
       101     부교수                                  1
       101     조교수                                  1
       101     전임강사                               1
       102                                                2
       102     교수                                     1
       102     전임강사                               1
       201                                                1
       201     조교수                                  1
       202                                                1
       202     부교수                                  1

17 rows selected.

---

[문제] emp 테이블을 사용하여 부서별로 직급별로 급여 합계를 구하고 합계를 구하세요.
SQL> insert into emp (empno, deptno, ename, sal)
  2   values (1000, 10, '홍길동', 3600);

1 row created.

SQL> insert into emp (empno, deptno, ename, sal)
  2   values (2000, 30, '일지매', 3000);

1 row created.

SQL> commit;

Commit complete.

SQL> select  deptno,
  2   sum(case lower(job) when 'clerk' then sal else 0 end) clerk,
  3   sum(case lower(job) when 'manager' then sal else 0 end) manager,
  4   sum(case lower(job) when 'president' then sal else 0 end) president,
  5   sum(case lower(job) when 'analyst' then sal else 0 end) analyst,
  6   sum(case lower(job) when 'salesman' then sal else 0 end) salesman,
  7   sum(sal) "합계"
  8   from emp
  9   where job is not null
 10  group by rollup(deptno)

   DEPTNO    CLERK  MANAGER PRESIDENT ANALYST SALESMAN        합계
---------- ---------- ---------- ---------- ---------- ---------- ----------
            10          1300          2450          5000              0               0          8750
            20          1900          2975              0          6000               0        10875
            30           950          2850               0              0          5600          9400
                         4150          8275          5000         6000           5600        29025

[문제] 교수 중에서 급여와 보직수당을 합친 금액이 가장 많은 경우와 가장 적은 경우 그리고 평균 금액을 출력하세요. (단, 보직수당이 없는 교수의 급여는 0으로 계산하고, 평균급여는 소수점 둘째자리에서 반올림하세요.)

SCOTT> select  round(max(nvl(sal+comm, 0)), 1) "최대",
  2   round(min(nvl(sal+comm, 0)), 1) "최소",
  3   round(avg(nvl(sal+comm, 0)), 1) "평균"
  4  from professor

      최대  최소     평균
---------- ---------- ----------
       520     0    223.4

SCOTT> select  max(nvl2(comm, sal+comm, sal*0)) max,
  2   min(nvl2(comm, sal+comm, sal*0)) min,
  3   round(avg(nvl2(comm, sal+comm, 0)), 1) avg
  4   from professor;

       MAX   MIN      AVG
---------- ---------- ----------
       520     0    223.4

[문제] temp 테이블의 자료를 열 단위로 사번, 이름을 3명씩 출력하시오.
SCOTT> select  ceil(rownum/3) no,
  2   max(decode(mod(rownum,3), 1, emp_id, null)) 사번1,
  3   max(decode(mod(rownum,3), 1, emp_name, null)) 이름1,
  4   max(decode(mod(rownum,3), 2, emp_id, null)) 사번2,
  5   max(decode(mod(rownum,3), 2, emp_name, null)) 이름2,
  6   max(decode(mod(rownum,3), 0, emp_id, null)) 사번3,
  7   max(decode(mod(rownum,3), 0, emp_name, null)) 이름3
  8   from temp
  9   group by ceil(rownum/3)
 10  order by 1

 NO 사번1 이름1       사번2 이름2     사번3 이름3
---------- ---------- ---------- ---------- ---------- ---------- ----------
  1   19970101 김길동    19960101 홍길동  19970201 박문수
  2   19930331 정도령    19950303 이순신  19966102 지문덕
  3   19930402 강감찬    19960303 설까치  19970112 연흥부
  4   19960212 배뱅이    20000101 이태백  20000102 김설악
  5   20000203 최오대    20000334 박지리  20000305 정북악
  6   20006106 유도봉    20000407 윤주왕  20000308 강월악
  7   20000119 장금강    20000210 나한라

7 rows selected.