서술형문제3 전체범위처리 SQL 작성

문제

같은 데이터를 두번 읽지 않고서 같은 결과집합을 출력하도록 아래 두 SQL을 각각 작성하시오.

(단, 부분범위처리가 불가능한 상황 전체범위처리 기준으로 튜닝할 것)

-'주문일자'의 데이터 타입은 문자형 8자리

- 거래 업체는 10,000개

- 월평균 주문건수는 100만 건

 

 

SQL 1

select b.주문번호,b.업체번호,b.주문일자,b.주문금액,b.총주문횟수,a.평균주문금액,a.최대주문금액
from (
select 업체번호,count(*) 총주문횟수, avg(주문금액) 평균주문금액, max(주문금액) 최대주문금액
from 주문
where 주문일자 like '201509%'
group by 업체번호
) a, 주문 b
where b.업체번호 = a.업체번호
and b.주문일자 like '201509%'
order by a.평균주문금액 desc

SQL 2

select b.주문번호, b.업체번호, b.주문일자, b.주문금액
from (
select 업체번호, max(주문번호) 마지막주문번호
from 주문
where 주문일자 like '201509%'
group by 업체번호
) a, 주문 b
where b.업체번호 = a.업체번호
and b.주문번호 = a.마지막주문번호

 

튜닝 Point

문제에서 확인할 수 있는 포인트는 두가지 정도인것 같습니다.

1.같은 데이터를 두번 읽지 말것

- 현재 같은 테이블 조인으로 되어 있는 것을 한번만 읽게 하라

2.전체범위처리 기준으로 SQL을 재작성할 것

- 스칼라 서브쿼리를 사용해서 작성할 필요가 없다는 뜻

그리고 이번 문제의 경우에는 머리속으로만 생각하고 손으로만 풀 수 밖에 없어서 100%이해가 되지는 않았습니다. 저도 문제집에 있는 자료를 풀고 정리하고 포스팅하기 때문에 백데이터 없이 SQL만 있어서 제대로 작성한 건지 100% 확신할 수 는 없지만 이것을 이해해야 제가 더 성장하는 것이겠죠

 

1번SQL의 경우에는 a 주문 테이블에서 그룹화하여 count,avg,max를 구한 후 b 주문 테이블과 조인하여 결과값을 보여주고 있습니다.

이것을 한번의 데이터 읽기로 해결하려면 현재 사용한 집계함수에서 그룹화가 된 값으로 보여주게 하면 될 것 같습니다.

 

1번 SQL 정답

select 주문번호,업체번호,주문일자,주문금액
,count(*) over (partition by 업체번호) as 총주문횟수
,avg(주문금액) over(partition by 업체번호) as 평균주문금액
,max(주문금액) over(partition by 업체번호) as 최대주문금액
from 주문
where 주문일자 like '201509%'
order by 평균주문금액 desc

 

2번 SQL의 경우에는 업체번호별 마지막 주문번호의 주문번호,업체번호,주문일자,주문금액을 추출하는 SQL입니다.

마찬가지로 주문테이블을 두번 조인해서 결과값을 내고 있습니다.

한번의 데이터 읽기로 표현하려면 한번 데이터읽기를 할 때 값을 다 정제해서 만들어야 할 것 같습니다.

즉, 이미 각 업체별로 마지막 주문번호와 그에 맞는 값들이 나올 수 있게 정제한 후 그 중에서 주문일자에 맞게 뽑아내면 될 것 같습니다.

 

2번 SQL 정답

select 주문번호,업체번호,주문일자,주문금액
from
(
select max(주문번호) over (partition by 업체번호) 마지막주문번호
,주문번호,업체번호,주문일자,주문금액
from 주문
where 주문일자 like '201509%'
)
where 주문번호=마지막주문번호

 

다른 정답

select 주문번호,업체번호,주문일자,주문금액
from
(
select row_number () over (partition by 업체번호 order by 주문번호 desc) as rn
,주문번호,업체번호,주문일자,주문금액
from 주문
where 주문일자 like '201509%'
)
where rn = 1