12강 - 집약
표준 SQL에 있는 집약 함수(aggregate function)는 총 5가지로, 여러 개의 레코드를 한 개의 레코드로 집약하는 기능이 있다.
•
COUNT, SUM, AVG, MAX, MIN
여러 개의 레코드를 한 개로 집약 - GROUP BY
위의 테이블에서 한 사람에 대한 데이터를 한 개의 레코드로 만들어보자.
/* CASE */
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 04_NonAggTbl
GROUP BY `id`;
/* IF */
SELECT `id`,
MAX(IF(data_type = 'A', data_1, NULL)) AS data_1,
MAX(IF(data_type = 'A', data_2, NULL)) AS data_2,
MAX(IF(data_type = 'B', data_3, NULL)) AS data_3,
MAX(IF(data_type = 'B', data_4, NULL)) AS data_4,
MAX(IF(data_type = 'B', data_5, NULL)) AS data_5,
MAX(IF(data_type = 'C', data_6, NULL)) AS data_6
FROM 04_NonAggTbl
GROUP BY `id`;
SQL
복사
최근에는 GROUP BY를 사용하는 집약에서 정렬보다 더 빠른 해시를 사용하는 경우가 많다.
해시 방식은 GROUP BY 구에 지정되어 있는 필드를 해시 함수를 사용해 해시 키로 변환하고, 같은 해시 키를 가진 그룹을 모아 집약하는 방식이다. 해시 특성상 GROUP BY의 유일성이 높으면 더 효율적으로 작동한다.
정렬 또는 해시를 위해 사용되는 메모리 영역(PGA(Oracle), 워크버퍼(PostgresSQL), 정렬버퍼(MySQL))의 크기가 집약 대상 데이터양에 비해 부족하면, 일시 영역(저장소)을 사용해 부족한 만큼 채운다. 이를 TEMP 탈락이 발생했다고 한다. TEMP 탈락이 발생하면 성능이 악화될 뿐 아니라, 최악의 경우 TEMP 영역을 모두 써버려 SQL 구문이 비정상적으로 종료될 수 있다.
연산 대상 레코드 수가 많은 집약 함수를 사용하는 SQL에서는 충분한 성능 검증을 실행해줘야 한다.
13강 - 자르기
GROUP BY 구는 원래 모집합인 테이블을 작은 부분 집합들로 분리하는 자르기 기능을 집약과 함께 한 번에 수행한다.
파티션
GROUP BY 구로 잘라 만든 각각의 부분 집합을 파티션이라 한다. 파티션은 서로 중복되는 요소를 가지지 않는다.
GROUP BY 구를 사용한 자르기
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 04_Persons
GROUP BY CASE
WHEN age < 20 THEN '어린이'
WHEN age BETWEEN 20 AND 69 THEN '성인'
WHEN age >= 70 THEN '노인'
ELSE NULL END;
SQL
복사
위에서 봤던 실행 계획과 큰 차이가 없다. GROUP BY 구에서 CASE 식 또는 함수를 사용해도 실행 계획에는 영향이 없다.
집약 함수와 GROUP BY의 실행 계획은 성능적인 측면에서, 해시 또는 정렬에 사용되는 워킹 메모리의 용량에 주의하자.
PARTITION BY 구를 사용한 자르기
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 04_Persons
ORDER BY age_class, age_rank_in_class;
SQL
복사
집약을 수행하는 GROUP BY 구와 달리, PARTITION BY 구는 입력에 정보를 추가하는 것이므로 원본 테이블 정보를 그대로 유지한다.