sql기본 / 데이터 분석 함수(unpivot, sum+over, ratio_to_report, rollup)

ㅁ복습

1. 오라클 설치

2. 데이터를 검색하는 select문의 6가지 절

select 컬럼명

from 테이블명

where 검색조건(일반조건)

group by 그룹핑 할 명

having 그룹함수로 검색조건

order by 정렬순서

(실행순서 : from > where > group by > having > select > order by)

3. 함수 - 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반

          - 복수행 함수 : max,min,avg, sum, count

          - 데이터 분석 함수 : rank, dense_rank, ntile, cume_dist, listagg, lag, lead, pivot

 

 

049 COLUMN를 ROW으로 출력하기(UNPIVOT)

 

ㅁ스크립트 생성

1. order2 테이블 생성

drop table order2;     < 기존 테이블을 덮어쓸 때 넣음. 처음 생성하는 거라면 굳이 넣지 않아도 됨

 

create table order2

( ename     varchar(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);

commmit;

 

ㅇunpivot문을 이용해서 column을 row로 생성하기

select *
 from order2
 unpivot( 건수 for 아이템 in (bicycle, camera, notebook) );

pivot문 : 데이터 > 컬럼으로 변경

unpivot문 :  컬럼 > 데이터

select *

 from 테이블명

 unpivot ( 컬럼을 데이터로 변경할 문법을 기술(for, in))

(* in 괄호 안에는 따옴표 없이 기술해야함)

문제208. SMITH는 BICYCLE을 몇 대 가지고 있는가

select 건수
 from order2
 unpivot( 건수 for 아이템 in (bicycle, camera, notebook) )
 where ename = 'SMITH' and 아이템 = 'BICYCLE';

문제209. 위와 같이 복잡하게 SQL을 작성하지 않도록 unpivot문을 view로 생성하시오

create view order2_view
as
 select *
  from order2
  unpivot ( 건수 for 아이템 in (bicycle, camera, notebook) );
  
 select *
  from order2_view;
  
-- view 작성방법
create view 뷰이름
as
 내가 보고싶은 결과 쿼리문;
 
-- 테이블은 데이터를 저장하지만 view는 데이터를 저장하지 않습니다.

-- view 수정방법
create or replace view order2_view  
-- or replace 문구를 넣어 view 덮어쓰기

문제210. ALLEN은 camera를 몇 대 가지고 있는가

select 건수
 from order2_view
 where ename = 'ALLEN' and 아이템 = 'CAMERA';
 
-- 기존의 order2 테이블에서는 뽑아낼 수는 있으나 어려움

문제211. 아이템, 아이템별 토탈건수를 출력하시오

select 아이템, sum(건수)
 from order2_view
 group by 아이템;

ㅇ데이터 분석을 위한 unpivot문 사용

내가 알고 싶은 답을 데이터에서 추출하는 연습

1. 질문을 정의하고

2. 데이터를 구해서

3. 데이터를 테이블로 생성

4. sql로 작성할 줄 알아야함

ex) 2018년도에 가장 근로시간이 많은 나라 1위부터 5위까지 순위는? 

-- 1. unpivot문을 이용해 데이터 컬럼 변환
select *
 from working_time
 unpivot ( 시간 for 년도 in ( y_2014, y_2015, y_2016, y_2017, y_2018) );
 
-- 2. view로 생성
create view working_time_view
as 
 select *
  from working_time
  unpivot ( 시간 for 연도 in (y_2014, y_2015, y_2016, y_2017, y_2018) );
  
select *
 from working_time_view;
 -- 이때, 컬럼명과 데이터형식을 잘 봐둬야함(특히, 데이터의 대소문자 구분)
 
-- 3. 쿼리작성
select country, 시간, rank() over (order by 시간 desc)순위
 from working_time_view
 where 연도 = 'Y_2018';
 /* 이 문제는 딱히 unpivot안써도 풀 수는 있음
    단, 1~5위까지 출력하려면 from절에 서브쿼리를 사용해야함 */
 
-- 4. 서브쿼리 작성해서 1~5위까지 출력하기
select *
 from(
  select country, y_2018, rank() over (order by y_2018 desc) 순위
 from working_time
  ) ;
  -- 서브쿼리를 from절 안에 넣어 작성
  -- 그 다음에 where절 작성 
  select *
   from(
    select country, y_2018, rank() over (order by y_2018 desc) 순위
      from working_time
       ) 
    where 순위 <= 5;

문제215. 2014년~ 2018년까지의 근무시간 출력하시오. 나라명, 나라명별 평균근로시간을 출력하시오

