집계와 조건분기

집계 대상으로 조건 분기

-테이블 생성

CREATE TABLE Population(
	 prefecture VARCHAR(32),
 	 sex        CHAR(1),
	 pop        INTEGER,
     CONSTRAINT pk_pop PRIMARY KEY(prefecture, sex)
);

-데이터 insert

INSERT INTO Population VALUES('성남', '1', 60);
INSERT INTO Population VALUES('성남', '2', 40);
INSERT INTO Population VALUES('수원', '1', 90);
INSERT INTO Population VALUES('수원', '2',100);
INSERT INTO Population VALUES('광명', '1',100);
INSERT INTO Population VALUES('광명', '2', 50);
INSERT INTO Population VALUES('일산', '1',100);
INSERT INTO Population VALUES('일산', '2',100);
INSERT INTO Population VALUES('용인', '1', 20);
INSERT INTO Population VALUES('용인', '2',200);

 

 -UNION을 사용한 방법

   절차지향적 사고방식을 가진다면 남성의 인구를 지역별로 구하고 여성의 인구를 지역별로 구한뒤 머지하는 방법을 생각

SELECT   perfecture
	   , SUM(pop_men) AS pop_men
       , SUM(pop_mom) AS pop_wom
  FROM (
		SELECT perfecture
        	 , pop AS pop_men
             , null AS pop_mom
		  FROM population
		 WHERE sex ='1'
		 UNION
		SELECT perfecture
        	 , null AS pop_men
             , pop AS pop_mom
		  FROM population
		 WHERE sex ='2'
        ) tmp
  GROUP BY perfecture;

-결과

남성과 여성의 인구 쿼리 결과

-실행계획

UNION 쿼리 실행 계획

FULL SCAN이 2번 타게 됨.

 

-CASE식을 사용하여 집계

   난잡한 쿼리

SELECT 
	  tmp.perfecture
    , SUM(pop_men)
    , SUM(pop_wom) 
 FROM (
		SELECT 
         	  perfecture
       		, CASE           
           		WHEN sex = '1'
           		THEN SUM(pop) 
         	  END AS pop_men
      		, CASE
            	WHEN sex = '2'
            	THEN SUM(pop)
        	 END AS pop_wom        
		 FROM population
		 GROUP BY perfecture, sex) tmp
 GROUP BY tmp.perfecture;

  조금더 간결하게

SELECT 
         perfecture
       , 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 perfecture

실행계획을 보면 아래 그림처럼 한번만 테이블을 사용하여 비용이 감소한다.

 

집약 결과로 조건 분기

-테이블 생성

CREATE TABLE Employees
(emp_id    CHAR(3)  NOT NULL,
 team_id   INTEGER  NOT NULL,
 emp_name  CHAR(16) NOT NULL,
 team      CHAR(16) NOT NULL,
    PRIMARY KEY(emp_id, team_id));

-데이터 insert

INSERT INTO Employees VALUES('201', 1, 'Joe', '상품기획');
INSERT INTO Employees VALUES('201', 2, 'Joe', '개발');
INSERT INTO Employees VALUES('201', 3, 'Joe', '영업');
INSERT INTO Employees VALUES('202', 2, 'Jim', '개발');
INSERT INTO Employees VALUES('203', 3, 'Carl', '영업');
INSERT INTO Employees VALUES('204', 1, 'Bree', '상품기획');
INSERT INTO Employees VALUES('204', 2, 'Bree', '개발');
INSERT INTO Employees VALUES('204', 3, 'Bree', '영업');
INSERT INTO Employees VALUES('204', 4, 'Bree', '관리');
INSERT INTO Employees VALUES('205', 1, 'Kim', '상품기획');
INSERT INTO Employees VALUES('205', 2, 'Kim', '개발');

1. 소속된 팀이 1개라면 해당 직원은 팀의 이름을 그대로 노출
2. 소속된 팀이 2개라면 해당 직원은 2개를 겸무 라는 문자열 출력
3. 소속된 팀이 3개라면 해당 직원은 3개이상을 겸무 라는 문자열 출력

 

조건을 만족하는 결과

-UNION 사용

SELECT 
	  emp_name
    , MIN(team) AS team
 FROM employees
GROUP BY emp_name
HAVING COUNT(team_id) = 1
UNION 
SELECT 
	  emp_name
    , '2개를 겸무' AS team
 FROM employees
GROUP BY emp_name
HAVING COUNT(team_id) = 2
UNION 
SELECT 
	  emp_name
    , '3개이상을 겸무' AS team
 FROM employees
