2012.01.05 SQL 3일차(연습노트) ; 그룹함수테크닉,rollup이용,cube이용, having이용예 등등

통계만 출력하기

구하고자 하는 출력이 

'학과별 평균 몸무게와 학년 별 평균 몸무게를 구하세요' 이고,

performance생각 안하고, 다른 군더더기 없이 딱 원하는 결과값만 출력하고 싶다면 어떻게 해야할까?

단순히 group by 해서 cube나 rollup해서는 되지 않는다.

여기에 집합연산자를 추가해서 중복되는 부분을 빼야 한다.

아래 쿼리를 보자. 

1  select deptno, grade, avg(weight) from student   2  group by cube(grade, deptno)   3  intersect   4  (   5  select deptno, grade, avg(weight) from student   6  group by rollup(grade), deptno   7  union   8  select deptno, grade, avg(weight) from student   9  group by rollup(deptno), grade  10  )  11  minus  12  select deptno, grade, avg(weight) from student  13* group by grade, deptno

DEPTNO G AVG(WEIGHT) ---------- - -----------        101            68        102          69.5        201         61.75               1  64.6666667               2        53.4               3          79               4  85.3333333 7 rows selected.

추가 :

grouping sets를 써도 위의 결과와 동일하게 된다.

select deptno, grade, avg(weight) from student

group by grouping sets(deptno, grade);                        ; 전체의 평균까지 보고 싶다면 grouping sets(deptno, grade, () );

==================================================================================

==================================================================================

학과별 교수 수가 2명 이하인 학과 번호, 교수 수를 출력 하세요

1  select deptno, count(name) from professor   2  group by deptno   3* having count(name) <= 2   4  /

DEPTNO COUNT(NAME) ---------- -----------        201           1        102           2        202           1

having절을 사용해서 출력할 행을 선택하는 예(그룹함수는 where에 사용하지 못하기 때문에 대신 having사용)

==================================================================================

==================================================================================

직급별로 평균 급여가 300보다 크면 ‘우수’, 작거나 같으면 ‘보통’을 출력하세요.

1  select position, avg(sal),   2  CASE WHEN avg(sal) > 300 THEN '우수'   3     WHEN avg(sal) <= 300 THEN '보통'   4  END 평가   5  from professor   6* group by position

POSITION               AVG(SAL) 평가 -------------------- ---------- ---- 교수                        475 우수 부교수                      410 우수 조교수                      340 우수 전임강사                    225 보통

CASE문의 조건에 그룹함수를 사용하는 예

==================================================================================

==================================================================================

Temp 테이블을 사용하여 아래와 같이 출력하시오.

      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    나한라

일단 테이블의 구조는 다음과 같다.

EMP_NAME   EMP_ID

---------- ----------

김길동       19970101

홍길동       19960101

박문수       19970201

정도령       19930331

이순신       19950303

지문덕       19966102

강감찬       19930402

설까치       19960303

연흥부       19970112

배뱅이       19960212

이태백       20000101

김설악       20000102

최오대       20000203

박지리       20000334

정북악       20000305

유도봉       20006106

윤주왕       20000407

강월악       20000308

장금강       20000119

나한라       20000210

1. 일단 생각할 수 있는 방법은 다음과 같다.

1  select ceil(rownum/3),   2  decode( mod(rownum,3), 1, emp_id ) 사번1,   3  decode( mod(rownum,3), 1, emp_name ) 이름1,   4  decode( mod(rownum,3), 2, emp_id ) 사번2,   5  decode( mod(rownum,3), 2, emp_name ) 이름2,   6  decode( mod(rownum,3), 0, emp_id ) 사번3,   7  decode( mod(rownum,3), 0, emp_name ) 이름3   8* from temp

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

상당히 흡사한 결과가 나왔지만 그래도 원하는 출력은 아니다.

위 결과에서 ceil(rownum/3)을 group으로 묶어주면 원하는 결과가 나올 것 같은데 여기선 그룹함수가 쓰여지지 않았으니 group by 로 묶어 줄 수도 없다.

그래서 하나 나온 꼼수가 그냥 아무 의미없는 그룹함수를 ceil을 제외한 나머지 칼럼에 써주는 것이다.

다음 쿼리를 보자

1  select ceil(rownum/3) NO,   2  max( decode( mod(rownum,3), 1, emp_id )) 사번1,   3  max( decode( mod(rownum,3), 1, emp_name )) 이름1,   4  max( decode( mod(rownum,3), 2, emp_id )) 사번2,   5  max( decode( mod(rownum,3), 2, emp_name )) 이름2,   6  max( decode( mod(rownum,3), 0, emp_id )) 사번3,   7  max( decode( mod(rownum,3), 0, emp_name )) 이름3   8  from temp   9* group by ceil(rownum/3)

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

아무 의미없는 그룹함수를 다른 컬럼에 씌워주게 되면(min을 써도 동일) group by 로 ceil(rownum/3)을 묶어 줄 수 있게 된다.

하지만 끝난건 아니다. 마지막으로 첫번쨰 컬럼을 기준으로 정렬까지 해야 원하는 출력을 얻을 수 있게 된다.

최종

1  select ceil(rownum/3) NO,   2  max( decode( mod(rownum,3), 1, emp_id )) 사번1,   3  max( decode( mod(rownum,3), 1, emp_name )) 이름1,   4  max( decode( mod(rownum,3), 2, emp_id )) 사번2,   5  max( decode( mod(rownum,3), 2, emp_name )) 이름2,   6  max( decode( mod(rownum,3), 0, emp_id )) 사번3,   7  max( decode( mod(rownum,3), 0, emp_name )) 이름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 나한라

의미없는 그룹함수를 써서 원하는 컬럼 GROUP BY로 묶어주기,

group by절에 ceil(rownum/3) 같은 컬럼도 올 수 있다.

==================================================================================

==================================================================================

Emp 테이블의 hiredate 컬럼을 참고해서 아래의 결과처럼 출력해보세요.

       HAP          1980          1981     1982      1983    1987

---------- -------  -------  ----- --------

        14                 1            10            1          0        2

힌트 : count(칼럼이름) 은 null값을 계산하지 않습니다., decode에서 조건에 없는 부분은 null로 처리합니다.

  1  select count(hiredate),

  2  count( decode( to_char(hiredate,'yyyy'),1980,1 )) "1980",

  3  count( decode( to_char(hiredate,'yyyy'),1981,1 )) "1981",

  4  count( decode( to_char(hiredate,'yyyy'),1982,1 )) "1982",

  5  count( decode( to_char(hiredate,'yyyy'),1983,1 )) "1983",

  6  count( decode( to_char(hiredate,'yyyy'),1987,1 )) "1987"

  7* from emp

SQL> /

COUNT(HIREDATE)       1980       1981       1982       1983       1987

--------------- ---------- ---------- ---------- ---------- ----------

             14          1         10          1          0          2