SQL Reporting 함수 - Grouping, Exists, Not Exists

레포팅 함수

• Rollup
• Cuve
• Grouping sets
• Grouping
• Exists 문과 not exists 문

---

• Grouping 함수

       진짜 null과 레포팅 함수에 의해서 어쩔 수 없이 출력되어야하는 null을 구분해주기 위한 함수

 

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

---

• Exists문

      A테이블에 존재하는 데이터가 B테이블에도 존재하는지 확인할 때 사용하는 문법

 

     Ex) 부서테이블에서 부서번호와 부서위치를 출력, 사원테이블에 존재하는

      부서번호에 대한 것만 출력하세요

     -> select deptno, loc from

         dept d

         where exists (select * from emp e where e.deptno = d.deptno)

select deptno, loc 
from dept d         
where exists (select * from emp e where e.deptno = d.deptno)

 @설명 : 1. 기존 서브쿼리문과 다르게 테이블 별칭 사용

           

            2. 메인쿼리의 exists 앞에 컬럼명이 없다.

           

            3. 메인쿼리의 컬럼이 서브쿼리문 안에 들어가게 되면

              반드시 메인쿼리문 부터 실행하게 된다.

           

            4. exist문은 메인쿼리의 데이터를 하나씩 서브쿼리문의 테이블에서

               하나씩 검색하는데 존재하면 바로 스캔을 멈추고

              바로 출력을 해준다. 그러므로 서브쿼리문의 테이블을

              매번 전체를 다 스캔하지 않아도 되는 장점이 있어

              검색속도 높일 수 있다.

             단, 메인쿼리의 테이블의 크기가 서브쿼리보다

             작아야 효과를 볼 수 있다.

 

---

• With 절

   : 복잡한 쿼리내에 동일 쿼리 블럭이 두번 이상 발생하는 경우에 사용하면

    좋은 성능을 보이는 sql

 

   : 테스트 테이블과 같이 임시로 사용하는 데이터를 가지고 테스트 할 때

    유용한 sql

 

    Ex) 1부터 10까지의 숫자 중에 짝수 출력

         with test_table as (select level as num from dual connect by level<=9)

         select num from test_table where mod(num,2) = 0;

     @ with 절의 임시테이블의 데이터는 with절이 끝나면 사라진다.

        with 절의 임시 테이블의 데이터는 오라클의 temporary tablespace 라는 임시저장소에 저장이 된다.

 

    @ With 절 부작용

        With절 남발 금지, 메모리 최대로 사용시, 다른 with절 대기해야함

        여러개의 with절이 동시에 한번에 돌아가면 다같이 느려진다.

        그러므로, 누군가 with 절 사용을 시간대별로 제약을 두는관리자가 있어야 한다.

 

 

      Select /* + 힌트 */ 컬럼명,…..

 

• With 절 사용할때 중요한 2가지 힌트

1. /* + inline */ : temp table 만들지 말고 서브쿼리로 풀어라
2. /* + materialize */ : temp table 만들어서 수행해라

Ex)

Explain plan for

With t_table as (select job, sum(sal) as 토탈 from emp group by job)

Select job, 토탈 from t_table where 토탈 > (select avg(토탈) from t_table);

 

select * from table(dbms_xplan.display);

 

Explain plan for

With t_table as (select /*+ inline */job, sum(sal) as 토탈 from emp group by job)

Select job, 토탈 from t_table where 토탈 > (select avg(토탈) from t_table);

 

select * from table(dbms_xplan.display);

 

@ inline -> subquery로 그냥 실행하겠다.

---

• Accept 절 사용하는 방법

 

• With 절 이용해서 최대 공약수 출력

     Accept p_num1 prompt '첫번째 숫자를 입력하세요~'

     Accept p_num2 prompt '두번째 숫자를 입력하세요~'

 

     With num_d as (select & p_num1 as num1,

                       &p_num2 as num2 from dual)

     Select max(level) as "최대공약수" from num_d

     Where mod(num1,level) = 0 and mod(num2, level) = 0

     Connect by level <=num2;

---

문제 256. 직업, 직업별 인원 수를 출력하는데, 맨 아래쪽에 전체 인원 수도 같이 출력

 

-> select job, nvl(job,'전체인원') 직업, count(*) 인원수

    from emp

    group by rollup(job);

select job, nvl(job,'전체인원') 직업, count(*) 인원수

from emp