GROUP BY emp_name
HAVING count(team_id) >= 3

-실행계획

 테이블 접근이 3회 발생

-CASE 식 이용

 

SELECT emp_name
      , CASE
         WHEN COUNT(team_id) = 1
         THEN MIN(team)
         WHEN COUNT(team_id) = 2
         THEN '2개를 겸무'
         WHEN COUNT(team_id) >= 3
         THEN '3개이상을 겸무'
       END AS team  
  FROM employees
  GROUP BY emp_name;

 

---

그래도 UNION이 필요한 경우

 UNION을 사용할 수 밖에 없는경우

  - SELECT구문에서 여러 테이블을 사용하는경우 

  - CASE도 사용할 수 있지만 실행 계획을 가지고 명확하게 확인 해서 사용

SELECT col_1
  FROM Table_A
 WHERE col_2 = 'A'
UNION ALL
SELECT col_3
  FROM Table_B
  WHERE col_4 = 'B'

UNION을 사용하는 것이 성능적으로 더 좋은경우

 

CREATE TABLE ThreeElements
(key    CHAR(8),
 name   VARCHAR(32),
 date_1 DATE,
 flg_1  CHAR(1),
 date_2 DATE,
 flg_2  CHAR(1),
 date_3 DATE,
 flg_3  CHAR(1),
    PRIMARY KEY(key));

    INSERT INTO ThreeElements VALUES ('1', 'a', '2013-11-01', 'T', NULL, NULL, NULL, NULL);
INSERT INTO ThreeElements VALUES ('2', 'b', NULL, NULL, '2013-11-01', 'T', NULL, NULL);
INSERT INTO ThreeElements VALUES ('3', 'c', NULL, NULL, '2013-11-01', 'F', NULL, NULL);
INSERT INTO ThreeElements VALUES ('4', 'd', NULL, NULL, '2013-12-30', 'T', NULL, NULL);
INSERT INTO ThreeElements VALUES ('5', 'e', NULL, NULL, NULL, NULL, '2013-11-01', 'T');
INSERT INTO ThreeElements VALUES ('6', 'f', NULL, NULL, NULL, NULL, '2013-12-01', 'F');

CREATE INDEX IDX_1 ON ThreeElements (date_1, flg_1) ;
CREATE INDEX IDX_2 ON ThreeElements (date_2, flg_2) ;
CREATE INDEX IDX_3 ON ThreeElements (date_3, flg_3) ;

전체 결과

-날짜가 2013-11-01이고 플래그가 T인 레코드

날짜가 2013-11-01이고 플래그가 T인 레코드

-UNION 사용

select * from 
threeelements
where date_1 = '2013-11-01'
 and flg_1 = 'T'
union 
select * from 
threeelements
where date_2 = '2013-11-01'
  and flg_2 = 'T'
union
select * from 
threeelements
where date_3 = '2013-11-01'
  and flg_3 = 'T'

 

-OR 사용

select * from 
    threeelements
    where ( date_1 = '2013-11-01' and flg_1 = 'T')
        or (date_2 = '2013-11-01' and flg_2 = 'T')
        or (date_3 = '2013-11-01' and flg_3 = 'T');

WHERE 구문에서 OR를 사용하면 인덱스를 사용할 수 없음

 

 

-IN을 사용

   -실행 계획은 OR를 사용할때랑 같음

  select * from threeelements
  where ('2013-11-01', 'T')
      in ((date_1, flg_1)
         ,(date_2, flg_2)
         ,(date_3, flg_3)
         );

-CASE를 사용

   - OR와 IN을 사용할 때랑 같음 

  select * from threeelements
     where case 
            when date_1 = '2013-11-01' then flg_1
            when date_2 = '2013-11-01' then flg_2
            when date_3 = '2013-11-01' then flg_3
            else null
          end ='T';

 - CASE문은 아래와 같은 데이터가 들어가면 결과가 달라지게 됨

INSERT INTO ThreeElements VALUES ('7', 'g', '2013-11-01', 'F', NULL, NULL, '2013-11-01', 'T');

 - 왜냐 하면 UNION이나 OR같은 구문은 조건의 컬럼들을 모두 평가 하기 때문에 정상적으로 나오지만 CASE문은 

   WHEN구가 단락 평가를 수행하기 때문에 앞에 있는 조건이 TRUE라면 평가를 중단하고 나머지 분기의 평가를 생략

 

출처

미크 저, 윤인성 옮김

DB 성능 최적화를 위한 SQL 실전 가이드: http://www.hanbit.co.kr/store/books/look.php?p_code=B4250257160