2012.01.05 SQL 3일차(그룹함수, rollup, cube)

그룹함수의 종류

 

종류	의미
COUNT	행의 개수 출력 (칼럼이름 직접 적을 시 NULL제외한 행의 개수출력, * 사용시 NULL포함한 행의 개수)
MAX	NULL을 제외한 모든행에서 MAX출력
MIN	NULL을 제외한 모든행에서 MIN출력
SUM	NULL을 제외한 모든 행의 합계
AVG	NULL을 제외한 모든 행의 평균값
STDDEV	NULL을 제외한 모든 행의 표준편차
VARIANCE	NULL을 제외한 모든 행의 분산 값
GROUPING	해당 칼럼이 그룹에 사용되었는지 여부를 1 OR 0으로 반환
GROUPING SET	한번의 질의로 여러개의 그룹화 가능

 

 

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

예제   1  select count(*), count(comm), count(position), count(distinct position)   2* from professor SQL> /   COUNT(*) COUNT(COMM) COUNT(POSITION) COUNT(DISTINCTPOSITION) ---------- ----------- --------------- -----------------------          8           4               8                       4 * COUNT(*), COUNT(COMM)의 결과값이 다른 이유 : COUNT(*)는 NULL포함한 COUNT, COUNT(COMM)은 NULL을 제회한 COUNT * COUNT안에 DISTINCT 사용가능

 

 

AVG, SUM, MIN, MAX( [DISTINCT | ALL] expr )

* NUMBER타입만 사용가능

예제 SQL> select avg(height), min(height), max(height), sum(height)   2  from student; AVG(HEIGHT) MIN(HEIGHT) MAX(HEIGHT) SUM(HEIGHT) ----------- ----------- ----------- -----------    171.5625         160         186        2745

* 위 함수들은 그냥 쓸 때 NULL값때문에 특히 조심해서 써야한다.

 

예를 들어,

NAME  RANK

----  ----

  A      1

  B      2

  C   

  D      3

  E      4

 

의 경우, AVG(RANK)를 하게 되면,

중간의 NULL값을 뺴버리고  (1+2+3+4) / 4를 해버리기 떄문에 원치 않은 결과가 나올 수 있다.(SUM, MIN, MAX 마찬가지)

그렇기 떄문에 상황에 따라 NULL의 값을 다른 값으로 변환하여 계산해야 한다.

NULL값 예제  SQL> select avg( nvl2(comm,comm,100) ), avg(comm) from emp; AVG(NVL2(COMM,COMM,100))  AVG(COMM) ------------------------ ----------               228.571429        550 * AVG만 적용한것과 NULL을 고려한 AVG사용은 결과값이 다름을 확인

 

데이터 그룹생성

 

GROUP BY 절

특정 칼럼값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절

 

사용규칙 :

1. GROUPING전에 WHERE절 사용해서 그룹대상 집합을 먼저 선택가능

2. SELECT절에 그룹함수가 적용되지 않은 칼럼은 모두 GROUP BY절에 적어줘야한다.(LITERAL, 등등 모두)

* 무조건 컬럼의 본명을 적어줘야 한다(alias로 별명만들어 준거는 적어주면 안되요)

3. GROUP BY절에 있는 칼럼이 SELECT절에 없어도 된다. (2번의 반대상황에서는 상관이 없습니다)

(*9i까지는 GROUP BY하면 자동으로 정렬을 사용하기때문에 고생할 수 있습니다. 10g부터는 정렬하지않고 바로바로 뽑아옵니다.)

4. WHERE절에는 GROUP함수를 쓸 수 없음.

5. 4번 대신에 HAVING절 사용

6. ROLLUP, CUBE를 사용해서 좀 더 원하는 출력을 볼 수 있다.(설명은 잠시 뒤에)

 

 

HAVING 절

WHERE절에 GROUP함수를 쓸 수가 없으므로, 대신해서 쓰는 절

규칙 : GROUP BY절 아래에 위치합니다  (WHERE절은 GROUP BY절 위에 위치합니다.)

 

 

ROLLUP, CUBE : 컬럼별로 통계를 출력할 때 사용합니다.

 참고 : http://blog.naver.com/hjc426?Redirect=Log&logNo=130029276284

ROLLUP설명

; 영어로 ~을 말다, 는 의미로 쓰임에 미묘한 차이점이 있습니다. 일단 예제부터 살펴보시죠

(헷갈리시면 아래 표 3번째 행은 보지마세요, 2번째 행에서 결과만 외우세요)

 