group by rollup(job);

 

문제 257. 부서번호, 직업, 부서번호 별, 직업 별 토탈월급 출력

-> select deptno, job, sum(sal) from emp

    group by deptno, job;

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

 

문제 258. 부서별. 직업별 월급 총합을 구하고, 부서번호간의 월급 총합을 출력

-> select deptno, job, sum(sal) 월급

    from emp

    group by grouping sets((deptno, job),(deptno),())

    order by deptno,job;

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

@ (deptno, job) - 부서번호별 직업별 집계

@ (deptno) - 부서번호별 집계

@ () - 전체 집계

 

 

문제 260. 아래의 Sql을 작성하세요

-> select deptno, nvl(job,'부서토탈'), sum(sal), grouping(deptno), grouping(job) from emp

    group by grouping sets((deptno, job),(deptno),());

select deptno, nvl(job,'부서토탈'), sum(sal), grouping(deptno), grouping(job) 
from emp    
group by grouping sets((deptno, job),(deptno),());

 

문제 261. 260번 문제의 sql을 case문이나 decode문을 이용해서 맨 아래 쪽은 전체 토탈로 출력하세요

-> select deptno, case when grouping(deptno)=1 and grouping(job) = 1 then nvl(job,'전체토탈:')

    else nvl(job,'부서토탈:') end as job,  

    sum(sal) from emp

    Group by grouping sets((deptno, job),(deptno),());

select deptno, 
case when grouping(deptno)=1 and grouping(job) = 1 then nvl(job,'전체토탈:')     
else nvl(job,'부서토탈:') end as job,      
sum(sal) from emp    
Group by grouping sets((deptno, job),(deptno),());

 

문제 262. telecom_price 테이블에서 통신사를 출력하는데 우리반 테이블에

             존재하는 통신사에 대해서만 출력

-> select lower(telecom)

    from telecom_price t

    where exists (select * from emp11 e where lower(e.telecom) = lower(t.telecom));

select lower(telecom)    
from telecom_price t    
where exists (select * from emp11 e where lower(e.telecom) = lower(t.telecom));

 

문제 263. 존재하지 않는 통신사를 출력하세요

-> select lower(telecom)

  from telecom_price t

  where not exists (select * from emp11 e where lower(e.telecom) = lower(t.telecom));

select lower(telecom)   
from telecom_price t   
where not exists (select * from emp11 e where lower(e.telecom) = lower(t.telecom));

 

문제 264. self join을 사용해서 이름과 직속상사(관리자)의 이름 출력

-> Select e.ename, d.ename from emp e, emp d

    Where e.mgr = d.empno;

Select e.ename, d.ename from emp e, emp d    Where e.mgr = d.empno;

문제 265. 위의 sql을 이용해서 관리자인 사원들의 이름만 출력하는데

             중복 제거해서 출력

-> select distinct d.ename from emp e, emp d

    where e.mgr = d.empno;

select distinct d.ename 
from emp e, emp d    
where e.mgr = d.empno;

문제 266. 위의 결과 다시 출력, exists문 사용

-> Select ename from emp d

   Where exists (select ename from emp e where d.empno = e.mgr);

Select ename
from emp d   
Where exists (select ename from emp e where d.empno = e.mgr);

문제 267. 관리자가 아닌 사원들의 이름을 출력하세요

-> Select ename

    from emp d

    Where not exists (select ename from emp e where d.empno = e.mgr);

Select ename    
from emp d    
Where not exists (select ename from emp e where d.empno = e.mgr);

문제 268. 아래의 sql의 결과를 minus 집합 연산자를 사용한 sql로 구현하세요

Select deptno,loc

From dept d

Where not exists (select * from emp e where e.deptno = d.deptno);

select deptno, loc

from dept

where deptno = (select deptno from dept minus select deptno from dept);

select deptno, locfrom deptwhere deptno = (select deptno from dept minus select deptno from dept);

 

문제 269. with 절로 구구단 2단

-> With test_table as (select level as num from dual connect by level<=9)

    Select '2x'||num||'='||2*num from test_table;

 With test_table as (select level as num from dual connect by level<=9)    
 Select '2x'||num||'='||2*num from test_table;

 

문제 270. 구구단 전체를 with 절로 출력

-> with temp_table1 as (select level+1 as num1 from dual connect by level <=9),

    temp_table2 as (select level as num2 from dual connect by level<=9)

   select num1||'x'||num2||'='||num2 * num1 from temp_table1, temp_table2;

