print('yongineer')

집약과 자르기

November 28, 2020

sql

집약

SQL의 집약 함수(aggregate function)는 다음과 같다.

  • COUNT
  • SUM
  • AVG
  • MAX
  • MIN

이 함수들은 여러 개의 레코드를 한 개의 레코드로 집약하는 기능을 갖고 있다.

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

만약 다음과 같은 테이블이 있다고 가정해 보자

https://yongineer.duckdns.org/sql/074.png

위 테이블은 CSV파일과 같은 형식의 플랫 파일을 그대로 테이블에 붙인 형태의 유사 배열 테이블이다. 사람을 관리하는 id필드와 데이터를 종류별로 관리하는 data_type필드를 기본키로 한다. 보통 이름을 키로 하는 것은 좋지 않지만 지금은 예제이므로 넘어간다. data_1 ~ data_6필드는 사람에 대해서 무언가를 나타내는 정보이다. data_type필드가 A라면 data_1 ~ data_2, B라면 data_3 ~ data_5, C라면 data_6을 사용한다.

이런 비집약 테이블 처럼 한 사람과 관련된 정보가 여러 개의 레코드에 분산되어 있는 테이블은, 한 사람의 정보에 접근할 때 WHERE id = 'Jim'과 같은 SELECT구문을 사용할 때 당연히 3개의 레코드가 선택된다. 하지만 이런 데이터를 처리하는 애플리케이션이라면 한 사람에 대한 데이터는 한 개의 레코드로 얻는 것이 편할 것이다.

또한 특정 처리에서 필요한 정보를 얻고 싶은 경우에도 이런 테이블은 문제가 많다. 예를 들어 어떤 작업 A~C에 사용하는 data_type의 데이터가 필요하다면 아래와 같은 3개의 쿼리를 사용해야한다.

# 작업 A에 관한 쿼리
SELECT id, data_1, data_2 From NonAggTbl
	WHERE id = 'Jim' AND data_type = 'A';

# 작업 B에 관한 쿼리
SELECT id, data_3, data_4, data_5 From NonAggTbl
	WHERE id = 'Jim' AND data_type = 'B';

# 작업 C에 관한 쿼리
SELECT id, data_6 From NonAggTbl
	WHERE id = 'Jim' AND data_type = 'C';

또한 이런 쿼리는 모두 필드의 수가 다르기 때문에 UNION으로 집약하는 것도 불가능하며 게다가 UNION으로 쿼리를 머지하는 것은 성능적으로 안티패턴이다.

사실 이런 데이터는 아래와 같은 레이아웃의 테이블로 만드는 것이 바람직 하다.

https://yongineer.duckdns.org/sql/075.png

집약되어 있는 테이블을 보면 한 사람의 정보가 모드 같은 레코드에 들어있다. 따라서 한 사람의 정보를 얻을 때 쿼리 하나면 충분하다.

CASE식과 GROUP BY응용

일단 사람 단위로 집약하므로 GROUP BY의 집약 키는 사람의 식별지인 id필드를 기준으로 한다. 이어서 CASE식을 사용하여 선택할 필드를 data_type필드로 분기한다.

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;

다만 이 쿼리는 문법 오류가 발생한다. (단, 최신 MySQL에서는 이런 코드를 입력할 수 있게 기능을 확장했다. 하지만 표준이 아니므로 사용을 자제하는 것이 좋다.)

https://yongineer.duckdns.org/sql/076.png

GROUP BY구로 집약했을 때 SELECT구에 입력할 수 있는 것은 다음과 같은 세 가지 뿐이다.

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

현재 테이블을 id 필드로 그룹화하고 CASE 식에 data_type를 지정하면 하나의 레코드만 선택된다. 따라서 집약 함수를 사용하지 않고 data_1 ~ data_6필드를 그냥 입력해도 데이터베이스 엔진이 새로운 레코드를 만들어 낼 수 있을 것이다.

하지만 이러한 발상은 집합과 요소를 혼동한 것으로 SQL의 원리 (집합론의 원리)를 위배하는 것이다. 따라서 귀찮더라도 집약 함수를 사용해서 다음과 같이 작성해야 한다.

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개의 요소가 있다. 그런데 여기에 집약 함수가 적용되면 NULL을 제외하고 하나의 요소만 있는 집합이 만들어 진다. 여기에 MAX함수를 사용하면 내부에 있는 하나의 요소를 선택할 수 있다. 따라서 MIN, AVG, SUM등의 함수를 사용해도 현재 예제 에서는 상관없다 data_1 ~ data_6이 숫자이므로 AVG, SUM도 사용할 수 잇지만 문자 또는 날짜 등도 마찬가지의 방법으로 통일해서 사용하려면 MAX 또는 MIN을 사용하는 습관을 들이는 것이 좋다.

집약, 해시, 정렬

그럼 이런 집약 쿼리의 실행 계획은 어떻게 될까?

https://yongineer.duckdns.org/sql/077.png

먼저 PostgreSQL과 Oracle의 실행계획을 보면 GROUP BY의 집약 조작에서 모두 Hash알고리즘을 사용하고 있다는 것을 확인 할 수 있다. 경우에 따라서는 정렬을 사용하기도 하는데 이때는 SORT GROUP BY와 같은 실행 계획이 나타난다.