1  select grade, deptno, avg(height)   2  from student   3* group by  rollup(grade, deptno)		1  select grade, deptno, avg(height)   2  from student   3* group by  rollup(grade), deptno		1  select grade, deptno, avg(height)   2  from student   3* group by  grade, rollup(deptno)		1  select grade, deptno,  avg(height)   2  from student   3* group by  rollup(grade), rollup(deptno)
G     DEPTNO AVG(HEIGHT) - ---------- ----------- 1        101         177 1        102         160 1        201   179.333333 1               175.333333 2        101  164.666667 2        102         164 2        201         166 2                    164.8 3        101         170 3        102         171 3                    170.5 4        101       175.5 4        102         177 4                      176                    171.5625 rollup(A,B) :  A별 결과 + 전체결과		G     DEPTNO AVG(HEIGHT) - ---------- ----------- 1        101         177 2        101  164.666667 3        101         170 4        101       175.5           101     171.125 1        102         160 2        102         164 3        102         171 4        102         177           102         168 1        201  179.333333 2        201         166           201         176 (추론해 볼때 deptno는 3가지 종류가 있네요) rollup(A), B : B별 결과		G     DEPTNO AVG(HEIGHT) - ---------- ----------- 1        101         177 1        102         160 1        201  179.333333 1             175.333333 2        101  164.666667 2        102         164 2        201         166 2                  164.8 3        101         170 3        102         171 3                  170.5 4        101       175.5 4        102         177 4                    176 (추론해 볼때 grade는 4가지종류가 있네요) A, rollup(B) :  A별 결과		G     DEPTNO AVG(HEIGHT) - ----------        ----------- 1                175.333333 2                  164.8 3                  170.5 4                    176                   171.5625 1        101         177 2        101  164.666667 3        101         170 4        101       175.5           101     171.125 1        102         160 2        102         164 3        102         171 4        102         177           102         168 1        201  179.333333 2        201         166           201         176 rollup(A), rollup(B) : A별 결과 + 전체 + B별 결과
rollup(grade, deptno) 을 사용하게 되면  , 1. deptno를 말아서 grade별로 결과를 내주고, (grade별로 deptno들의 avg를 계산)   2. 전체의 avg를 계산한다.   즉, ROLLUP(A,B) 하게 되면 1. A별로 B값들을, AVG나 SUM등등 계산 2. A, B 모두 고려한 계산   *rollup안의 컬럼 들의 순서 상관있습니다		rollup(grade), deptno 을 사용하게 되면,  grade를 말아서 deptno별로 결과를 내줍니다. (deptno별로 grade들의 avg계산)   즉, ROLLUP(A), B 면 B값 별로 A값들을 AVG, SUM등등 계산 (테이블 전체의 계산은 해주지 않습니다.)   * deptno, rollup(grade)해도 결과는 같습니다. 심지어 출력 순서도 똑같습니다.   * rollup(deptno),            grade, profno;  경우는 deptno를 말아서 grade, profno를 둘다 고려해서 grade, profno가 같은 자료별로 deptno의 결과를 출력한다.		grade, rollup(deptno) 를  사용하게 되면   deptno를 말아서 grade별로 결과를 내줍니다. (grade별로 deptno들의 avg계산)   즉  A, ROLLUP(B)면 A값 별로 B값들의 AVG, SUM등등을 계산 (테이블 전체의 계산은 해주지 않습니다.)   * 헷갈리시면, 그냥 왼쪽의 경우와 같다고 생각하시면 됩니다. ROLLUP이 적용되는 컬럼만 변경되었을뿐 다른 건 다 똑같습니다.		rollup(grade), rollup(deptno)  이 경우는 두 컬럼 전부 rollup을 사용한 경우인데,  1. deptno를 말아서 grade별로 결과는 내고 (grade별로 deptno들의 avg계산)   2. grade를 말아서 deptno별로 결과를 내고 (deptno별로 grade들의 avg계산)   3. 전체의 avg계산한다.

 

 

 

다른 예를 적용시키면,

professor테이블에서 position과 deptno를 이용해서 sal의 평균을 보고 싶은데,

professor전체의 sal평균과, position별로 sal의 평균을 보고 싶다면   SELECT position, deptno, avg(sal) FROM PROFESSOR GROUP BY rollup(position, deptno); 보고 싶은 분류를 앞에다 써주세요

반대로 professor전체의 sal평균과, deptno별로 sal평균을 보고 싶다면   SELECT position, deptno, avg(sal) FROM PROFESSOR GROUP BY rollup(deptno, position); 보고 싶은 분류를 앞에다 써주세요

POSITION                 DEPTNO   AVG(SAL) -------------------- ---------- ---------- 교수                        101         500 교수                        102         450 교수                                      475 부교수                      101        420 부교수                      202        400 부교수                                    410 조교수                      101        360 조교수                      201        320 조교수                                    340 전임강사                    101        210 전임강사                    102        240 전임강사                                  225                                           362.5 13 rows selected.

POSITION              DEPTNO     AVG(SAL) -------------------- ----------        ---------- 교수                         101            500 부교수                      101           420 조교수                      101          360 전임강사                   101          210                                101          372.5 교수                         102            450 전임강사                   102           240                                102           345 조교수                      201           320                                201           320 부교수                     202           400                                202           400                                               362.5 13 rows selected.

 

 

CUBE 설명

CUBE(A,B)  =  ROLLUP(A), ROLLUP(B)와 흡사하다고 생각하시면 됩니다.

참고문서에는

GROUP BY절에 기술 된 칼럼수가 N개일 경우,

ROLLUP 연산자의 그룹핑 조합 : N+1,

CUBE 연산자의 그룹핑 조합 : 2N

이라고 쓰여 있습니다.

 http://blog.naver.com/hjc426?Redirect=Log&logNo=130029276284

; 위 사이트 참조하시는게 이해가 빠르실 겁니다.

예제 SQL> select deptno, grade, avg(weight) from student   2  group by cube(deptno, grade);     DEPTNO G AVG(WEIGHT) ---------- - -----------                  66.8125             ; 전체의 평균            1  64.6666667           ; 1학년의 평균            2        53.4              ; 2학년의 평균            3          79               ; 3학년의 평균            4  85.3333333           ; 4학년의 평균        101            68              ; deptno가 101인 학생들의 평균        101 1          62        101 2          56        101 3          88        101 4          82        102          69.5             ; deptno가 102인 학생들의 평균        102 1          68        102 2          48        102 3          70        102 4          92        201         61.75             ; deptno가 201인 학생들의 평균        201 1  65.3333333        201 2          51 18 rows selected. 굵게 표시된 줄들이 cube에 의해서 추가된 줄입니다.