부제 : 집합의 세계

1. 여러 개의 레코드를 한 개의 레코드로 집약

id data_type data_1 data_2 data_3 data_4 data_5 data_6
Jim A 100 10 34 346 54  
Jim B 45 2 167 77 90 157
Jim C   3 687 1355 324 457
Ken A 78 5 724 457   1
Ken B 123 12 178 346 75 235
Ken C 45   23 46 687 33
Beth A 75 0 190 25 356  
Beth B 435 0 183   4 325
Beth C 96 128   0 0 12

 

* 해당 레코드에서 사용하고자 하는 데이터에 하이라이트 효과를 적용했다.

 

위의 테이블은 한 사람에 관련된 정보가 여러 레코드에 분산되어 있어 정보를 찾기 위해서는 여러 레코드에 접근해야 한다.

불필요한 과정이 생기기 때문에 이를 보완하기 위해 이 테이블을 아래와 같은 레이아웃의 테이블로 만드는 것이 목표이다.

id data_1 data_2 data_3 data_4 data_5 data_6
Jim 100 10 167 77 90 457
Ken 78 5 178 346 85 33
Beth 75 0 183   4 12

 

이렇게 집약된 테이블을 만들기 위해 어떻게 해야 할까?

(단순히 세 개의 쿼리를 만들어서 UNION 시키는 쿼리는 성능적으로 안티패턴이라는 것을 기억하자!)

1.1 CASE 식과 GROUP BY 응용

집약된 표를 보면 사람 단위로 데이터가 집약되어 있다 = GROUP BY 사용하기

여기서 놓치지 말아야 할 점은 GROUP BT 집약 키 를 사용할 때는 SELECT 구에 아래의 세 가지만 올 수 있다는 것이다.

① 상수

② GROUP BY 구에 사용한 집약 키

③ 집약 함수

SELECT id,
	MAX(CASE WHEN data_type = 'A' THEN data_1 ELSE NULL END) AS data_1,
	MAX(CASE WHEN data_type = 'A' THEN data_2 ELSE NULL END) AS data_2,
	MAX(CASE WHEN data_type = 'B' THEN data_3 ELSE NULL END) AS data_3,
	MAX(CASE WHEN data_type = 'B' THEN data_4 ELSE NULL END) AS data_4,
	MAX(CASE WHEN data_type = 'C' THEN data_5 ELSE NULL END) AS data_5,
	MAX(CASE WHEN data_type = 'C' THEN data_6 ELSE NULL END) AS data_6
FROM NonAggTb1
GROUP BY id;

그런고로 CASE문이 그대로 SELECT구에 들어갈 수 없기에 MAX 라는 집약 함수 내부에 넣어서 SELECT 구에 넣는다.

 

여기서 MAX는 CASE문으로 인해 만들어지는 하나의 요소를 선택하기 위해 사용하는 것이다.

즉, 내부 요소를 선택하기 위한 집계함수이므로 MAX, MIN, AVG, SUM 등의 함수를 사용해서 현 예제에서는 상관 없다.

 

1.2 집약, 해시, 정렬

이 쿼리의 실행계획을 살펴보면

NonAggTb1을 FULL SCAN 한 후 HASH GROUP BY 를 실행한다.

 

주목할 부분은 'HASH GROUP BY', 즉 집약 조작에 '해시' 알고리즘을 사용했다는 점이다.

집약할 때는 정렬을 쓰는 게 아니냐는 의문이 들 수 있지만 최근에는 GROUP BY 에서 HASH 알고리즘을 쓰는 경우가 많아졌다.

왜? 더 빠르니까!

 

더보기

HASH GROUP BY

GROUP BY 구에 지정되어 있는 필드를 해시 함수를 사용해 해시 키로 변환하고, 같은 해시 키를 가진 그룹을 모아 집약하는 방법이다.

해시의 성질상 GROUP BY의 유일성이 높으면 더 효율적으로 작동한다.

 

여기서 GROUP BY를 쓸 때의 주의점을 하나 짚고 넘어가자.

정렬과 해시는 모두 메모리를 많이 사용한다.

