109 WITH절 사용하기 1(WITH ~ AS)
--시간이 오래 걸리는 무거운 쿼리문이 하나의 쿼리문에서 반복 사용된다면?
하나의 sql에서 비슷한 select 문장이 반복되어서 나타나는 경우 with절로 작성하면 성능을 개선할 수 있음.
-- (튜닝전) with절을 사용하지 않은 무거운 SQL select e1.*, e2.* from ( select deptno, sum(sal) as sumsal from emp where deptno != 20 group by deptno) e1, ( select deptno, sum(sal) as sumsal from emp where deptno != 30 group by deptno) e2 where e1.deptno = e2.deptno ; -- (튜닝후) with emp500 as (select deptno as 부서번호, sum(sal) as 토탈월급 from emp group by deptno ) select e1.*, e2.* from emp500 e1, emp500 e2 where e1.부서번호 = e2.부서번호 and e1.부서번호 != 20 and e2.부서번호 != 30;
튜닝후 sql을 살펴보면,
1. select deptno as 부서번호, sum(sal) as 토탈월급
from emp
group by deptno
의 결과를 출력하는 임시테이블 emp500로 설정
2. emp500을 가지고 select절이 수행됨
3. 임시테이블 emp500은 with절이 끝날때까지만 유지됨
문제600. 위의 with절의 실행계획을 확인하시오

ㅇwith절 작성 시 주의사항
여러 개의 with절을 동시에 수행하면 다같이 느려짐.
-- with절이 길어져서 이를 다시 튜닝해야할 때는 "힌트" 활용 with emp500 as (select /*+ inline */ deptno as 부서번호, sum(sal) as 토탈월급 from emp group by deptno ) select e1.*, e2.* from emp500 e1, emp500 e2 where e1.부서번호 = e2.부서번호 and e1.부서번호 != 20 and e2.부서번호 != 30;
with절에 inline 힌트를 줘서 튜닝

ㅇwith절 사용히 중요 힌트 2가지
1. inlin : temp table 구성 안하겠다
2. materialize : temp table 구성 하겠다.
with절 여러개가 동시에 수행되면 다같이 느려지기 때문에 코디네이트 하는 사람이 있어야 함.
문제601. 아래의 sql을 with절로 변경하시오
select ' 2 x ' || empno || ' = ' || empno *2
from (
select level as empno
from dual
connect by level <= 10 );
with emp200 as ( select level as empno from dual connect by level <= 10 ) select ' 2 x ' || e.empno || ' = ' || e.empno *2 from emp200 e
임시테이블을 생성해달라고 dba에게 요청하지 않아도 내가 짠 SQL에서 with절로 임시테이블을 생성할 수 있는 장점이 있음.
문제602. 1부터 10까지의 숫자 중에 짝수만 출력하시오
with emp10 as ( select level as num from dual connect by level <= 10) select num from emp10 where mod(num,2)= 0;
문제603. 1부터 10까지의 합을 출력하시오
with emp10 as ( select level as num from dual connect by level <= 10) select sum(num) from emp10;
문제604. (수학식을 SQL로 구현하기) 아래의 수학식을 SQL로 구현하시오

select ln(10) from dual;
= 밑수가 자연상수(e), 진수가 10인 로그함수
문제605. 아래의 수학식을 SQL로 구현하시오

select exp(10) from dual;
= 자연상수e의 10승
문제606. 1부터 10까지의 곱을 출력하시오

with emp10 as ( select level as num from dual connect by level <= 10) select exp(sum(ln(num))) from emp10;
문제607. 1부터 6까지의 숫자중에 하나를 랜덤으로 출력하시오
( = 주사위를 한번 던지세요)
select dbms_random.value(0.5, 6.5) from dual;
0.5 ~ 6.5 사이의 난수가 출력됨
select round(dbms_random.value(0.5, 6.5)) from dual;
round를 둘러주면 1에서 6사이의 난수가 출력됨
문제608. 주사위를 10번 던져서 10개의 숫자를 출력하시오
/* with game as ( select level as num from dual connect by level <= 10) select round(dbms_random.value(0.5, 6.5) ) from game; game테이블 행의 갯수만큼 주사위가 반복됨 = 주사위를 10개 던진 것 */ -- 주사위 1개를 10번 던진 것 with game as ( select round(dbms_random.value(0.5, 6.5 ) ) as num from dual ) select num from game connect by level <= 10;
문제609. 주사위를 100번 던져서 숫자 3이 나올 확률은 어떻게 되는가
with game as ( select round(dbms_random.value(0.5, 6.5 ) ) as num from dual ) select count(*) / 100 from (select num from game connect by level <= 100) where num = 3;
문제610. 주사위를 던져서 주사위의 눈이 홀수가 나올 확률을 출력하시오
with game as ( select round(dbms_random.value(0.5, 6.5 ) ) as num from dual ) select count(*) / 100000 from (select num from game connect by level <= 100000) where mod(num,2) = 1 -- where num in (1,3,5) = 주사위라서 숫자가 별로 없기때문에 나열 가능
문제611. (확률문제) 두개의 주사위가 있다. 두개의 주사위를 동시에 던져서 하나는 짝수가 나오고 다른 하나는 홀수가 나올 확률을 구하시오(주사위를 십만번 던지세요)
with dice1 as ( select round(dbms_random.value(0.5, 6.5 ) ) as num1 from dual ), dice2 as (select round(dbms_random.value(0.5, 6.5 ) ) as num2 from dual ) select count(*) / 100000 from (select num1, num2 from dice1 d1, dice2 d2 connect by level <= 100000 ) where num1 in (1,3,5) and num2 in (2,4,6); with dice_table as ( select round(dbms_random.value(0.5, 6.5 ) ) as num1, round(dbms_random.value(0.5, 6.5 ) ) as num2 from dual ) select count(*) / 100000 from (select num1, num2 from dice_table connect by level <= 100000 ) where (num1 in (1,3,5) and num2 in (2,4,6) ) or (num2 in (1,3,5) and num1 in (2,4,6) ) ;
문제612. 주사위 3개를 동시에 던져서 주사위 3개의 합이 10이 되는 확률?
with dice1 as ( select round(dbms_random.value(0.5, 6.5 ) ) as num1 from dual ), dice2 as (select round(dbms_random.value(0.5, 6.5 ) ) as num2 from dual ), dice3 as (select round(dbms_random.value(0.5, 6.5 ) ) as num3 from dual ) select count(*) / 100000 from (select num1 + num2 + num3 as sumnum from dice1 d1, dice2 d2, dice3 d3 connect by level <= 100000 ) where sumnum = 10;
문제613. (오늘의 마지막 문제) 주사위 1개와 동전 1개를 동시에 던져서 주사위의 눈이 5가 나오고 동전이 앞면이 나오는 확률을 구하시오.
with dice as ( select round(dbms_random.value(0.5, 6.5 ) ) as num1 from dual ), coin as (select round(dbms_random.value(0.5, 2.5 ) ) as num2 from dual ) select count(*) / 100000 from (select num1, num2 from dice d, coin c connect by level <= 100000 ) where num1 = 5 and num2 = 1 ;
'Study > class note' 카테고리의 다른 글
sql / 알고리즘 문제 (0) | 2021.12.01 |
---|---|
sql중급 / with절(subauery factoring) (0) | 2021.12.01 |
sql중급 / 데이터분석 복습 (0) | 2021.11.30 |
sql중급 / primary key, unique, not null, check, foreign key (0) | 2021.11.29 |
sql중급/ flachback version query, transaction query (0) | 2021.11.29 |