[Oracle] 집계와 조건 분기

1. 집계와 조건 분기

집계를 수행하는 쿼리를 작성할 때, 쓸데없이 길어지는 경우를 자주 볼 수 있습니다. 다음과 같은 Population 테이블을 생각해 봅시다. 성별 1은 남자, 2는 여성을 의미한다고 가정하겠습니다.

 

prefecture(지역 이름)	sex(성별)	pop(인구)
성남	1	60
성남	2	40
수원	1	90
수원	2	100
광명	1	100
광명	2	50
일산	1	100
일산	2	100
용인	1	20
용인	2	200

 

다음이 우리가 원하는 결과 입니다.

2. UNION을 사용한 집계

절차 지향적으로 문제를 풀 때 남성의 인구를 지역별로 구하고, 여성의 인구를 지역별로 구한 뒤 합치는 방법을 생각할 것 입니다. 남성과 여성의 인구가 별도의 레코드로 나오게 됩니다. 그리고 GROUP BY를 사용하여 하나의 레코드로 집약하는 것 입니다. WHERE 구에서 sex 필드로 분기를 하고, 결과를 UNION으로 MERGE 하는 절차 지향적인 구성입니다.

SELECT prefecture, SUM(pop_men) AS pop_men, SUM(pop_wom) AS pop_wom
FROM   (
            SELECT prefecture, pop AS pop_men, null AS pop_wom
            FROM   Population
            WHERE  sex = '1'
            UNION
            SELECT prefecture, NULL AS pop_men, pop AS pop_wom
            FROM   Population
            WHERE  sex = '2'
        ) TMP
GROUP BY prefecture;

인라인 뷰 데이터

실행 계획

Population 테이블에 풀 스캔이 2회 수행되는 것을 확인할 수 있습니다.

3. CASE를 이용한 조건 분기

이 문제는 CASE 식의 응용 방법으로 굉장히 유명한 표측/표두 레이아웃 이동 문제입니다. 원래 SQL은 이러한 결과 포맷팅을 목적으로 만들어진 언어가 아닙니다. 하지만 실무에서 자주 사용되는 기술이다 보니 유명합니다. CASE 식을 집약 함수 내부에 포함시켜서 남성인구와 여성인구 필터를 만듭니다.

SELECT   prefecture
       , SUM( CASE WHEN sex='1' THEN pop ELSE 0 END) AS pop_men
       , SUM( CASE WHEN sex='2' THEN pop ELSE 0 END) AS pop_wom
FROM     Population
GROUP BY prefecture;

실행 계획

SELECT를 구문을 사용한 조건 분기의 경우 쿼리가 굉장히 간단합니다. 또한 외관 뿐만 아니라 성능도 향상됩니다. 실행 계획 자체도 굉장히 간단합니다. 테이블의 풀 스캔이 1회로 감소합니다. 이렇게 CASE 식으로 조건 분기를 잘 사용하면 UNION을 사용하지 않을 수 있습니다.

 

WHERE 구에서 조건 분기를 하는 사람은 초보자라고 하는데 HAVING 구에서 조건 ㅂㄴ기를 하는 사람도 초보자라는 것을 명심해주세요.