DB/Oracle Basic

SQL 함수(그룹 함수)

soccerda 2012. 7. 6. 01:03
반응형
 
1. count : 행의 개수 출력
가. count(*) : null의 개수도 포함, 중복된 값도 포함(중복된 값 제외시 distinct 사용)
나. count(col_name) : null 제외, 중복된 값도 포함(중복된 값 제외시 distinct 사용)

2. max : null 을 제외한 모든행에서 최대값 출력

3. min : null 을 제외한 모든행에서 최소값 출력

4. sum : null 을 제외한 모든 행의 합게

5. avg : null 을 제외한 행의 평균값
null을 포함한 row 값도 평균에 반영하고자 할 때에는 nvl(col_name, 0) 함수를 이용한다.

6. stddev : null 을 제외한 모든 행의 표준편차

7. variance : null 을 제외한 모든 행의 분산

8. grouping : 해당 컬럼이 그룹에 사용되었는지 여부를 1 또는 0으로 반환

9. grouping set : 한번의 질이로 여러개의 그룹화 가능

10. group by 절의 사용규칙
가. 그룹핑 전에 where 절을 사용하여 그룹 대상 집합 선정가능
나. select 절에 나열된 컬럼이나, 표현식은 group by 절에 명시해야 함
다. group by 절에 나열된 컬럼은 select 절에 명시 하지 않아도 됨
라. group by 절에는 select 절에 사용한 alias 를 사용할 수 없음


11, rollup : group by 절의 그룹조건에 따라 전체 행을 그룹화하고 각 그룹에 대한 부분합을 구하는 연산잔

12. cube: rollup의 그룹 결과와 group by 절에 기술된 조건에 따라 그룹 조합을 만드는 연산자

참고 : group by 절에 기술된 칼럼 수가 n개 인경우
rollup : n+1
cube : 2^n




연습문제 :
1 .학과별로 평균 몸무게와 학생수를 출력하되 평균 몸무게의 내림차순으로 정렬하세요.

1
2
3
4
5
6
7
8
9
10
11
SQL> select deptno, avg(nvl(weight, 0)), count(*)
2 from student
3 group by deptno
4 order by 2 desc
5 ;
DEPTNO AVG(NVL(WEIGHT,0)) COUNT(*)
---------- ------------------ ----------
102 69.5 4
101 68 8
201 61.75 4



2. 동일 학과 내에서 같은 학년에 재학중인 학생 수가 3명 이상인 그룹의 학과번호, 학년, 학생 수, 최대 키, 최대 몸무게를 출력하세요.
1
2
3
4
5
6
7
8
9
SQL> select deptno, grade, count(*), max(height), max(weight)
2 from student
3 group by deptno, grade
4 having count(deptno) >= 3 and count(grade) >=3;
DEPTNO G COUNT(*) MAX(HEIGHT) MAX(WEIGHT)
---------- - ---------- ----------- -----------
201 1 3 184 70
101 2 3 171 72



3. 학과와 학년 별 키의 평균, 학과별 키의 평균, 전체 학생에 대한 키의 평균을 구하세요.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> select deptno, grade, avg(nvl(height,0))
2 from student
3 group by rollup(deptno, grade);
DEPTNO G AVG(NVL(HEIGHT,0))
---------- - ------------------
101 1 177
101 2 164.666667
101 3 170
101 4 175.5
101 171.125
102 1 160
102 2 164
102 3 171
102 4 177
102 168
201 1 179.333333
201 2 166
201 176
171.5625
14 rows selected.



4. 학과별 평균 몸무게와 학년 별 평균 몸무게를 구하세요
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> select deptno, grade, round(avg(nvl(weight,0)))
2 from student
3* group by grouping sets((deptno), (grade))
<p></p>
<p> DEPTNO G ROUND(AVG(NVL(WEIGHT,0)))
---------- - -------------------------
201 62
102 70
101 68
1 65
3 79
4 85
2 53</p>
<p>7 rows selected.
</p>



