[Oracle] SQL - [고급함수] [다중행함수] 계산 함수, group 함수, Grouping 함수, grouping sets 명령, 컬럼 그룹화,

*** 다중행 함수

--계산함수, 그룹함수
--sum, avg, count, max, min

--형식 : group by 컬럼명,... having             // group by 와 having은 항상 같이 사용.

SQL> select sum(sapay), avg(nvl(sapay,0))*21, count(*), max(sapay), min(sapay) from sawon;

 

(연습1)
--부서별로 급여합계를 추출
--단, 10, 30번 부서만 추출
--단, 급여합계가 10000이상인 부서를 추출 

select deptno, sum(sapay) from sawon
--where deptno=10 or deptno=30                     // where절 대신에 group by ... having 사용
group by deptno
having sum(sapay)>=10000;

 

********************
select 
from // 1.
where // 2
group by // 3.
having // 4.
order by
********************

 

(연습2)
--직책별 급여합계와 평균급여를 추출
SQL> select sajob, sum(sapay) tot, avg(sapay) average from sawon
           group by sajob;

 

(연습3)
--직책별 인원수와 급여합계를 추출
SQL>  select sajob, count(*) 인원수, sum(sapay) 급여합계 from sawon
           group by sajob;

 

(연습4)
--성별, 직책별로 평균 급여와 인원수를 출력하되, 사원과 대리 직책만 추출하되 인원수가 많은 순서대로(desc) 정렬
SQL>   select sasex, sajob, avg(sapay), count(*) 인원수 from sawon
            where sajob='사원' or sajob='대리'
            group by sasex, sajob
            order by 4 desc;                             // order by 인원수 desc;

 

(연습5)
--성별, 직책별로 평균 급여와 인원수를 출력하되, 사원을 제외하고, 집계 인원수가 2명 이하인 것만 추출
SQL>  select sasex, sajob, avg(sapay), count(*) cnt from sawon
           where sajob != '사원'
           group by sasex, sajob
           having count(*) <= 2;

 

(연습6)
--입사년도별로 평균급여를 추출
select to_char(sahire, 'yyyy') year, avg(sapay) from sawon
group by to_char(sahire, 'yyyy');

 

(연습7)
--5명씩 급여합계를 추출
select ceil(rownum/5), sum(sapay) from sawon                   //rownum
group by ceil(rownum/5);

* rollup & cube

--group by와 함께 사용

--rollup : 1차 그룹에 대한 집계

ROLLUP함수는 소그룹간의 합계를 계산하는 함수입니다. ROLLUP을 사용하면 GROUP BY로 묶은 각각의 소그룹 합계와 전체 합계를 모두 구할 수 있습니다. 

--cube   : rollup + 2차 그룹에 대한 집계

CUBE함수는 항목들 간의 다차원적인 소계를 계산합니다. ROLLUP과 달리 GROUP BY절에 명시한 모든 컬럼에 대해 소그룹 합계를 계산해줍니다.

 

(연습8)
--직책별 성별 급여합계를 추출
SQL> select sajob, sasex, sum(sapay) from sawon
          group by sajob, sasex;

 

(연습8-1)

select sajob, sasex, sum(sapay) from sawon group by sajob, sasex
union all                                                         // 테이블끼리 붙이는 명령어
select sajob, '' , sum(sapay) from sawon group by sajob
union all                                                         // 테이블끼리 붙이는 명령어
select '', sasex, sum(sapay) from sawon group by sasex
union all                                                         // 테이블끼리 붙이는 명령어
select '', '' ,sum(sapay) from sawon;
------------------------------------------

 

(연습8-2)

select sajob, sasex, sum(sapay) from sawon
group by rollup(sajob, sasex);                       --***

 

(연습8-3)
SQL> select sajob, sasex, sum(sapay) from sawon
           group by cube(sajob, sasex);                      --***

* grouping 함수

--연산작업 없다!!
--rollup or cube와 함께 사용하며, rollup or cube의 의한 결과 행인지(1), 일반 집계에 의한 결과 행인지(0)를 반환하는 함수

SQL> select sajob, sasex, sum(sapay), grouping(sajob) J, grouping(sasex) S from sawon
          group by cube(sajob, sasex);

* grouping sets 명령

-- group by와 함께 사용하며, 여러 그룹화 작업을 한 번의 명령으로 사용

-- 성별 직책별, 직책별 부서별로 급여합계를 추출
SQL> select sasex, sajob, deptno, sum(sapay) from sawon
           group by grouping sets( (sasex, sajob), (sajob, deptno) );

* 컬럼 그룹화 (조건명령 + 계산함수 이용)

--직책별 급여합계를 다음의 형태로 추출

      과장       대리       사원         이사
---------- ---------- ---------- -----------
     15903       6006      12606          0

select sum(decode(sajob, '과장', sapay, 0)) "과장",
sum(decode(sajob, '대리', sapay, 0)) "대리",
sum(decode(sajob, '사원', sapay, 0)) "사원",
sum(decode(sajob, '이사', sapay, 0)) "이사",
from sawon;

--직책별 인원수를 다음의 형태로 추출

      과장       대리       사원       이사
---------- ---------- ---------- ----------
         5          3            8            0

select  sum(decode(sajob, '과장', 1, 0)) "과장",
sum(decode(sajob, '대리', 1, 0)) "대리",
sum(decode(sajob, '사원', 1, 0)) "사원",
sum(decode(sajob, '이사', 1, 0)) "이사",
from sawon;

select  count(decode(sajob,'과장',0)) "과장",
count(decode(sajob,'사원',1)) "사원",
count(decode(sajob,'대리','aaa')) "대리",
count(decode(sajob,'이사',0)) "이사" 
from sawon;

--직책별 부서별 급여합계를 추출

직책           dept10     dept20     dept30     dept40   직책합계
---------- ---------- ---------- ---------- ---------- ----------
과장             4500       7400       4003          0      15903
대리             1800          0       4206          0       6006
부장             3000       3000       2803          0       8803
사원             3100       6800       2706          0      12606
회장             5000          0          0          0       5000
                17400      17200      13718          0      48318 -- rollup

<명령>
select  sajob "직책",
sum(case deptno when 10 then sapay else 0 end) "dept10",
sum(case deptno when 20 then sapay else 0 end) "dept20",
sum(case deptno when 30 then sapay else 0 end) "dept30",
sum(case deptno when 40 then sapay else 0 end) "dept40",
sum(sapay) "직책합계"
from sawon
group by sajob;
-- group by rollup(sajob)

<속성>
break on report
compute   sum label 부서합계 of dept10 dept20 dept30 dept40 직책합계 on 직책 report
--compute 함수 label 문자열 of 컬럼명 컬럼명 ......on 그룹화컬럼명 report

[참고]
col 컬럼명 format [aXX(byte) || '형식']                     // col dept10 format '9,990'
break on 컬럼명 // 컬럼 그룹화 속성
compute  // break on report 속성과 함께 사용하며 컬럼 집계하는데 사용한 속성