[Oracle] 오라클 그룹 함수(집계 함수)

1. 그룹함수의 종류,특징,주의사항
2. ROLLUP, CUBE, GROUPING SETS, GROUPNG
3. HAVING 그룹함수 조건절
4. 누적(누계)

 

---

그룹함수의 종류, 특징, 주의사항

1. sum          --합계
2. avg           --평균
3. max          --최대값
4. min           --최소값
5. count        --select 되어서 나오는 결과물의 행의 개수
6. variance   --분산
7. stddve      --표준편차

-분산 : 분산의 제곱근이 표준편차(평균에서 떨어진 정도)

 표준편차 : 표준편차의 제곱이 분산 (평균과의 차액)

 분산과 표준편차는 어떤 의사결정시 도움이 되는 지표이다.

 

- 그룹함수(집계함수)에서는 null 이 있으면 무조건 null을 제외시킨 후 연산을 한다.

   => 그래서 AVG를 사용할 때에 조심, AVG는 SUM()/COUNT()인데 COUNT에 NULL 값이 있으면 AVG값이 다르게 나올 수 있다. 이때는 AVG를 쓰지 않고 NULL값에 NVL처리를 해주어서 SUM()과 COUNT() 값을 따로 구해주도록 한다.

COUNT()로 되어지는 컬럼에 NULL 값이 없으면 AVG사용가능.

 

- 그룹함수(집계함수)를 사용하면 1개의 결과값을 가진다.

 

GROUP BY 절

- GROUP BY 절의 일반적인 사용.

SELECT [ GROUP BY 절에 지정된 컬럼1 [ GROUP BY 별로 집계할 값]

FROM 테이블명 GROUP BY 그룹으로 묶을 컬럼의 값

 

- ORDER BY 도 정렬기준 1,2,3,4 등을 줄 수 있는것처럼 , GROUP BY 도 1차그룹,2차그룹 등으로 묶을 수 있다.

ROLLUP, CUBE, GROUPING SETS, GROUPNG

• ROLLUP

ROLLUP(1번째인자, 2번째인자) 를 하면 1번째인자별 2번째인자의 합, 총합을 보여준다.

 

• CUBE

ROLLUP 함수와 비슷한 기능을 하지만, CUBE 함수는 그룹핑 컬럼이 가질 수 있는 모든 경우의 수에 대하여 소계와 총계를 생성한다.

 

CUBE 예제코드

 select decode( grouping(department_id), 0, NVL( to_char(department_id), '인턴') , '전체' ) AS 부서번호
        , decode( grouping(gender), 0, gender, '전체') AS 성별
        , count(*) AS 인원수
        , TO_CHAR( round( count(*)/(select count(*) from employees) * 100 , 1), '990.0') AS 퍼센티지
from
(select department_id
     , case when substr(jubun, 7, 1) in('1','3') then '남' else '여' end AS GENDER
from employees) V
group by CUBE(department_id, gender);

GRUOPING

- GROUPING은 ROLLUP이나 CUBE, GROUPING SETS 등의 그룹함수에 의해 컬럼 값이 소계나 총합등 집계된 데이터일 경우 1을 반환하고, 만약 집계된 데이터가 아니면 0을 반환하는 함수이다.

 

GROUPING과 ROLLUP을 이용한 예제코드

select decode( grouping(department_id), 0, NVL( to_char(department_id), '인턴') , '전체' ) AS 부서번호
    , decode( grouping(gender), 0, gender, '전체') AS 성별
    , count(*) AS 인원수
    , TO_CHAR( round( count(*)/(select count(*) from employees) * 100 , 1), '990.0') AS 퍼센티지
from
(select department_id
     , case when substr(jubun, 7, 1) in('1','3') then '남' else '여' end AS GENDER
from employees) V
group by ROLLUP(department_id, gender);


 /*
 결과
      ------------------------------------
       부서번호    성별    인원수    퍼센티지
      ------------------------------------
        ...       ...     ....      ....
        50        남       23       21.5
        50        여       22       20.6
        50       전체      45       42.1
        60        남        4        3.7
        60        여        1        0.9
        60       전체       5        4.7
        ...       ...     ....      ....
        전체	     전체	  107	    100.0
  */

GRUOPING SETS

- GROUPING SETS는 여러 그룹핑 쿼리를 지정하여서 소계,합계를 지정할 수 있다.

 

group by rollup(컬럼1, 컬럼2) 은
group by grouping sets( (컬럼1, 컬럼2), (컬럼1), () ) 와 같다.

 

GROUPING SETS 예제코드

select decode( grouping(department_id), 0, NVL( to_char(department_id), '인턴') , '전체' ) AS 부서번호
        , decode( grouping(gender), 0, gender, '전체') AS 성별
        , count(*) AS 인원수
        , TO_CHAR( round( count(*)/(select count(*) from employees) * 100 , 1), '990.0') AS 퍼센티지
from
(select department_id
     , case when substr(jubun, 7, 1) in('1','3') then '남' else '여' end AS GENDER
from employees) V
group by GROUPING SETS( (department_id, gender), (department_id), (gender), () )
order by 1;

 

HAVING 그룹함수 조건절

group by 절을 사용하여 그룹함수의 값을 나타내었을때
그룹함수의 값이 특정 조건에 해당하는 것만 추출하고자 할 때는 where 절을 사용하는 것이 아니라
having 그룹함수조건절 을 사용해야 한다.

 

HAVING 절 예제코드

--- employees 테이블에서 사원이 10명 이상 근무하는 부서번호와 그 인원수를 나타내세요.
select department_id AS 부서번호
    , count(*) AS 인원수 
from employees
group by department_id
having count(*) >= 10
order by 2 asc;

누적(누계)

- SUM(누적되어야할 컬럼명) OVER(ORDER BY (누적되어질 기준이 되는 컬럼명)  ASC[DESC] )

 

- SUM(누적되어야할 컬럼명) OVER(PARTITION BY (그룹화 되어질 컬럼명) ORDER BY  (누적되어질 기준이 되는 컬럼명) ASC[DESC] )