[DB] - 집약 (4장)

[Irisation23]

0. 집약

SQL 에는 집약 함수(aggregate function) 라고 하는, 다른 함수와 구별해서 부르는 함수가 있다.

• COUNT
• SUM
• AVG
• MAX
• MIN

이외에도 확장적인 집약 함수가 있지만, 표준 SQL에 있는 집약 함수는 5개이다.
집약 함수의 특징은 여러개의 레코드를 한 개의 레코드로 집약하는 기능을 가지고 있다.

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

해당 집약 파트에서 사용하게 될 테이블은 아래와 같다.

• 비집약 테이블

CSV 파일과 같은 형식의 플랫 파일을 그대로 테이블에 붙인 유사 배열 테이블이다.
해당 테이블은 관계 모델의 관점에서는 좋지 않은 모델링을 가졌다.

한 사람의 정보에 접근할 때 'WHERE id = 'Jim'' 과 같은 SELECT 구문을 사용할 때 당연히 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으로 여러 개의 쿼리를 머지하는 것은 성능적으로 안티 패턴이다.(3장 참조)

해당 데이터의 이상한 구조는 아래의 테이블 같은 구조로 작성 되어야한다.

• AggTbl

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

기존의 테이블이 AggTbl과 같이 작성 되었다면, SELECT 구문을 3번날리는 수고로움을 덜 수 있다.
이젠 NonAggTbl 테이블을 AggTbl테이블 처럼 변환하기 위한 쿼리에 대해 알아보자.

1.1 CASE 식과 GROUP BY 응용

일단 사람 단위로 집약하므로 GROUP BY의 집약 키는 사람의 식별자인 id 필드가 필요할 것이다.
이어서 선택할 필드를 data_type 필드로 분기한다.

3장에서 살펴본 CASE 식을 사용한다면 아래와 같은 쿼리를 생각해 볼 수있다.

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 에서는 오류가 발생하지 않음 하지만 대부분의 쿼리에서는 오류가 발생)

SELECT 구에 입력할 수 있는 것은 다음과 같은 세 가지 뿐이다.

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

현재 테이블을 id 필드로 그룹화하고 CASE 식에 data_type을 지정하면, 하나의 레코드만 선택된다.

위 쿼리가 원하는대로 동작하기 위해서는 아래와 같은 쿼리 수정이 필요하다.

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 함수를 사용하면 내부에 있는 하나의 요소를 선택할 수 있다. (꼭 MAX 함수뿐 아니라 다른 함수의 사용도 가능하다)

1.2 집약, 해시, 정렬

해당 쿼리의 실행 계획은 어떻게 될까?

NonAggTbl을 모두 스캔하고 GROUP BY로 집약을 수행하는 단순한 실행 계획이다.
주목해야 할 부분은 GROUP BY의 집약 조작에 모두 '해시' 알고리즘을 사용하고 있다는 것이다. 하지만 집약은 때에 따라서 정렬을 사용하기도 한다.

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

GROUP BY와 관련된 성능을 챙길 때 중요한 점 🎉

정렬과 해시 모두 메모리를 많이 사용하므로, 충분한 해시용(또는 정렬용) 워킹 메모리의 확보가 필수적이다.
만약 확보하지 못한다면 이는 스왑이 발생된다. 그리고 저장소 위의 파일이 사용되면서 굉장히 느려진다.

따라서, 연산 대상 레코드 수가 많은 GROUP BY 구(또는 집약 함수)를 사용하는 SQL에서는 충분한 성능 검증을 해야한다.

---

2. 집약의 이해

아래와 같은 테이블이 있다.

• PriceByAge(연령별 가격)

해당 테이블은 (product_id, low_age)라는 키로 레코드가 유일하게 정해진다.(low_age 대신 high_age를 사용해도 상관없다.)

문제가 주어진다.

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

해당 테이블의 데이터중 제품3은 21~30까지의 데이터가 끊겨있다.
1개의 레코드로 전체를 커버하지 못해도 여러 개의 코드를 조합해 커버할 수 있다면 집약의 이해에 큰 도움이 된다.

해결 방법은 아래와 같다.

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

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

HAVING 구의 'high - low_age + 1' 로 각 레코드의 연령 범위에 있는 정수 개수를 구한다.
그리고 같은 제품을 모아 이 개수를 합하는 쿼리이다.

현재 예제에서는 '연령' 이라는 숫자 자료형의 데이터를 사용했다. 하지만 확장하면 날짜 또는 시각에도 적용할 수 있다.

추가적으로 아래와 같은 호텔 테이블이 있다.

• 호텔 테이블 (Hotel Rooms)

해당 테이블에서 사람들이 숙박한 날이 10일 이상인 방을 선택하는 방법은?

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