해시 알고리즘은 GROUP BY구에 지정되어 있는 필드를 해시 함수를 사용해서 해시키로 변환하고, 같은 해시키를 가진 그룹을 모아 집약하는 방법이다. 정렬을 사용한 방법 보다 빠르므로 많이 사용되고 있다. 특히 해시의 성질상 GROUP BY의 유일성이 높으면 더 효율적으로 작동한다.

다만 정렬과 해시 모두 메모리를 많이 사용하므로 충분한 정렬 및 해시 워킹 메모리가 확보되지 않으면 스왑이 발생한다. 따라서 저장소 위의 파일이 사용되면서 굉장히 느려진다. 워킹 메모리의 크기가 집약 대상 데이터양에 비해 부족하면, 일시 영역 (저장소)를 사용해 부족한 만큼 채우는데 이를 TEMP 탈락 이라고 부른다. TEMP 탈락이 발생하면 메모리 만으로 처리가 끝나는 경우와 비교해서 극단적으로 성능이 떨어진게 된다. 이는 메모리와 저장소의 접근 속도 차이가 굉장히 많이 나기 때문이다. 따라서 연상 대상 레코드 수가 많은 GROUP BY구 (또는 집약 함수)를 사용하는 SQL에서는 충분한 성능 검증(특히 실제 환경에서 어떻게 되는지 부하 검증)을 실행해줘야 한다. TEMP 탈락으로 인해 최악의 경우 TEMP 영역을 모두 써버려 SQL 구문이 비정상적으로 종료되는 경우가 발생 할 수도 있다.

https://yongineer.duckdns.org/sql/078.png

한편 MySQL에서는 그룹화 시 해시 알고리즘을 지원하지 않는 대신 INDEX를 사용하여 효율적으로 GROUP BY한다. 이때 반드시 GROUP BY 기준 필드는 인덱싱 되어 있어야 하며 만약 인덱스가 되어 있지 않은 테이블을 GROUP BY 할 경우 임시 테이블을 생성하게 되는데 이는 성능적으로 안티패턴이다.

https://yongineer.duckdns.org/sql/079.png

임시 테이블 생성을 확인하는 방법은 실행 계획에서 Extra 필드에 Using temporary라고 나오게 된다면 임시테이블을 생성하고 있다는 것이다. 이 외에도 다른 데이터베이스와 마찬가지로 정렬을 지원하는데 이때는 Using filesort라고 나오게 된다. 물론 이 둘을 모두 사용한다고 나오는 경우도 존재한다.

2. 합쳐서 하나

https://yongineer.duckdns.org/sql/080.png

다음과 같이 제품의 대상 연령별 가격을 관리하는 테이블이 있다고 가정해보자 여기서 0~100세까지 모든 연령이 가지고 놀 수 있는 제품을 구하려고 한다.

https://yongineer.duckdns.org/sql/081.png

제품1의 경우 2개의 레코드를 사용해서 0~100까지의 정수 범위 전체를 커버할 수 있다. 따라서 제품1 은 조건을 만족한다. 반면 제품3은 3개의 레코드를 사용하고 있음에도 21~30세까지의 범위가 없다. 따라서 제품3은 조건을 만족하지 못한다. 이렇게 1개의 레코드로 전체를 커버하지 못해도 여러 개의 레코드를 조합해 커버할 수 있다면 ‘합쳐서 하나’라고 하는 것이 문제의 주제이다.

일단 집약 단위가 제품이므로 집약 키는 제품 ID로 한다. 이어서 각 범위에 있는 상수 개수를 모두 더한 합계가 101인 제품을 선택하면 된다.

# 여러 개의 레코드로 한 개의 범위를 커버

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

https://yongineer.duckdns.org/sql/082.png

HAVING구의 high_age - low_age + 1로 각 레코드의 연령 범위에 있는 정수 개수를 구한다. 그리고 같은 제품을 모아 이 개수를 합하는 코드이다. 이는 확장하면 날짜 또는 시각에도 적용할 수 있다.

https://yongineer.duckdns.org/sql/083.png

이 테이블에서 사람들이 숙박한 날이 10일 이상인 방을 선택한다. 숙박한 날의 수는 도착일이 2월 1일 출발일이 2월 6일이라면 5박이므로 5일이다.

SELECT room_nbr, SUM(end_date - start_date) AS working_days
	FROM hotelrooms
		GROUP BY room_nbr
		HAVING SUM(end_date - start_date) >= 10;

https://yongineer.duckdns.org/sql/084.png

자르기

GROUP BY구는 집약 이외에도 한 가지 중요한 기능이 있다. 바로 ‘자르기’라는 기능이다. 이는 원래 모집합인 테이블을 작은 부분 집합들로 분리하는 것이다. 따라서 GROUP BY구라는 것은 다음과 같은 기능을 한꺼번에 수행하는 연산이다.

  • 자르기
  • 집약

한 개의 구에 두개의 연산이 들어있다는 것은 GROUP BY구에 대한 이해를 막는 원인이 된다.