즉, 충분한 워킹 메모리가 확보되지 않으면 스왑이 발생하고, 이에 따라 저장소 위의 파일이 사용되므로 굉장히 느려진다.

 

오라클 기준으로 보면 오라클은 정렬 또는 해시를 위해 PGA 메모리 영역을 사용한다. 이때 PGA 크기가 충분하지 않으면 (집약 대상 데이터양에 비해 부족하면), 일시 영역 (저장소)를 사용해 부족한 만큼 채운다. 

이러한 현상을 TEMP 탈락이라고 부른다.

TEMP 탈락이 발생하면 극단적으로 성능이 저하되는데 이는 메모리와 저장소의 접근 속도 차이가 매우 많이 나기 때문이다.

최악의 경우 TEMP 영역을 모두 써서 SQL 구문이 비정상적으로 종료되는 경우가 발생할 수도 있다.

 

2. 합쳐서 하나

reserve_id(예약 ID) low_age(대상 연령의 하한) high_age(대상 연령의 상한) price(가격)
제품1 0 50 2000
제품1 51 100 3000
제품2 0 100 4200
제품3 0 20 500
제품3 31 70 800
제품3 71 100 1000
제품4 0 99 8900

제품의 대상 연령별 가격을 관리하는 테이블이 있다.

한 개의 제품에서 연령 범위가 반복되는 경우는 없다.

이 테이블에서 0~100세까지 모든 연령이 가지고 놀 수 있는 제품을 구해보자.

 

이 테이블을 보았을 때 1개의 레코드로는 전체를 커버하지 못하지만 여러 개의 레코드를 조합하면 커버가 가능하다. 

 

여러 개의 레코드로 한 개의 범위를 커버하는 코드는 다음과 같다.

SELECT product_id
FROM PriceByAge
GROUP BY product_id
HAVING SUM(high_age - low_age + 1) = 101;  -- 0~100 은 총 101개다

 

 

3. 자르기와 파티션

GROUP BY는 집약 관점으로 볼 수 있지만, 자르기 관점으로도 바라볼 수 있다. (두 가지 기능 한꺼번에 수행하는 연산)

name(이름) age(나이) height(키(cm)) weight(몸무게(kg))
Anderson 30 188 90
Adela 21 167 55
Bates 87 158 48
Becky 54 187 70
Bill 39 177 120
Chris 90 175 48
Darwin 12 160 55
Dawson 25 182 90
Donald 30 176 53

 

우선 이름 첫 글자를 사용해 특정 알파벳으로 시작하는 이름을 가진 사람이 몇 명인지 집계해보자.

SELECT SUBSTRING(name, 1, 1) AS label, COUNT(*)
FROM Persons
GROUP BY SUBSTRING(name, 1, 1);

 

3.1 파티션

GROUP BY 구로 잘라 만든 각각의 부분 집합을 수학적으로 파티션(partition)이라고 부른다.

같은 모집합에서도 파티션을 만드는 방법은 많은데 아래는 나이를 기준으로 만든 파티션 예시이다.

 

이렇게 구분을 하기 위해서는 GROUP BY 의 키를 3가지로 구분해야 한다.

CASE 식을 사용해서 SQL문을 작성해보자.

 

SELECT CASE WHEN age < 20 THEN '어린이'
	    WHEN age BETWEEN 20 AND 69 THEN '성인'
            WHEN age >= 70 THEN '노인'
            ELSE NULL END AS age_class,
       COUNT(*)
FROM Persons
GROUP BY CASE WHEN age < 20 THEN '어린이'
              WHEN age BETWEEN 20 AND 69 THEN '성인'
              WHEN age >= 70 THEN '노인'
              ELSE NULL END;

 

이렇게 자르기의 기준이 되는 키를 GROUP BY 구와 SELECT 구 모두에 입력하는 것이 포인트이다.

 

그렇다면 GROUP BY 구에서 CASE 문을 실행했을 때의 성능은 어떨까?

 

크게 나빠지거나 크게 좋아지지 않았음을 알 수 있다.

 

3.2 BMI로 자르기

BMI 지표를 사용해서 분류해보자.

더보기

BMI = W / T**2

