23.09.18. SQL Developer 예제 49 ~ 55번

49. COLUMN을 ROW로 출력하기

-- 예제49. 다음과 같이 컬럼이 데이터로 들어가게 하세요.
select * 
    from order2 
    unpivot ( 건수 for 아이템 in (BICYCLE, CAMERA, NOTEBOOK) ) ;

-- 예제49_문제1. 범죄원인 테이블을 생성하고 방화사건의 가장 큰 원인이 무엇인지 출력하세요.
select * 
    from crime_cause
    unpivot( 건수 for 범죄원인 in (생계형, 유흥, 도박, 허영심, 복수, 해고, 징벌, 가정불화, 호기심, 유혹, 사고, 불만, 부주의, 기타) ) 
    where crime_type = '방화' 
    order by 건수 desc ;

1) unpivot(출력할 컬럼이름 for 데이터가 출력될 컬럼이름 in (데이터1, 데이터2, ...) )
2) unpivot문 작성 시 쿼테이션마크(' ' 또는 " ")는 작성하지 않아도 됨
   

50. 데이터 분석 함수로 누적 데이터 출력하기 

--  예제50. 직업이 ANALYST, MANAGER 인 사원들의 사원번호, 사원이름, 월급, 월급에 대한 누적치를 출력하세요.
select empno, ename, sal, sum(sal) over( order by empno ) 누적치
    from emp
    where job in ('ANALYST', 'MANAGER') ;
    
select empno, ename, sal, sum(sal) over( order by empno rows
                                         between unbounded preceding and current row) 누적치
    from emp
    where job in ('ANALYST', 'MANAGER') ;

-- 예제 50_문제1. 부서번호가 20번인 사원들의 사원이름, 월급, 월급에 대한 누적치가 출력되게 하세요.
select deptno, ename, sal, sum(sal) over( order by deptno ) 누적치
 from emp 
 where deptno = 20 ;

1) 작성법(원칙): order by 기준 컬럼 (rows between unbounded preceding and current row) 생략가

 

51. 데이터 분석 함수로 비율 출력하기

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

-- 예제51_문제1. 사원 테이블 전체에서 사원번호, 이름, 월급, 월급에 대한 비율을 출력하세요
select empno, ename, sal, round(ratio_to_report(sal) over(), 2) 비율
    from emp ;

1) 컬럼명 : ratio_to_report(컬럼명) over()

 

52. 데이터 분석 함수로 집계 결과 출력하기 ①

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

-- 예제52_문제1. 부서번호, 부서번호별 토탈월급을 출력하는데 맨 아래에 전체 토탈 월급이 출력되게 하세요
select deptno, sum(sal)
    from emp 
    group by rollup(deptno) ;

1)  group by 절에 rollup(기준 컬럼) 

 

53. 데이터 분석 함수로 집계 결과 출력하기 ②

-- 예제53. 직업, 직업별 토탈월급을 출력하는데 맨 위에 다음과 같이 전체 토탈월급이 출력되게 하세요
select job, sum(sal)
    from emp
    group by cube(job) ; 

-- 예제53_문제1. 입사한 년도(4자리), 입사한 년도별 토탈 월급을 출력하는데 맨위에 사원 테이블의 전체 토탈월급이 출력되게 하세요 
select to_char(hiredate, 'RRRR'), sum(sal) 
    from emp
    group by cube( to_char(hiredate, 'RRRR')) ;

1) 작성법 : group by절에 cube(기준 컬럼) 

 

54. 데이터 분석 함수로 집계 결과 출력하기 ③

-- 예제54. 다음과 같이 직업별 토탈월급과 부서번호별 토탈월급과 전체 토탈월급을 같이 출력하세요.
select deptno, job, sum(sal) 
    from emp 
    group by grouping sets ( deptno, job, () ) ;

-- 예제54_문제1. 입사한 년도(4자리), 입사한 년도(4자리)별 토탈월급과 직업, 직업별 토탈월급을 위아래로 같이 출력하세요
select to_char(hiredate, 'RRRR'), job, sum(sal) 
    from emp 
    group by grouping sets( (to_char(hiredate, 'RRRR')), (job)) ;

1) group by grouping sets(기준 컬럼1, 기준 컬럼2, ...) 
2) grouping sets 안에서 괄호()의 역할은 기준 컬럼들이 동시에 묶인 결과를 출력할 지, 단일 결과를 출력할지 를 결정하는 중요한 역할

 

55. 데이터 분석 함수로 출력 결과 넘버링 하기

-- 예제55. 부서번호가 20번인 사원들의 사원번호, 사원이름, 월급, 순위를 출력하는 결과 끝에 번호를 넘버링해서 출력하세요
select empno, ename, sal, rank() over( order by sal desc ) as rank,
                          dense_rank() over( order by sal desc ) as dense_rank, 
                          row_number() over ( order by sal desc ) 번호 
    from emp 
    where deptno = 20 ;

 select empno, ename, sal, row_number() over ( order by empno asc ) 번호 
    from emp ;
-- 예제55_문제1. 월급이 1000 에서 3000 사이인 사원들의 이름과 월급을 출력하는데 출력하는 결과 맨 끝에 번호를 넘버링 해서 출력하세요
select ename, sal, row_number() over (order by empno asc ) as 번호 
from emp
where sal between 1000 and 3000 ;