[SQL] 분석함수 UNPIVOT

문제1. 아래의 테이블을 생성하시오

-- unpivot 문 준비 데이터

drop table order2;

create  table  order2
(  ename    varchar2(10),
   bicycle     number(10),
   camera    number(10),
   notebook   number(10) );

insert  into  order2  values('SMITH', 2, 3, 1);
insert  into  order2  values('ALLEN', 1, 2, 3);
insert  into  order2  values('KING', 3, 2, 2 );

commit;

문제2. order2 테이블을 조회해서 SMITH 는 자전거를 몇대 가지고 있는지 조회하시오

select bicycle
 from order2
 where ename='SMITH';

문제3. SMITH 가 3대를 가지고 있는 물건은 무엇인가?

지금 상태로는 SQL 을 작성하기 어렵습니다.

문제4. unpivot 문을 이용해서 ORDER2 를 unpivot 시키시오

select *
 from order2
 unpivot (cnt for item in (bicycle, camera, notebook));

문제5. 위에서 출력된 결과를 view 로 생성하시오

💡 view 는 쿼리문의 결과를 보여주는 데이터베이스 객체
테이블처럼 데이터를 보유하고 있지는 않음

create or replace view order_view
as
select *
 from order2
 unpivot ( cnt for item in ( bicycle, camera, notebook));
 
select * 
 from order_view
 where ename='SMITH' and cnt = 3;

문제6. 서울시 경찰청 빅데이터에서 범죄 시간에 대한 데이터를 내려받아 테이블로 생성하시오

drop table crime_time;

 create table  crime_time
 ( crime_type   varchar2(20),
     f0t3  number(10),
     f3t6  number(10),
     f6t9  number(10),
     f9t12  number(10),
     f12t15  number(10), 
     f15t18   number(10),
     f18t21   number(10),
     f21t24     number(10)  );                                                                                                                                            


 insert into crime_time values('살인',36,34,26,51,46,46,56,69);
 insert into crime_time values('살인미수',59,45,24,48,44,61,105,123);
 insert into crime_time values('강도',381,594,136,135,169,155,228,349);
 insert into crime_time values('강간강제추행',2278,2675,1654,1073,1142,1423,1799,3248);
 insert into crime_time values('방화',269,272,107,145,147,211,252,339);
 insert into crime_time values('절도',23761,23392,20292,31732,32648,36720,45653,39745);
 insert into crime_time values('상해',7751,6107,3401,4412,4254,5508,7572,13043);
 insert into crime_time values('폭행',18911,14228,6918,7942,8129,10848,16806,31950);
 insert into crime_time values('체포감금',56,69,55,98,89,84,123,114);
 insert into crime_time values('협박',222,145,199,506,427,473,544,547);
 insert into crime_time values('약취유인',9,5,14,29,22,34,22,21);
 insert into crime_time values('폭력행위등',6915,5910,2470,2558,2787,4079,5376,9879);
 insert into crime_time values('공갈',207,165,269,612,758,1173,935,591);
 insert into crime_time values('손괴',6316,4938,3621,4486,4224,6059,10694,13566);
 insert into crime_time values('직무유기',4,6,5,110,27,18,17,10);
 insert into crime_time values('직권남용',11,4,6,43,29,17,6,11);
 insert into crime_time values('증수뢰',2,1,1,84,37,22,8,2);
 insert into crime_time values('통화',51,36,142,2018,777,590,209,133);
 insert into crime_time values('문서인장',193,127,65,2364,994,538,359,432);
 insert into crime_time values('유가증권인지',10,5,2,86,41,24,19,13);
 insert into crime_time values('사기',5149,4034,2315,39296,22459,13987,8323,8483);
 insert into crime_time values('횡령',837,686,884,4891,2292,1813,1653,1656);
 insert into crime_time values('배임',11,2,3,1007,268,55,24,6);
 insert into crime_time values('성풍속범죄',374,286,464,961,983,1608,1296,1353);
 insert into crime_time values('도박범죄',360,189,104,450,748,1670,1931,2024);
 insert into crime_time values('특별경제범죄',2573,1858,1549,10887,6360,5225,4828,5988);
 insert into crime_time values('마약범죄',110,87,53,261,292,322,477,439);
 insert into crime_time values('보건범죄',639,369,112,2301,1973,1178,900,1882);
 insert into crime_time values('환경범죄',23,29,120,408,813,351,66,64);
 insert into crime_time values('교통범죄',42507,31839,41865,44621,47385,59278,75466,157988);
 insert into crime_time values('노동범죄',30,21,33,245,74,45,73,157);
 insert into crime_time values('안보범죄',5,2,3,13,8,10,5,9);
 insert into crime_time values('선거범죄',11,14,49,225,142,124,86,38);
 insert into crime_time values('병역범죄',11,3,44,4085,775,106,108,28);
 
 commit;

문제7. 위에 crime_time 을 unpivot 시켜서 다음의 결과를 출력하시오

select *
 from crime_time
 unpivot ( 건수 for 시간 in (F0T3,F3T6,F6T9,F9T12,F12T15,F15T18,F18T21,F21T24));
 
select listagg(column_name,',')
 from dba_tab_columns	-- 테이블의 컬럼들이 뭐가 있는지 확인하는 데이터 사전
 where table_name='CRIME_TIME';	-- 테이블명을 반드시 대문자로 작성 

문제8. 위에 unpivot 된 결과를 view 로 생성하시오 view 이름을 crime_time_view 입니다.

create or replace view crime_time_view
as
select *
 from crime_time
 unpivot (건수 for 시간 in 			(F0T3,F3T6,F6T9,F9T12,F12T15,F15T18,F18T21,F21T24));
 
select * from crime_time_view;

문제9. crime_time_view 를 조회해서 살인이 가장 많이 일어나는 시간대를 출력하시오

select *
 from crime_time_view
 where crime_type='살인'
 order by 건수 desc fetch first 1 rows only;