[SQL]조건 분기 - 집계와 조건 분기

작성일자 : 2023-09-19

Ver 0.1.1

집계를 수행하는 쿼리를 작성할 때, 쓸데없이 길어지는 경우를 자주 볼 수 있다. 아래의 예시 테이블을 살펴보자.

 

(인구 테이블)

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

(원하는 결과)

Perfecture	Pop_mem	Pop_wom
수원	90	100
일산	100	100
성남	60	40
광명	100	50
용인	20	200

1. 집계 대상으로 조건 분기

   1) UNION을 사용한 방법

 

이 문제를 풀 때 절차 지향적인 사고방식으로 접근한다면, 일단 남성과 여성 인구를 지역별로 구한뒤 merge 하는 방법을 생각할 수 있다.

SELECT perfecture, SUM(pop_mem) AS pop_men, SUM(pop_wom) AS pop_wom
  FROM (SELECT perfecture, pop AS pop_men, null AS pop_wom
            FROM Population
         WHERE sex = '1' --남성
         UNION
        SELECT perfecture, pop AS pop_men, null AS pop_wom
            FROM Population
         WHERE sex = '2') TMP --여성
 GROUP BY prefecture;

위 쿼리의 실행 계획을 보면, population 테이블에 풀 스캔이 2회 수행된다.

     

     2) 집계의 조건 분기도 CASE 식을 사용

 

아래 방법은 CASE 식의 응용 방법으로 굉장히 유명한 표측/표두 레이아웃 이동 문제이다. 원래는 SQL은 이러한 결과 포맷팅을 목적으로 만들어진 언어는 아니지만, 실무에서 자주 사용되는 기술

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;

위 쿼리는 외관이 간단해질 뿐만 아니라 실행 계획도 Population 테이블의 풀 스캔 1회로 감소하여 성능도 향상된다.

따라서 UNION을 사용하여 2회 풀 스캔 하는 것에 비해 (캐시 등을 고려하지 않는다면) I/O 비용이 절반으로 감소한다는 의미이다.

---

2.집약 결과로 조건 분기

집약에 조건 분기를 적용하는 또 하나의 패턴으로, 집약 결과에 조건 분기를 수행하는 경우가 있다.

(직원 테이블)

emp_id(직원ID)	team_id(팀 ID)	emp_name(직원 이름)	team(팀)
201	1	Joe	상품기획
201	2	Joe	개발
201	3	Joe	영업
202	2	Jim	개발
203	3	Carl	영업
204	1	Bree	상품기획
204	2	Bree	개발
204	3	Bree	영업
204	4	Bree	관리
205	1	Kim	상품기획
205	2	Kim	개발

(조건을 만족하는 결과)

emp_name	team
Jim	개발
Bree	3개 이상을 겸무
Joe	3개 이상을 겸무
Carl	영업
Kim	2개를 겸무

     1) UNION을 사용한 조건 분기

SELECT emp_name
      ,MAX(team) AS team
  FROM Employees
 GROUP BY emp_nam
HAVING COUNT(*) = 1
UNION
SELECT emp_name
      ,'2개를 겸무' AS team
  FROM Employees
 GROUP BY emp_nam
HAVING COUNT(*) = 2
UNION
SELECT emp_name
      ,'3개 이상을 겸무' AS team
  FROM Employees
 GROUP BY emp_nam
HAVING COUNT(*) >= 3;

위 쿼리의 실행 계획을 보면 3개의 쿼리를 머지하는 쿼리이므로 Employee 테이블에 대한 접근도 3번 발생한다.

 

     2) CASE 식을 사용한 조건 분기

 

이 문제를 푸는 최적의 방법은 CASE 식을 사용하는 것이다.

SELECT emp_name
      ,CASE WHEN COUNT(*)  = 1 THEN MAX(team)
            WHEN COUNT(*)  = 2 THEN '2개를 겸무'
            WHEN COUNT(*) >= 3 THEN '3개를 겸무'
       END AS team
  FROM Employees
 GROUP BY emp_name;

위처럼 CASE 식을 사용하면 테이블에 대한 접근 비용을 3분의 1로 줄일 수 있다. 추가적으로 GROUP BY의 HASH 연산도 3회에서 1회로 줄었다. WHERE 구에서 조건 분기를 하는 사람은 초보자라고 언급했던 것과 마찬가지로 HAVING 구에서 조건분기를 하는 사람도 초보자이다.