sql기본 / rollup, cube, grouping sets, row_number

ㅁ 복습

- 옵티마이져 : 스스로 학습해서 오라클 환경에서 돌아가는 SQL들의 성능을 높이는 기능

- 함수 

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

2. 복수행 함수 : max, ,min, avg, sum, count

3. 데이터 분석함수 : rank, dense_rank, ntile, listagg, cume_dist, lag, lead, sum() over (), ratio_to_report(), sum+decode, pivot, unpivot, rollup

 

 

ㅇrollup을 사용한 현업사례

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

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

문제240. 위의 결과를 다시 출력하는데 rollup을 사용해서 각각의 부서번호별 토탈월급과 전체 토탈월급이 출력되게 하시오

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

문제241. (점심시간 문제) 다음과 같이 결과가 나오게 하시오.

-- 1. 처음 쓴 코드
select deptno, case when deptno is null then decode(to_char(grouping(job)),1,'전체토탈',job)
                   when job is null then decode(to_char(grouping(job)),1,'부서토탈',job)
                   else job end as job2, sum(sal)
 from emp
 group by rollup(deptno, job);


--2. 수정코드
select deptno, case when deptno is null then '전체토탈'
                   when job is null then '부서토탈'
                   else job end as job2, sum(sal)
 from emp
 group by rollup(deptno, job);


--3. 수정코드2  grouping 사용
select deptno, case when grouping(deptno)+grouping(job) = 2 then '전체토탈'
                    when grouping(deptno)+grouping(job) = 1 then '부서토탈'
                    else job end as job2, sum(sal)
 from emp
 group by rollup(deptno, job);

문제242. grouping 함수를 이용해서 아래의 sql을 실행하고, grouping함수를 이해하시오 

ㅇrollup과 같은 레포팅성 결과를 출력하는 함수 : cube, grouping sets, grouping

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

grouping에서 1로 나오는 부분은 집계결과를 보기 위해서 어쩔 수 없이 null로 나오는 부분을 1로 출력(이외에는 0으로 출력)

 

 

053 데이터 분석 함수로 집계 결과 출력하기 2(CUBE)

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

집계결과가 맨 아래에 나오게 하고 싶다면 rollup을 사용하면되고 집계결과가 맨 위에 나오게 하고 싶다면 cube를 사용하면 됨.

문제243. 부서번호, 부서번호별 토탈월급을 출력하는데 전체 토탈월급을 맨 위에 출력되게 하시오

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

문제244. 위의 결과를 아래와 같이 출력되게 하시오

select decode(deptno,null,'전체토탈',deptno), sum(sal)
 from emp
 group by cube(deptno);

문제245. 직업, 직업별인원수를 출력하는데 아래와 같이 맨위에 전체토탈이 출력되게 하시오

select nvl(job,'전체토탈'), count(*)
 from emp
 group by cube(job);
 
--job이 문자함수이기 때문에 to_char 사용하지 않아도 됨

ㅇcube 함수에 컬럼을 여러개 사용할 때

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

rollup과 다르게 한가지 더 출력되는 그룹핑된 컬럼은 제일 처음에 지웠던 컬럼

 

 

054 데이터 분석 함수로 집계 결과 출력하기 3(GROUPING SETS)

--직업별 토탈월급과 부서번호별 토탈월급을 같이 출력하시오
select deptno, job, sum(sal)
 from emp
 group by grouping sets( (deptno),(job),() );

rollup과 cube보다 출력되는 결과를 더 쉽고 정확하게 예측할 수 있음

grouping sets ((그룹핑할 컬럼1), (그룹핑할 컬럼2), () )

아무것도 안 넣은 괄호는 전체집계결과

문제246. 위의 sql을 order by 절을 사용해서 아래와 같이 정렬되게 하시오

select deptno, job, sum(sal)
 from emp
 group by grouping sets( (deptno),(job), () )
 order by deptno, job asc;

문제247. 아래의 sql 결과를 grouping sets만 이용해서 출력하시오

select deptno, sum(sal)

 from emp

 group by rollup(deptno);

select deptno, sum(sal)
 from emp
 group by grouping sets( (deptno), () );

(rollup과 grouping sets에 정렬 기능이 있는건 아님 > 옵티마이져에 따라 결과순서가 달라짐)

문제248. 아래의 sql 결과를 grouping sets를 사용해서 출력하시오

select deptno, job, sum(sal)

 from emp

 group by rollup( (deptno, job) );

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

문제249. grouping sets를 이용해서 아래와 같이 결과를 출력하시오

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

 

문제250. (복습문제) 직업, 직업별 최대월급을 출력하시오(세로출력)

select job, max(sal)
 from emp
 group by job;

문제251. (복습문제) 직업, 직업별 최대월급, 직업별 최소월급, 직업별 평균월급, 직업별 토탈월급을 출력하시오