select country, avg(시간)
 from working_time_view
 group by country;

문제216. 위의 결과를 view로 생성하시오.(view이름은 working_avg_time)

-- view를 가지고 또 view 만들기
create or replace view working_avg_time
as
 select country, avg(시간) as avg_time
 from working_time_view
 group by country;
 -- view를 만드는 쿼리문에 그룹함수가 포함되어져 있다면 컬럼별칭을 반드시 써줘야함

문제217. working_avg_time에서 나라명, 평균근로시간, 순위를 출력하시오

select country, avg_time, rank() over (order by avg_time desc) as 순위
 from working_avg_time;

 

 

050 데이터 분석 함수로 누적 데이터 출력하기(SUM OVER)

sum(컬럼명) over()

-- 직업이 ANALYST, MANAGER인 사원들의 사원번호, 사원이름, 월급, 월급에 대한 누적치를 출력하세요
select empno, ename, sal, sum(sal) over (order by sal rows 
                                         between unbounded preceding
                                         and current row) as 누적치
 from emp
 where job in ('ANALYST','MANAGER');
 
 /* unbounded preceding : 제일 첫번째 행
    unbounded following : 맨 마지막 행
    current row : 현재 행 */

문제218. (점심시간 문제) 우리반테이블에서 이름, 나이, 나이에 대한 누적데이터를 출력하시오. 

select ename, age, sum(age) over (order by age rows
                                  between unbounded preceding
                                  and current row) as 누적나이
 from emp14;
 
 -- 역으로 누적치 계산
 select ename, age, sum(age) over (order by age rows
                                  between current row
                                  and unbounded following) as 누적나이
 from emp14;

문제219. 부서번호, 이름, 월급, 월급누적치가 출력되게 하는데 월급의 누적치가 부서번호별로 각각 월급의 누적치가 출력되게 하시오

select deptno, ename, sal, sum(sal) over (partition by deptno
                                           order by sal rows between unbounded preceding
                                           and current row) as 월급누적치
 from emp;

 

 

051 데이터 분석 함수로 비율 출력하기(RATIO_TO_REPORT)

ratio_to_report : 원형그래프처럼 전체에서 얼마만큼의 파이를 가지고 있는지 알아내는 함수

-- 부서번호가 20번인 사원들의 사원번호, 이름, 월급, 월급에 대한 비율을 출력하세요
select deptno, ename, sal, ratio_to_report(sal) over () as 비율
 from emp
 where deptno = 20;

문제220. 위의 결과를 다시 출력하는데 비율이 높은 사원부터 출력하시오

select deptno, ename, sal, ratio_to_report(sal) over () as 비율,
                           sal/sum(sal) over () as 비교비율
 from emp
 where deptno = 20
 order by 4 desc;

문제221. 사원 테이블의 토탈월급을 출력하시오.

select sum(sal)
 from emp;

문제222. 사원이름, 월급, 사원테이블의 토탈월급을 출력하시오

select ename, sal, sum(sal)
 from emp;
 /* 에러
  sum(sal)은 하나의 데이터만 출력하려고 하는데, ename과 sal은 여러개의 데이터가 나오려 하기때문 */

 -- 행마다 sum을 출력시키기 위해서는 sum over()를 뽑아야함
select ename, sal, sum(sal) over () 토탈월급
 from emp;
 -- sum과 sum over()의 차이

문제223. 부서번호, 이름, 월급, 자신의 월급의 비율을 출력하는데 월급의 비율이 부서번호별로 각각 출력되게 하시오 

select deptno, ename, sal, ratio_to_report(sal) over (partition by deptno) as 비율
 from emp;

 

문제224. (복습문제) 사원테이블의 직업의 종류가 몇가지가 있는지 출력하시오

-- [내가 쓴 코드]
select count(*)
 from (
  select job, count(*)
   from emp
   group by job
   );
   
-- [선생님이 쓰신 코드]
select count(distinct job) as 직업수
 from emp;

문제225. (복습문제) 우리반에 전공이 몇가지인지 출력하시오

select count(distinct major) as 전공수
 from emp14;

 

 

052 데이터 분석 함수로 집계 결과 출력하기 1(ROLLUP)

rollup()을 사용하면 전체 집계값이 아래에 출력됨. (sum, avg, min, max, count 가능) < group by절에만 사용 가능

-- 직업, 직업별 토탈월급을 출력하는데 맨 아래에 전체 토탈월급이 출력되게 하세요
select job, sum(sal)
 from emp
 group by rollup(job);

