[DB] SQLP 실기 - 3

한번만 테이블을 읽기

위 컬럼을 구하시오

select sum(cnt) 전체사원수,
count(deptno) 부서개수,
round(sum(cnt)/count(deptno), 1) 부서별평균사원,
max(cnt) 부서별최대사원수,
min(cnt) 부서별최소사원수,
min(min_dept) 최소사원의부서,
max(max_dept) 최대사원의부서
from(select deptno, count(*) cnt,
     first_value(deptno) over(order by count(*)) min_dept,
     first_value(deptno) over(order by count(*) desc) max_dept
     from emp
     group by deptno);

• 인라인 뷰에서 부서별 사원수, 그것을 정렬한 첫번째값, 역정렬한 첫번째값을 가져온다.
• 나머지는 sum round 나누기 등으로 연산하고 first_value로 가져온 값을 그룹화 해주기만 하면된다
  (인라인 뷰에서 최소사원 등을 미리 구하였으므로)

파티션, Pruning

인덱스 : local prefixed (상담일자로 인덱스 분할),(상담자ID, 상담일자로 정렬)

select 상담자ID, count(1) 상담건수,
count(case when rslt_cd = '0009' then 1 end) 완료건수,
count(case when rslt_cd = '0009' and atfrslt_cd = '21' then 1 end) 이관건수,
count( distinct(cust_id)) 상담고객수
from consult
where 상담자ID = :CONSULTANT_ID
상담일자 between substr(:dt, 1, 6) ||'01' and :dt
and 상담일자||상담시간 <= :dt||'1200'
group by 상담자ID

• Patition Pruning을 위해 필수로 상담일자 컬럼을 가공없이 기술한다.
  상담일자 between substr(:dt, 1, 6) ||'01' and :dt

• 그후 시간을 지정하기 위해 상담일자 + 시간을 지정한다.

• 인덱스는 Local Prefixed 이고 파티션 키는 상담일자.

• 인덱스 키는 상담자ID, 상담일자로 최대효율로 접근한다.

• Local 인덱스는 자동으로 관리가 되므로 해당문제에 적격이다.

• case문으로 완료건수, 이관건수, Unique한 상담고객수를 select한다.