with temp_table1 as (select level+1 as num1 from dual connect by level <=9),    
temp_table2 as (select level as num2 from dual connect by level<=9)   
select num1||'x'||num2||'='||num2 * num1 from temp_table1, temp_table2;

 

문제 271. 1부터 100까지의 합을 with 절로 구현

-> with test_table as (select level as num from dual connect by level<=100)

    select sum(num) from test_table;

with test_table as (select level as num from dual connect by level<=100)    
select sum(num) from test_table;

문제 272 (a14) with절 사용해서 직각 삼각형 출력

-> With temp_table as (select level as num from dual connect by level <=9)

    Select lpad('★' ,num, '★') as star from temp_table;

With temp_table as (select level as num from dual connect by level <=9)    
Select lpad('★' ,num, '★') as star from temp_table;

문제 273. 위의 sql 수정해서 숫자를 물어보게 하고. 숫자를 입력하면 해당 숫자만큼 직각삼각형 만들게끔 하세요

-> With temp_table as (select mod(level,2) as num from dual connect by level <=&숫자)

    Select lpad('★' ,num, '★') as star from temp_table;

With temp_table as (select mod(level,2) as num from dual connect by level <=&숫자)    
Select lpad('★' ,num, '★') as star from temp_table;

문제 275. 마름모 출력

-> accept p_num prompt '숫자입력'

   With temp_table as (select level as num from dual connect by level <=&p_num)

   Select lpad(' ' ,&p_num-num, ' ')||lpad('★' ,num, '★') as star from temp_table

   union all

   select lpad(' ' ,num, ' ')||lpad('★' ,&p_num-num, '★') from temp_table;

 

 

 

튜닝을 위해서 with절을 사용하는 방법

 

문제 276. 직업, 직업별 토탈월급

-> select job,sum(sal)

    from emp

    group by job;

 

문제 277. 직업별 토탈월급들의 평균값을 출력

-> select avg(sum(sal))

    from emp

    group by job;

 

문제 278. 직업, 직업별 토탈웍릅 출력, 직업별 토탈월급이 직업별 토탈월급들의 평균값보다 더 큰것만 출력하세요

-> select job, sum(sal) from emp

    group by job

    having sum(sal)>(select avg(sum(sal)) from emp group by job);

select job, sum(sal) from emp    
group by job    
having sum(sal)>(select avg(sum(sal)) from emp group by job);

문제 279. 위의 sql을 with 절로 구현하세요

With t_table as (select job, sum(sal) as 토탈 from emp group by job)

Select job, 토탈 from t_table where 토탈 > (select avg(토탈) from t_table);

With t_table as (select job, sum(sal) as 토탈 from emp group by job)Select job, 토탈 
from t_table where 토탈 > (select avg(토탈) from t_table);

 

문제 281. with절로 수행하세요

이메일 도메인(naver, gmail..), 이메일 도메인 별 토탈나이 출력, 이메일 도메인별 토탈나이의 평균값보다 큰 것만 출력

 

-> with e_table as (select substr( rtrim (rtrim(email,'.com'),'.net') , instr(email, '@')+1) as 도메인, sum(age) 토탈나이

    from emp11

    group by substr( rtrim (rtrim(email,'.com'),'.net') , instr(email, '@')+1))

 

    select 도메인, 토탈나이 from e_table where 토탈나이> (select avg(토탈나이) from e_table);

 with e_table as (select substr( rtrim (rtrim(email,'.com'),'.net') , instr(email, '@')+1) as 도메인, sum(age) 토탈나이    
 from emp11    group by substr( rtrim (rtrim(email,'.com'),'.net') , instr(email, '@')+1))    
 select 도메인, 토탈나이 from e_table where 토탈나이> (select avg(토탈나이) from e_table);

 

문제 282. factorial을 with 절로 구현하세요

-> accept p_n1 prompt '숫자를 입력하세요~'

    with n_d as (select &p_n1 as num1 from dual)

    select '팩토리얼값:'||round(exp(sum(ln(level)))) 

   from n_d 

   connect by level <=num1;

accept p_n1 prompt '숫자를 입력하세요~'    
with n_d as (select &p_n1 as num1 from dual)    
select '팩토리얼값:'||round(exp(sum(ln(level))))    
from n_d    connect by level <=num1;