이러한 기준으로 Persons 테이블의 사람들의 체중을 분류하고 몇 명이 해당되는지 알아보자.

 

이름 BMI 분류
Anderson 25.5 과체중
Adela 19.7 정상
Bates 19.2 정상
Becky 20 정상
Bill 38.3 과체중
Chris 15.7 저체중
Darwin 21.5 정상
Dawson 27.2 과체중
Donald 17.1 저체중

 

파티션을 나눠보자.

 

 

BMI 연산은 weight / POWER(height / 100, 2) 식을 사용해 구할 수 있다.

이렇게 구한 BMI를 CASE 식으로 구분해 분류하고 이를 GROUP BY 구와 SELECT 구에 모두 적어주면 된다.

 

SELECT CASE WHEN weight / POWER(height / 100, 2) < 18.5 THEN '저체중'
            WHEN 18.5 <= weight / POWER(height / 100, 2)
                 AND weight / POWER(height / 100, 2) < 25 THEN '정상'
            WHEN 25 <= weight / POWER(height / 100, 2) THEN '과체중'
            ELSE NULL END AS bmi,
       COUNT(*)
FROM Persons
GROUP BY CASE WHEN weight / POWER(height / 100, 2) < 18.5 THEN '저체중'
              WHEN 18.5 <= weight / POWER(height / 100, 2)
                   AND weight / POWER(height / 100, 2) < 25 THEN '정상'
              WHEN 25 <= weight / POWER(height / 100, 2) THEN '과체중'
              ELSE NULL END;

 

이 경우의 실행 계획도 그림 4-11 의 실행계획과 동일하다.

 

GROUP BY 구에는 필드 이름만 키로 적을 수 있는 것이 아닌, 복잡한 수식을 기준으로도 자를 수 있음을 꼭 기억하자!!

 

4. PARTITION BY 구를 사용한 자르기

GROUP BY 구에서 집약 기능을 제외하고 자르는 기능만 남긴 것이 위도우 함수의 PARTITION BY 구이다.

집약이라는 기능을 제외하면 이 두 구의 실질적인 기능의 차이는 없다.

즉, PARTITION BY 구 또한 단순한 필드 이름 뿐만 아니라 CASE 식, 복잡한 계산 식을 사용한 기준을 사용할 수 있다.

 

위에서 보았던 연령 범위 테이블에 PARTITION BY 구를 사용해 자르기를 해보자.

같은 연령 등급(어린이, 성인, 노인)에서 어린 순서로 순위를 매기는 코드를 작성해보자.

 

SELECT name,
       age,
       CASE WHEN age < 20 THEN '어린이'
            WHEN age BETWEEN 20 AND 69 THEN '성인'
            WHEN age = 70 THEN '노인'
            ELSE NULL END AS age_class,
       RANK() OVER(PARTITION BY  CASE WHEN age < 20 THEN '어린이'
                                      WHEN age BETWEEN 20 AND 69 THEN '성인'
                                      WHEN age = 70 THEN '노인'
                                      ELSE NULL END
                   ORDER BY age) AS age_rank_in_class
FROM Persons
ORDER BY age_class, age_rank_in_class;

 

 

PARTITION BY 구는 GROUP BY 구와 달리 집약 기능이 없고, 입력에 정보를 추가할 뿐이므로 원래 Persons 테이블 정보를 완전히 그대로 유지한다.

 

5. 마치며

  • GROUP BY 구와 PARTITION BY 구는 단순히 필드명만을 키값으로 가지는 것이 아니라 복잡한 계산 및 수식을 기준으로 가져갈 수 있다.
  • GROUP BY 구는 자르기, 집약 기능을 한꺼번에 수행하는 연산이다.
  • GROUP BY 구로 잘라 만든 각각의 부분 집합을 PARTITION 이라 한다.
  • GROUP BY 구에서 집약 기능만 뺀 것이 윈도우 함수의 PARTITION BY 구이다.

 

 

'DATABASE > 📖 SQL 레벨업' 카테고리의 다른 글

[ SQL 레벨업 ] 7장. 서브쿼리  (0) 2024.12.12

티스토리툴바