[SQL] 집약과 자르기 - 집약(Aggregation)

작성일자 : 2023-09-23

Ver 0.1.1

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

• COUNT
• SUM
• AVG
• MIN
• MAX

위 5개 함수는 가장 대표적이면서 익숙한 함수이고, 이 외에도 분산, 상관과 같은 통계적 자료를 구하기 위한 함수들이 많이 구현되고 있다. 

---

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
Ken	B	123	12	178	346	85	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

위 테이블은 CSV 파일과 같은 형식의 플랫 파일을 그대로 테이블에 붙인 형태의 유사 배열 테이블이다. 위 테이블은 모델의 관점에서 굉장히 좋지 않은 테이블이자만 이렇게 플랫 파일을 유사 배열 테이블로 표현하는 상황도 꽤 있다.

 

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

 

- CASE 식과 GROUP BY 응용

 

(CASE 문으로 작성해보았지만 오류가 발생하는 쿼리)

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

위 쿼리는 문법 오류가 발생한다. GROUP BY 구로 집약했을 때 SELECT 구에 입력할 수 있는 것은 1. 상수 2. GROUP BY 구에서 사용한 집약 키 3. 집약 함수 과 같은 세가지 뿐이며, 현재 CASE 식의 내부에서 사용하고 있는 data_1 ~ data_6은 이중 어떠한 것에도 해당되지 않는다.

 

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

 

하지만 위와같은 발상은 집합과 요소를 혼동한 것으로 SQL 원리(=집합론의 원리)를 위배하는 것이기에 귀찮더라도 집약 함수를 사용하여 아래처럼 작성해야 한다.

 

(모든 구현에서 작동하는 정답)

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

이런 집약 쿼리의 실행 계획은 둘다 굉장히 간단하다. 주목해야할 부분은 GROUP BY의 집약 조작에 모두 '해시(HASH)'라는 알고리즘을 사용하고 있다는 것이다. 최근에는 GROUP BY를 사용하는 집약에서 정렬보다 해시를 사용하는 경우가 많다. 이는 GROUP BY 구에 지정되어 있는 필드를 해시 함수를 사용해 해시 키로 변환하고, 같은 해시 키를 가진 그룹을 모아 집약하는 방법이다. 고전적인 정렬을 사용한 방법보다 빠르므로 많이 사용되고 있다. 특히 해시의 성질상 GROUP BY의 유일성이 높으면 더 효율적으로 작동한다.

 

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

---