문제226. 부서번호, 부서번호별 평균월급을 출력하는데 맨 아래에 전체 평균월급도 출력되게 하시오

select deptno, round(avg(sal),2)
 from emp
 group by rollup(deptno);

문제227. 나이, 나이별 인원수를 출력하는데 맨 밑에 전체 인원수가 출력되게 하시오

select age, count(*)
 from emp14
 group by rollup(age);

문제228. 위의 결과는 나이가 asc하게 출력되고 있는데 나이를 desc하게 출력하시오

select age, count(*)
 from emp14
 group by rollup(age)
 order by 1 desc nulls last;

 

ㅁorder by 컬럼명 asc/desc nulls last / nulls first 로 정렬

 

문제229. 위의 결과를 다시 출력하는데 null로 출력되는 자리에 토탈인원이라는 글씨가 출력되게 하시오

select decode(age, null, '토탈인원',age), count(*)
 from emp14
 group by rollup(age)
 order by age desc nulls last;
 

select nvl(to_char(age),'토탈인원'), count(*)
    from emp14
    group by rollup(age)
    order by age desc nulls last;
    
/* order by 절에 숫자를 쓰지 않고 컬럼명을 써서 실행을 하면 order by 절까지 다 수행하고 맨 마지막에 nvl함수를 실행하는 것이고
   order by 절에 숫자를 쓰게 되면 nvl함수를 실행하고 나서 order by 절이 실행됩니다 */

문제230. 입사년도(4자리), 입사년도별 토탈월급을 출력하는데 맨 아래에 전체토탈월급을 출력하시오

select to_char(hiredate, 'RRRR'), sum(sal)
 from emp
 group by rollup(to_char(hiredate, 'RRRR'));

ㅇrollup함수에 컬럼을 여러개 사용하기

문제231. 부서번호, 직업, 부서번호별 직업별 토탈월급을 출력하시오

select deptno, job, sum(sal)
 from emp
 group by deptno, job
 order by deptno, job;
 
-- 그동안에는 group by절에 컬럼을 하나만 사용했었는데, 위와같이 group by절에 컬럼을 여러개 사용가능

문제232. class_type, 나이, 인원수를 출력하는데 class_type, 나이별 인원수가 출력되게 하시오

select class_type, age, count(*)
 from emp14
 group by class_type, age
 order by class_type, age;

문제233. 위의 결과에서 맨 아래에 전체 인원수가 출력되게 하시오

select class_type, age, count(*)
 from emp14
 group by rollup((class_type, age));
 /* rollup에 컬럼을 여러개 쓴 상황에서 전체집계를 출력하려면
    괄호를 이용해 class_type과 age를 묶어줘야함 : rollup((컬럼명1, 컬럼명2))
    괄호로 묶지 않을 경우 class_type따로 age 따로 그룹핑됨 */

문제234. 이번에는 A반의 전체 인원수와 B반의 전체 인원수가 출력되게 하고 싶다면?

select class_type, age, count(*)
 from emp14
 group by rollup(class_type, age);
 
 /* 그룹핑한 결과는 rollup안에 쓴 컬럼의 개수 + 1개 만큼 만들어짐
  그룹핑한 결과 3가지
  1. class_type, age
  2. class_type
  3. 전체 */
 
select class_type, age, count(*)
 from emp14
 group by rollup((class_type), (age));
 -- 동일한 결과 출력됨

문제235. 아래의 결과를 출력하시오

select deptno, job, sum(sal)
 from emp
 group by rollup(deptno, job);

문제236. 아래의 sql을 실행하세요

select empno, ename, sal

 from emp;

문제237. 맨 아래에 전체 토탈월급이 출력되게 하시오

select empno, ename, sum(sal)
 from emp
 group by rollup((empno,ename));
 
 -- empno, ename 모두 unique 하기 때문에 sum을 씌워도 상관없음.

 

문제238. (오늘의 마지막문제) 통신사, 이름, 인원수를 출력하는데 아래와 같이 집계된 결과가 출력되게 하시오

-- [내가 쓴 코드]
select class_type, 통신사, count(*)
 from (select class_type, decode(lower(telecom), 'skt','sk',lower(telecom)) as 통신사
        from emp14)
  group by rollup( class_type, 통신사);

-- [다른 사람이 쓴 코드]
select class_type, decode(lower(telecom), 'skt', 'sk', lower(telecom)), count(*)
 from emp14
 group by rollup( class_type, decode(lower(telecom), 'skt', 'sk', lower(telecom)) );