5. 학과별 교수 수가 2명 이하인 학과 번호, 교수 수를 출력 하세요.
1
2
3
4
5
6
7
8
9
10
SQL> select deptno, count(position)
2 from professor
3 where position='교수'
4 group by deptno
5 having count(deptno) <= 2;
DEPTNO COUNT(POSITION)
---------- ---------------
102 1
101 1



6. 교수 중에서 급여와 보직수당을 합친 금액이 가장 많은 경우와 가장 적은 경우 그리고 평균 금액을 출력하여라. 단, 보직수당이 없는 교수의 수당은 0으로 계산하고, 급여는 소수점 둘째 자리에서 반올림하세요.
1
2
3
4
5
6
7
8
SQL> select round(avg(nvl(sal,0)+nvl(comm,0)),1) sal_avg,
2 round(max(sal+nvl(comm,0)),1) sal_max,
3 round(min(sal+nvl(comm,0)),1) sal_min
4 from professor;
SAL_AVG SAL_MAX SAL_MIN
---------- ---------- ----------
372.1 520 210



7. 학과와 직급별 급여 평균, 학과별 급여 평균, 직급별 급여 평균 그리고 교수 전체에 대한 급여 평균을 출력하세요.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SQL> select deptno, position, avg(nvl(sal,0)) sal
2 from professor
3 group by cube(deptno, position);
DEPTNO POSITION SAL
---------- -------------------- ----------
362.5
교수 475
부교수 410
조교수 340
전임강사 225
101 372.5
101 교수 500
101 부교수 420
101 조교수 360
101 전임강사 210
102 345
102 교수 450
102 전임강사 240
201 320
201 조교수 320
202 400
202 부교수 400
17 rows selected.



8. 직급별로 평균 급여가 300보다 크면 ‘우수’, 작거나 같으면 ‘보통’을 출력하세요.
1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> select position, avg(nvl(sal, 0)) sal,
2 case when avg(nvl(sal,0)) > 300 then '우수'
3 else '보통'
4 end mark
5 from professor
6 group by position;
POSITION SAL MARK
-------------------- ---------- ----
교수 475 우수
부교수 410 우수
조교수 340 우수
전임강사 225 보통




9. Emp 테이블의 hiredate 컬럼을 참고해서 아래 결과처럼 출력해보세요.
1
2
3
4
5
6
7
8
9
10
SQL> select count(*) hap,
2 count(decode(to_char(hiredate,'rrrr'),'1980',1)) "1980",
3 count(decode(to_char(hiredate,'rrrr'),'1981',1)) "1981",
4 count(decode(to_char(hiredate,'rrrr'),'1982',1)) "1982",
5 count(decode(to_char(hiredate,'rrrr'),'1987',1)) "1987"
6 from emp;
HAP 1980 1981 1982 1987
---------- ---------- ---------- ---------- ----------
14 1 10 1 2



10. Emp table을 사용하여 부서별로 직급별로 급여 합계를 구하고 합계를 구하세요.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select deptno,
2 sum(decode(job,'CLERK',sal,0)) "CLERK",
3 sum(decode(job,'MANAGER',sal,0)) "MANAGER",
4 sum(decode(job,'PRESIDENT',sal,0)) "PRESIDENT",
5 sum(decode(job,'ANALYST',sal,0)) "ANALYST",
6 sum(decode(job,'SALESMAN',sal,0)) "SALESMAN",
7 sum(sal) "HAP"
8 from emp
9 where job is not null
10 group by rollup(deptno)
11 order by deptno;
DEPTNO CLERK MANAGER PRESIDENT ANALYST SALESMAN HAP
---------- ---------- ---------- ---------- ---------- ---------- ----------
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


11. temp 테이블을 사용하여 아래와 같이 출력하시오
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> 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 나한라
7 rows selected.
반응형