select job, max(sal), min(sal), avg(sal), sum(sal)
 from emp
 group by job;

문제252. (복습문제) 위의 결과를 아래와 같이 출력하시오

-- 1. grouping sets
select job, max(sal), min(sal), avg(sal), sum(sal)
 from emp
 group by grouping sets((job),());
 

-- 2. rollup
select job, max(sal), min(sal), avg(sal), sum(sal)
 from emp
 group by rollup(job);

문제253. (복습문제) 부서번호, 부서번호별 토탈월급을 출력하시오(가로출력)

select sum(decode(deptno, 10, sal, null)) as "10",
       sum(decode(deptno, 20, sal, null)) as "20",
       sum(decode(deptno, 30, sal, null)) as "30"
 from emp;

문제254. 위의 쿼리문 select절에 job을 추가하고 실행하시오

select job, sum(decode(deptno, 10, sal, null)) as "10",
       sum(decode(deptno, 20, sal, null)) as "20",
       sum(decode(deptno, 30, sal, null)) as "30"
 from emp
 group by job;

문제255. 위의 sql을 이용해서 아래의 결과를 출력하시오

select job, sum(decode(deptno, 10, sal, null)) as "10",
       sum(decode(deptno, 20, sal, null)) as "20",
       sum(decode(deptno, 30, sal, null)) as "30"
 from emp
 group by rollup(job);

문제256. (점심시간문제2) 위의 sql을 이용해서 아래의 결과를 출력하시오

select nvl(job, '토탈값 :') as 직업, sum(decode(deptno, 10, sal, null)) as "10",
                                    sum(decode(deptno, 20, sal, null)) as "20",
                                    sum(decode(deptno, 30, sal, null)) as "30", 
       sum(sal) as 토탈값
 from emp
 group by rollup(job);

문제257. class_type, class_type별 인원수를 출력하시오

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

문제258. emp14_address를 조회해서 통신사, 통신사별 인원수를 출력하시오

select telecom, count(*)
 from emp14_address
 group by telecom;

문제259. 위의 결과를 아래와 같이 가로로 출력하시오

select sum(decode(telecom, 'kt', 1, 0)) as "kt",
       sum(decode(telecom, 'sk', 1, 0)) as "sk",
       sum(decode(telecom, 'lg', 1, 0)) as "lg"
 from emp14_address;

문제260. 지역별 가입자의 인원수를 출력하시오

select address2, sum(decode(telecom, 'kt', 1, 0)) as "kt",
                 sum(decode(telecom, 'sk', 1, 0)) as "sk",
                 sum(decode(telecom, 'lg', 1, 0)) as "lg"
 from emp14_address
 group by address2;

문제261. 지역별, 통신사별 토탈값이 출력되도록 해라

select nvl(address2, '토탈값:')  , sum(decode(telecom, 'kt', 1, 0)) as "kt",
       sum(decode(telecom, 'sk', 1, 0)) as "sk",
       sum(decode(telecom, 'lg', 1, 0)) as "lg",
       count(*) as 토탈값 
 from emp14_address
 group by rollup(address2);

select address2, 1
 from emp14_address;

-- 이 결과를 보면 각 학생마다 1이 행의 데이터로 출력이 됨
-- 그래서 1을 지역별로 집계하면 count와 동일한 값 출력

 

 

055 데이터 분석 함수로 출력 결과 넘버링 하기(ROW_NUMBER)

row_number 데이터 함수는 출력되는 select문 결과행을 넘버링 하는 것

넘버링의 쓰임새 > 특정행만 뽑고 싶을 때 유용

-- 사원번호, 사원이름, 월급, 순위를 출력하는 결과 끝에 번호를 넘버링해서 출력하세요
select empno, ename, sal, row_number() over (order by sal desc) 
 from emp
 where deptno = 20;

문제262. 위의 결과를 사원번호대로 번호를 부여해서 출력되게 하시오

select empno, ename, sal, row_number() over (order by empno asc) 
 from emp
 where deptno = 20;

문제263. 위의 결과에서 두번째 행을 출력하시오

select *
 from (
select empno, ename, sal, row_number() over (order by empno asc) 번호
 from emp
 where deptno = 20
 )
 where 번호 = 2 ;

문제264. 사원테이블에서 3번째행을 출력하시오

 select *
 from (
     select e.*, row_number() over (order by rowid) 번호
      from emp e
       )
 where 번호 = 3;

select empno, ename, sal, job, mgr, comm, hiredate, deptno,
       row_number() over (order by rowid) 번호
 from emp;
 
 -- rowid는 행(row)의 주소
 
select e.*, row_number() over (order by rowid) 번호
  from emp e;
  /* 테이블명(or 별칭).* = 테이블의 모든 컬럼을 출력하면서 그 옆에 여러개의 컬럼을 출력하고 싶을 때 사용
  별칭 사용 시 from 절에도 별칭 표시 해줘야함*/