1.직군,월별 입사건수
결과 화면
JOB 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
--------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
ANALYST 0 0 0 0 0 0 1 0 0 0 0 1
CLERK 0 0 0 0 0 0 1 0 0 0 0 2
MANAGER 0 0 0 1 1 1 0 0 0 0 0 0
PRESIDENT 0 0 0 0 0 0 0 0 0 0 1 0
SALESMAN 0 2 0 0 0 0 0 0 2 0 0 0
답
1.1) 데이터복제 방식.
SQL>
select
job,
count("1월") "1월",
count("2월") "2월",
count("3월") "3월",
count("4월") "4월",
count("5월") "5월",
count("6월") "6월",
count("7월") "7월",
count("8월") "8월",
count("9월") "9월",
count("10월") "10월",
count("11월") "11월",
count("12월") "12월"
from
(
select job,
(case when TO_CHAR(hiredate, 'FMMM') = '1' then 1 end ) "1월",
(case when TO_CHAR(hiredate, 'FMMM') = '2' then 1 end ) "2월",
(case when TO_CHAR(hiredate, 'FMMM') = '3' then 1 end ) "3월",
(case when TO_CHAR(hiredate, 'FMMM') = '4' then 1 end ) "4월",
(case when TO_CHAR(hiredate, 'FMMM') = '5' then 1 end ) "5월",
(case when TO_CHAR(hiredate, 'FMMM') = '6' then 1 end ) "6월",
(case when TO_CHAR(hiredate, 'FMMM') = '7' then 1 end ) "7월",
(case when TO_CHAR(hiredate, 'FMMM') = '8' then 1 end ) "8월",
(case when TO_CHAR(hiredate, 'FMMM') = '9' then 1 end ) "9월",
(case when TO_CHAR(hiredate, 'FMMM') = '10' then 1 end) "10월",
(case when TO_CHAR(hiredate, 'FMMM') = '11' then 1 end) "11월",
(case when TO_CHAR(hiredate, 'FMMM') = '12' then 1 end) "12월"
from emp
)
group by job
order by 1
JOB 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
--------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
ANALYST 0 0 0 0 0 0 1 0 0 0 0 1
CLERK 0 0 0 0 0 0 1 0 0 0 0 2
MANAGER 0 0 0 1 1 1 0 0 0 0 0 0
PRESIDENT 0 0 0 0 0 0 0 0 0 0 1 0
SALESMAN 0 2 0 0 0 0 0 0 2 0 0 0
5 rows selected.
1.2) Pivot
SQL> SELECT *
FROM (
SELECT job , TO_CHAR(hiredate, 'FMMM') || '월' hire_month
FROM emp
)
PIVOT (
COUNT(*)
FOR hire_month IN ('1월', '2월', '3월', '4월', '5월', '6월',
'7월', '8월', '9월', '10월', '11월', '12월')
)
order by 1;
JOB '1월' '2월' '3월' '4월' '5월' '6월' '7월' '8월' '9월' '10월' '11월' '12월'
--------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
ANALYST 0 0 0 0 0 0 1 0 0 0 0 1
CLERK 0 0 0 0 0 0 1 0 0 0 0 2
MANAGER 0 0 0 1 1 1 0 0 0 0 0 0
PRESIDENT 0 0 0 0 0 0 0 0 0 0 1 0
SALESMAN 0 2 0 0 0 0 0 0 2 0 0 0
5 rows selected.
1.3)DECODE사용.
SQL> select job,
sum(DECODE(to_char(hiredate,'FMMM'),'1',1,0)) "1월",
sum(DECODE(to_char(hiredate,'FMMM'),'2',1,0)) "2월",
sum(DECODE(to_char(hiredate,'FMMM'),'3',1,0)) "3월",
sum(DECODE(to_char(hiredate,'FMMM'),'4',1,0)) "4월",
sum(DECODE(to_char(hiredate,'FMMM'),'5',1,0)) "5월",
sum(DECODE(to_char(hiredate,'FMMM'),'6',1,0)) "6월",
sum(DECODE(to_char(hiredate,'FMMM'),'7',1,0)) "7월",
sum(DECODE(to_char(hiredate,'FMMM'),'8',1,0)) "8월",
sum(DECODE(to_char(hiredate,'FMMM'),'9',1,0)) "9월",
sum(DECODE(to_char(hiredate,'FMMM'),'10',1,0)) "10월",
sum(DECODE(to_char(hiredate,'FMMM'),'11',1,0)) "11월",
sum(DECODE(to_char(hiredate,'FMMM'),'12',1,0)) "12월"
from emp
group by job
order by 1;
JOB 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
--------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
ANALYST 0 0 0 0 0 0 1 0 0 0 0 1
CLERK 0 0 0 0 0 0 1 0 0 0 0 2
MANAGER 0 0 0 1 1 1 0 0 0 0 0 0
PRESIDENT 0 0 0 0 0 0 0 0 0 0 1 0
SALESMAN 0 2 0 0 0 0 0 0 2 0 0 0
5 rows selected.
2.부서별,총계별,급여합과 급여평균 구하기.
결과화면
DEPTNO EMPNO 급여합 급여평균
---------------------------------------- ---------------------------------------- --------- ---------
10 7782 2450 2450
10 7839 5000 5000
10 부서계 7450 3725
20 7369 800 800
20 7566 2975 2975
20 7788 3000 3000
20 7876 1100 1100
20 7902 3000 3000
20 부서계 10875 2175
30 7499 1600 1600
30 7521 1250 1250
30 7654 1250 1250
30 7698 2850 2850
30 7844 1500 1500
30 7900 950 950
30 부서계 9400 1567
총계 부서계 27725 2133
답
2.1)데이터 복제
SQL> select
(case when r.rn=1 then to_char(e.deptno)
when r.rn=2 then to_char(e.deptno)
when r.rn=3 then '총계' end ) deptno,
(case when r.rn=1 then to_char(e.empno)
when r.rn=2 then '부서계'
when r.rn=3 then '부서계' end ) empno,
sum(e.sal) "급여합",
round(avg(e.sal)) "급여평균"
from emp e,(select rownum as rn from dual connect by level <= 3) r
group by
(case when r.rn=1 then to_char(e.deptno)
when r.rn=2 then to_char(e.deptno)
when r.rn=3 then '총계' end ),
(case when r.rn=1 then to_char(e.empno)
when r.rn=2 then '부서계'
when r.rn=3 then '부서계' end )
order by 1;
DEPTNO EMPNO 급여합 급여평균
---------------------------------------- ---------------------------------------- --------- ---------
10 7782 2450 2450
10 7839 5000 5000
10 부서계 7450 3725
20 7369 800 800
20 7566 2975 2975
20 7788 3000 3000
20 7876 1100 1100
20 7902 3000 3000
20 부서계 10875 2175
30 7499 1600 1600
30 7521 1250 1250
30 7654 1250 1250
30 7698 2850 2850
30 7844 1500 1500
30 7900 950 950
30 부서계 9400 1567
총계 부서계 27725 2133
17 rows selected.
2.2) grouping 함수
SQL> select
(case when grouping(deptno) = 0 then to_char(deptno)
when grouping(deptno) = 1 then '총계' end) deptno,
(case when grouping(empno) = 0 then to_char(empno)
when grouping(empno) =1 then '부서계' end ) empno,
sum(sal) "급여합",round(avg(sal)) "급여평균"
from emp
group by rollup(deptno,empno)
order by 1;
DEPTNO EMPNO 급여합 급여평균
---------------------------------------- ---------------------------------------- --------- ---------
10 7839 5000 5000
10 7782 2450 2450
10 부서계 7450 3725
20 7876 1100 1100
20 7902 3000 3000
20 7788 3000 3000
20 7566 2975 2975
20 7369 800 800
20 부서계 10875 2175
30 7900 950 950
30 7499 1600 1600
30 7521 1250 1250
30 7654 1250 1250
30 7698 2850 2850
30 7844 1500 1500
30 부서계 9400 1567
총계 부서계 27725 2133
17 rows selected.
3.JOB='CLERK' 인 사원들의 항목별(sal,deptno,comm) 값을 보이는 쿼리작성 (작성시 null값은 로우값으로 표시되지 않게 하기)
결과화면
EMPNO ENAME 항목명 값
----- ---------- ------ ---------
7369 SMITH SAL 800
7369 SMITH DEPTNO 20
7876 ADAMS SAL 1100
7876 ADAMS DEPTNO 20
7900 JAMES SAL 950
7900 JAMES COMM 1000
7900 JAMES DEPTNO 303.1)데이터 복제
SQL> select e.empno,e.ename,
(case r.rn when 1 then 'sal'
when 2 then 'deptno'
when 3 then 'comm' end ) "항목명",
(case r.rn when 1 then e.sal
when 2 then e.deptno
when 3 then e.comm end) "값"
from emp e,(select rownum as rn from dual connect by level <=3) r
where job='CLERK'
and r.rn in (
(case when e.sal is not null then 1 else null end),
(case when e.deptno is not null then 2 else null end),
(case when e.comm is not null then 3 else null end)
)
order by 1;
EMPNO ENAME 항목명 값
----- ---------- ------ ---------
7369 SMITH sal 800
7369 SMITH deptno 20
7876 ADAMS deptno 20
7876 ADAMS sal 1100
7900 JAMES sal 950
7900 JAMES comm 1000
7900 JAMES deptno 30
7 rows selected.
3.2)UNPIVOT
SQL> select empno,ename,항목명,값
from emp
unpivot (값 for 항목명 in (sal,comm,deptno))
where JOB='CLERK';
EMPNO ENAME 항목명 값
----- ---------- ------ ---------
7369 SMITH SAL 800
7369 SMITH DEPTNO 20
7876 ADAMS SAL 1100
7876 ADAMS DEPTNO 20
7900 JAMES SAL 950
7900 JAMES COMM 1000
7900 JAMES DEPTNO 30
7 rows selected.
4.
테이블.
with 고객 as(
select 1 as "고객번호",'김두환' as "고객명",'VIP' as "고객구분코드" from dual
union all
select 2,'홍길동','VIP' from dual
) ,
고객연락처 as (
select 1 as "고객번호",'HOM' as "연락처구분코드",'02-1234-4568' as "연락처번호" from dual
union all
select 1 ,'OFC' ,'032-234-4568' from dual
union all
select 1 ,'MBL' ,'010-234-4568' from dual
union all
select 2 ,'HOM' ,'031-9876-6543' from dual
union all
select 2 ,'MBL' ,'010-9876-6543' from dual
)
데이터
select * From 고객;
고객번호 고객명 고객구분코드
-------- --------- ------------
1 김두환 VIP
2 홍길동 VIP
2 rows selected.
select * From 고객연락처;
고객번호 연락처구분코드 연락처번호
-------- -------------- -------------
1 HOM 02-1234-4568
1 OFC 032-234-4568
1 MBL 010-234-4568
2 HOM 031-9876-6543
2 MBL 010-9876-6543
5 rows selected.
결과.
고객번호 고객명 집 사무실 휴대폰
-------- --------- ------------- ------------- -------------
1 김두환 02-1234-4568 032-234-4568 010-234-4568
2 홍길동 031-9876-6543 010-9876-6543답
4.1) case when & group by
select
c.고객번호,
min(c.고객명) "고객명",
min((case when d.연락처구분코드='HOM' then d.연락처번호 else null end)) "집",
min((case when d.연락처구분코드='OFC' then d.연락처번호 else null end)) "사무실",
min((case when d.연락처구분코드='MBL' then d.연락처번호 else null end)) "휴대폰"
from
고객 c,고객연락처 d
where c.고객번호 = d.고객번호
and c.고객구분코드 = 'VIP'
group by c.고객번호
4.2) PIVOT
select
c.고객번호,
c.고객명,
d.집,
d.사무실,
d.모바일
from 고객 c,고객연락처
pivot
(min(연락처번호) for 연락처구분코드 in ('HOM' as "집",'MBL' as "모바일",'OFC' as "사무실")
) d
where c.고객번호 = d.고객번호
and c.고객구분코드='VIP';
고객번호 고객명 집 사무실 모바일
-------- --------- ------------- ------------- -------------
1 김두환 02-1234-4568 032-234-4568 010-234-4568
2 홍길동 031-9876-6543 010-9876-6543
2 rows selected.
5.고객구분코드가 VIP인 고객을 고객연락처와 조인해서 연락처 정보를 Piviot하는 SQL 작성하기.
결과 테이블.
고객번호 고객명 연락처
-------- --------- -------------------------------------------------------
1 김두환 (HOM)02-1234-4568,(MBL)010-234-4568,(OFC)032-234-4568
2 홍길동 (HOM)031-9876-6543,(MBL)010-9876-6543
답,
5.1)listagg 함수 사용. (한 로우에 값 다 넣기.)
select
c.고객번호,
min(c.고객명) 고객명,
listagg('('||연락처구분코드||')'||연락처번호,',') within group(order by c.고객번호) 연락처
from 고객 c,고객연락처 d
where c.고객번호 = d.고객번호
and c.고객구분코드='VIP'
group by c.고객번호;
고객번호 고객명 연락처
-------- --------- -------------------------------------------------------
1 김두환 (HOM)02-1234-4568,(MBL)010-234-4568,(OFC)032-234-4568
2 홍길동 (HOM)031-9876-6543,(MBL)010-9876-6543
6.누적 매출금액 조회하기.
SQL> with 월별지점매출 as (
select 10 as 지점코드,1 as 판매월 , 521 as 판매금액 from dual
union all
select 10 ,2 , 684 from dual
union all
select 10 ,3 , 590 from dual
union all
select 20 ,1 , 537 from dual
union all
select 20 ,1 , 537 from dual
union all
select 20 ,2 , 650 from dual
union all
select 20 ,3 , 500 from dual
union all
select 20 ,4 , 919 from dual
union all
select 20 ,5 , 658 from dual
union all
select 30 ,1 , 631 from dual
union all
select 30 ,2 , 736 from dual
union all
select 30 ,3 , 513 from dual
union all
select 30 ,4 , 970 from dual
union all
select 30 ,5 , 939 from dual
union all
select 30 ,6 , 666 from dual
)
select * from 월별지점매출;
지점코드 판매월 판매금액
-------- ------ --------
10 1 521
10 2 684
10 3 590
20 1 537
20 1 537
20 2 650
20 3 500
20 4 919
20 5 658
30 1 631
30 2 736
30 3 513
30 4 970
30 5 939
30 6 666
15 rows selected.
결과화면
지점코드 판매월 판매금액 누적판매
-------- ------ -------- ---------
10 1 521 521
10 2 684 1205
10 3 590 1795
20 1 537 537
20 1 537 1074
20 2 650 1724
20 3 500 2224
20 4 919 3143
20 5 658 3801
30 1 631 631
30 2 736 1367
30 3 513 1880
30 4 970 2850
30 5 939 3789
30 6 666 4455
15 rows selected.
답.
6.1) 분석을 활용하는 방법.
select 지점코드,판매월,판매금액,sum(판매금액) over (partition by 지점코드 order by 판매월 rows between unbounded preceding and current row) 누적판매
from 월별지점매출
지점코드 판매월 판매금액 누적판매
-------- ------ -------- ---------
10 1 521 521
10 2 684 1205
10 3 590 1795
20 1 537 537
20 1 537 1074
20 2 650 1724
20 3 500 2224
20 4 919 3143
20 5 658 3801
30 1 631 631
30 2 736 1367
30 3 513 1880
30 4 970 2850
30 5 939 3789
30 6 666 4455
15 rows selected.
6.2) 조인을 활용하는 방법. (같은 테이블 두번 조인)
select b.지점코드,b.판매월,min(b.판매금액),sum(a.판매금액)
from 월별지점매출 a, 월별지점매출 b
where 1=1
and a.지점코드 = b.지점코드
and a.판매월 <= b.판매월
group by b.지점코드,b.판매월
order by 1,2;
지점코드 판매월 MIN(B.판매금액) SUM(A.판매금액)
-------- ------ --------------- ---------------
10 1 521 521
10 2 684 1205
10 3 590 1795
20 1 537 2148
20 2 650 1724
20 3 500 2224
20 4 919 3143
20 5 658 3801
30 1 631 631
30 2 736 1367
30 3 513 1880
30 4 970 2850
30 5 939 3789
30 6 666 4455
14 rows selected.
풀이.
전부다 하면 많으니까 지점코드가 10인 데이터만 가지고 한스텝씩 보도록함.
1) 조인하여 a.지점코드 = b.지점코드
select *
from 월별지점매출 a, 월별지점매출 b
where 1=1
and a.지점코드 = 10
and a.지점코드 = b.지점코드;
지점코드 판매월 판매금액 지점코드 판매월 판매금액
-------- ------ -------- -------- ------ --------
10 1 521 10 1 521
10 2 684 10 1 521
10 3 590 10 1 521
10 1 521 10 2 684
10 2 684 10 2 684
10 3 590 10 2 684
10 1 521 10 3 590
10 2 684 10 3 590
10 3 590 10 3 590
9 rows selected.
2)a.판매월 <= b.판매월 조건 추가
select *
from 월별지점매출 a, 월별지점매출 b
where 1=1
and a.지점코드 = 10
and a.지점코드 = b.지점코드
and a.판매월 <= b.판매월;
지점코드 판매월 판매금액 지점코드 판매월 판매금액
-------- ------ -------- -------- ------ --------
10 1 521 10 1 521
10 1 521 10 2 684
10 2 684 10 2 684
10 1 521 10 3 590
10 2 684 10 3 590
10 3 590 10 3 590
6 rows selected.
보면 b.판매월이 더 큰 데이터를 보게 되어있음.
b.판매월 =2 인 기준의 데이터를 보면
A B
10 1 521 / 10 2 684
10 2 684 / 10 2 684
이렇게 되어있음.
group by를 위해서 컬럼명을 쓴다면 B의 컬럼을 기준으로 A를 집계하면 합계를 얻을 수 있게 됨.
3) 4번째 컬럼에 누적합계가 구해지게 됨.
select b.지점코드,b.판매월,min(b.판매금액),SUM(a.판매금액)
from 월별지점매출 a, 월별지점매출 b
where 1=1
and a.지점코드 = 10
and a.지점코드 = b.지점코드
and a.판매월 <= b.판매월
group by b.지점코드,b.판매월
order by b.지점코드,b.판매월;
지점코드 판매월 MIN(B.판매금액) SUM(A.판매금액)
-------- ------ --------------- ---------------
10 1 521 521
10 2 684 1205
10 3 590 1795
3 rows selected.
4) a.지점코드 = 10을 제외하여 모든 값에서 구하게 즉, 정답을 구하게 함.
select b.지점코드,b.판매월,min(b.판매금액),SUM(a.판매금액)
from 월별지점매출 a, 월별지점매출 b
where 1=1
and a.지점코드 = b.지점코드
and a.판매월 <= b.판매월
group by b.지점코드,b.판매월
order by b.지점코드,b.판매월;
지점코드 판매월 MIN(B.판매금액) SUM(A.판매금액)
-------- ------ --------------- ---------------
10 1 521 521
10 2 684 1205
10 3 590 1795
20 1 537 2148
20 2 650 1724
20 3 500 2224
20 4 919 3143
20 5 658 3801
30 1 631 631
30 2 736 1367
30 3 513 1880
30 4 970 2850
30 5 939 3789
30 6 666 4455
14 rows selected.
'자격증 > SQLP' 카테고리의 다른 글
| 계층형 쿼리 (0) | 2021.08.26 |
|---|---|
| 조건절 pushdown 과 서브쿼리 Unnesting (0) | 2021.08.25 |
| 서술형 문제 테이블을 한번만 읽고 SQL 작성 (0) | 2021.08.18 |
| 서술형문제 6 야간배치 SQL 튜닝 (0) | 2021.08.16 |
| 서술형문제5. 페이징처리 SQL 작성법 (0) | 2021.08.14 |
댓글