<DataBase_231101수> 다중행 함수

select
from // 1
where // 2
group by // 3
having // 4
order by
 
-------------------------------------------------
 
rownum (중요)
rowid
rollup
cube
grouping sets
group by
col dept10 format '9,990'
col dept10 format a20
 
-------------------------------------------------
 
<속성 지울 때 clear 명령>
clear col
clear break
clear compute
 
-------------------------------------------------

// 연습문제

1. 직책별 급여합계와 평균급여를 추출
select sajob, sum(sapay), avg(sapay) from sawon
group by sajob; 

2. 직책별 인원수와 급여합계를 추출
select sajob, count(*), sum(sapay) from sawon
group by sajob;

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

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

5. 입사년도별로 평균 급여를 추출
select to_char(sahire, 'yyyy') year, avg(sapay)
from sawon
group by to_char(sahire, 'yyyy');
 
6. 5명씩 급여합계를 추출
select sum(sapay), ceil(rownum/5)
from sawon
group by ceil(rownum/5);
 
7. 성별&직책별, 직책별&부서별로 급여합계 추출
select sasex, sajob, deptno, sum(sapay) from sawon
group by grouping sets((sasex, sajob), (sajob, deptno));
 
8. 직책별 성별 급여합계를 추출
select sajob, sasex, sum(sapay) from sawon
group by rollup(sajob, sasex);

 
또는
 
select sajob, sasex, sum(sapay) from sawon
group by cube(sajob, sasex);

 
9. 직책별 급여합계를 추출
select sajob, sum(sapay) from sawon
group by grouping sets(sajob);

 
10. 직책별 인원수를 추출
SELECT sajob, COUNT(*) AS person_count
FROM sawon
GROUP BY sajob;

11. 직책별 부서별 급여합계를 추출
select sajob, deptno, sum(sapay) from sawon
group by grouping sets((sajob, deptno));

 
------------------------------------------
 
<선생님이 정리해주신>
<다중행 함수>
 
*** 다중행 함수
--계산함수, 그룹함수
--sum, avg, count, max, min

--형식 : group by 컬럼명,... having

SQL> select sum(sapay), avg(nvl(sapay,0))*21, count(*), max(sapay), min(sapay) from sawon;
     ------------------      ---------
SUM(SAPAY) AVG(NVL(SAPAY,0))*21   COUNT(*) MAX(SAPAY) MIN(SAPAY)
---------- -------------------- ---------- ---------- ----------
     48318                48318         21       5000        400

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

select deptno, sum(sapay) from sawon
--where deptno=10 or deptno=30
group by deptno
having  sum(sapay)>=10000;

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

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

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

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

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

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

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

* rollup & cube
--group by와 함께 사용

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

--직책별 성별 급여합계를 추출
select sajob, sasex, sum(sapay) from sawon
group by sajob, sasex;
------------------------------------------
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;
------------------------------------------

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

SAJOB      SASE SUM(SAPAY)
---------- ---- ----------
과장       남자      12003
과장       여자       3900
과장                 15903 --***
대리       남자       1800
대리       여자       4206
대리                  6006 --***
부장       남자       5803
부장       여자       3000
부장                  8803
사원       남자       4906
사원       여자       7700

SAJOB      SASE SUM(SAPAY)
---------- ---- ----------
사원                 12606
회장       남자       5000
회장                  5000
                     48318 --***

15 개의 행이 선택되었습니다.

SQL> select sajob, sasex, sum(sapay) from sawon
  2  group by cube(sajob, sasex); --***

SAJOB      SASE SUM(SAPAY)
---------- ---- ----------
                     48318
           남자      29512 --***
           여자      18806
과장                 15903
과장       남자      12003
과장       여자       3900
대리                  6006
대리       남자       1800
대리       여자       4206
부장                  8803
부장       남자       5803

SAJOB      SASE SUM(SAPAY)
---------- ---- ----------
부장       여자       3000
사원                 12606
사원       남자       4906
사원       여자       7700
회장                  5000
회장       남자       5000

17 개의 행이 선택되었습니다.

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

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

SAJOB      SASE SUM(SAPAY)          J          S
---------- ---- ---------- ---------- ----------
                     48318          1          1 --***
           남자      29512          1          0
           여자      18806          1          0
과장                 15903          0          1 --***
과장       남자      12003          0          0
과장       여자       3900          0          0
대리                  6006          0          1
대리       남자       1800          0          0
대리       여자       4206          0          0
부장                  8803          0          1
부장       남자       5803          0          0

SAJOB      SASE SUM(SAPAY)          J          S
---------- ---- ---------- ---------- ----------
부장       여자       3000          0          0
사원                 12606          0          1
사원       남자       4906          0          0
사원       여자       7700          0          0
회장                  5000          0          1
회장       남자       5000          0          0

17 개의 행이 선택되었습니다.

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

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

SASE SAJOB          DEPTNO SUM(SAPAY)
---- ---------- ---------- ----------
     부장               20       3000
     대리               30       4206
     부장               10       3000
     사원               10       3100
     사원               20       6800
     대리               10       1800
     회장               10       5000
     사원               30       2706
     부장               30       2803
     과장               10       4500
     과장               30       4003

SASE SAJOB          DEPTNO SUM(SAPAY)
---- ---------- ---------- ----------
     과장               20       7400 // 직책별 부서 끝
남자 회장                        5000
남자 사원                        4906
여자 부장                        3000
여자 사원                        7700
남자 대리                        1800
남자 부장                        5803
남자 과장                       12003
여자 과장                        3900
여자 대리                        4206

21 개의 행이 선택되었습니다.

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

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

      과장       대리       사원         이사
---------- ---------- ---------- -----------
     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 속성과 함께 사용하며 컬럼 집계하는데 사용한 속성