집약

• COUNT
• SUM
• AVG
• MAX
• MIN

여러 개의 레코드를 한 개의 레코드로

아래와 같은 테이블이 있다고 가정

아래 테이블은 data_type마다 사용하는 data_n가 다르다

A: data_1~data_2

B: data_3~data_5

C: data_6

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
Ken	C		3	5	724	457
Ken	A	45		23	46	687	33

data가 너무 흩어져 있어, 한 사람의 정보가 같은 레코드에 들어있을 수 있도록 집약 테이블로 변환해야 한다.

CASE 식과 GROUB BY 응용

아래 쿼리는 오류가 발생한다.

GROUP BY구로 집약했을 때, SELECT 구에 입력할 수 있는 것

• 상수
• GROUP BY 구에서 사용한 집약 키
• 집약 함수

하지만 data_n은 그 어떤 것에도 해당되지 않는다.

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

오류 해결 쿼리

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 = 'B' 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 NonAggTbl
GROUP BY id;

• GROUP BY로 데이터를 자를 땐, 각 집합에 3개의 레코드 존재
• 만약, 집합 함수인 MAX가 적용되면 내부에 있는 하나의 요소 선택

집약, 해시, 정렬

아래는 위 쿼리문의 실행 계획이다.

이때 주목할 점은 GROUP BY 집약 조작에 ‘해시’를 사용하고 있다는 것

QUERY PLAN                             
--------------------------------------------------------------------
 HashAggregate  (cost=34.22..36.22 rows=200 width=106)
   Group Key: id
   ->  Seq Scan on nonaggtbl  (cost=0.00..15.70 rows=570 width=114)

최근에는 GROUP BY에서 정렬보다 해시를 사용하는 경우가 많다. (정렬 속도 < 해시 속도)

• GROUP BY에 지정되어 있는 키를 해시 키로 변환
• 같은 해시 키를 가진 그룹을 모아 집약

GROUP BY 성능 문제

아무래도 GROUP BY에서 정렬 또는 해시를 사용하므로, 워킹 메모리를 사용하게 된다.

TEMP 탈락

이때 앞에서 배운 것처럼 워킹 메모리는 용량이 작아, 워킹 메모리가 부족한 경우 다른 임시 저장소(디스크)를 이용해 스왑이 발생하게 되고, 이때 스왑이 발생한다면 속도가 굉장히 느려진다.

최악의 경우, 주어진 저장소를 다 사용하고도 쿼리를 모두 실행하지 못해 비정상적으로 종료되는 경우가 발생한다.

⇒ 따라서 실제 환경과 유사한 환경에서 어떻게 실행되는 지 부하 검증을 실행해야 한다.

합쳐서 하나

아래와 같은 테이블이 있다고 가정

이때 이 테이블은 같은 제품이라도 나이 제한이 다르면 다른 레코드이다.

따라서 레코드는 (product_id, low_age)의 키로 구별할 수 있다.

product_id	low_age	high_age	price
제품1	0	50	2000
제품1	51	100	3000
제품2	0	100	4200

제품 중 0~100세까지 모든 연령이 가지고 놀 수 있는 제품을 구하자.

1. 일단 집약 단위가 제품이므로, 집약 키는 제품 ID
2. 그리고 각 범위의 합이 101이 되는 제품을 선택하기

SELECT product_id
FROM PriceByAge
GROUP BY product_id
HAVING SUM(high_age - low_age + 1) = 101;

자르기

자르기 & 파티션

아래와 같은 테이블이 있다고 가정

name	age	height	weight
Anderson	30	188	90
Adela	21	167	55
Bates	87	158	48

GROUP BY는 파티션을 만들 수 있다. 이번엔 나이를 기준으로 어린이(20세 미만), 성인(20~69세), 노인(70세 이상)으로 나눠보자.

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 구 모두에 입력

QUERY PLAN                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=34.75..38.75 rows=200 width=40)
   Group Key: CASE WHEN (age < 20) THEN '어린이'::text WHEN ((age >= 20) AND (age <= 69)) THEN '성인'::text WHEN (age >= 70) THEN '노인'::text ELSE NULL::text END
   ->  Seq Scan on persons  (cost=0.00..29.80 rows=990 width=32)

• GROUP BY 구에서 CASE 식을 사용해도 실행 계획에는 영향이 없다.
• 이처럼 필드에 연산을 추가한 식을 GROUP BY 키로 한다면 CPU 연산에 오버헤드 가능성 O → 이는 데이터를 뽑아온 뒤의 이야기이므로, 데이터 접근 경로에는 영향 X

PARTITION BY 이용한 자르기

GROUP BY와 다르게 집약 기능이 없고 자르기만 가능한 윈도우 함수, PARTITION BY

PATITION 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;