1. 자르기와 파티션

https://yongineer.duckdns.org/sql/085.png

다음과 같이 개인 신체정보를 저장하고 있는 테이블있다고 가정해보자. 이때 이름 첫 글자를 사용해 특정한 알파벳으로 시작하는 이름을 가진 사람이 몇 명인지 집계하려고 한다.

집합의 요소 수를 구할 때는 당연히 COUNT를 사용한다 name필드는 기본키이므로 NULL인 경우를 따로 생각할 필요가 없다 (기본 키를 구성하는 필드는 NULL일수 없다.) 이어서 앞 글자를 GROUP BY구의 키로 지정하면 자르기가 된다.

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

https://yongineer.duckdns.org/sql/086.png

파티션

이렇게 GROUP BY구로 잘라 만든 하나하나의 부분 집합을 수학적으로는 ‘파티션 (partition)‘이라 한다. 파티션은 서로 중복되는 요소를 가지지 않는 부분집합이다. 같은 모집합이라도 파티션을 만드는 방법은 굉장히 많다.

예를들어 나이를 기준으로 어린이(20세 미만), 성인(20~69세), 노인(70세 이상)으로 나눈다면 다음과 같을 것이다.

https://yongineer.duckdns.org/sql/087.png

이런 구분을 하려면 CASE식을 이용하여 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 persons
		GROUP BY CASE WHEN age < 20 THEN '어린이'
									WHEN age BETWEEN 20 AND 69 THEN '성인'
									WHEN age >= 70 THEN '노인'
									ELSE NULL END;

https://yongineer.duckdns.org/sql/088.png

자르기의 기준이 되는 키를 GROUP BY구와 SELECT 구 모두에 입력하는 것이 포인트이다. PostgreSQL과 MySQL에서는 SELECT 구에 붙인 ‘age_class’라는 별칭을 사용해 GROUP BY age_class처럼 단순하게 작성할수 있다. 다만 표준에는 없는 내용이므로 사용에 주의가 필요하다.

# 나이로 자르기 : PostgreSQL, MySQL

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 age_class

그렇다면 GROUP BY구에서 CASE식을 사용하면 실행 계획은 어떻게 될까?

https://yongineer.duckdns.org/sql/089.png

앞선 실행계획과 비교해도 딱히 차이가 없다. GROUP BY구에서 CASE식 또는 함수를 사용해도 실행 계획에는 영향이 없다. 물론 단순한 필드가 아니라 필드에 연산을 추가한 식을 GROUP BY구의 키로 한다면 어느 정도 CPU연산에 오버헤드가 걸릴 것이다. 하지만 이는 데이터를 뽑아온 뒤의 이야기이므로 데이터 접근 경로에는 영향을 주지 않는다.

사실 집약 함수와 GROUP BY의 실행 계획은 성능적인 측면에서 해시(또는 정렬)에 사용되는 워킹 메모리의 용량에 주의하라는 것 이외에 따로 할말이 없다.

BMI로 자르기

BMI는 키를 tt(m 단위), 몸무게를 tt(kg 단위)라고 했을때 다음과 같은 방법으로 구한다. w/t2w/t^2 이때 키가 cm 단위가 아니라 m 단위라는 것에 주의가 필요하다. BMI수치가 18.5 미만을 저체중, 18.5 이상 25 미만을 정상, 25이상을 과체중으로 한다. 이러한 기준을 바탕으로 persons테이블의 사람들의 체중을 분류하고 몇 명이 해당되는지 구하려고 한다.

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;

https://yongineer.duckdns.org/sql/090.png

GROUP BY구에는 필드 이름 뿐만 아니라 복잡한 수식도 자를 수 있다는 것을 기억해야한다.

2. 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

https://yongineer.duckdns.org/sql/091.png

마지막에 있는 age_rank_in_class가 각 파티션 내부에서의 나이 순위를 나타내는 필드이다. PARTITION BY구는 GROUP BY구와 달리 집약 기능이 없으므로, 원래 persons 테이블의 레코드가 모두 원래 형태로 나오는 것에 주목할 필요가 있다. 다르게 말하면 GROUP BY구는 입력 집합을 집약하므로 전혀 다른 레벨의 출력으로 변환하지만 PARTITION BY구는 입력에 정보를 추가할 뿐이므로 원본 테이블 정보를 완전히 그대로 유지한다.

또한 GROUP BY구가 식을 매개변수로 받는 이상, PARTITION BY구 또한 마찬가지로 식을 매개변수로 받을 수 있다.

정리

  • GROUP BY구 또는 윈도우 함수의 PARTITION BY구는 집합을 자를때 사용
  • GROUP BY구 또는 윈도우 함수는 내부적으로 해시 또는 정렬 처리를 실행
  • 해시 또는 정령을 메모리를 많이 사용해 만약 메모리가 부족하면 일시 영역으로 저장소를 사용해 성능 문제를 일으킨다.
  • GROUP BY구 또는 윈도우 함수와 CASE식을 함께 사용하면 굉장히 다양한 것을 표현할 수 있다.
Written by@Yongineer
Backend Developer

GitHubInstagram