[SQL] 집약 함수 (aggregate function) 작동 원리 이해하기

이 글은 SQL 레벨업(한빛 미디어, 미크)를 참고하여 작성하였습니다.

SQL의 특징적인 사고방식 중 하나는 레코드 단위가 아닌 레코드의 "집합" 단위로 처리를 기술하는 것이다. 이런 사용 방식을 집합 지향 (set-oriented)이라고 부른다. 이런 특징이 가장 잘 드러나는 때는 group by구, having 구와 sum, count 등의 집약 함수를 사용할 때이다.

SQL은 이런 집합 조작이 잘 구현 되어서, 절차 지향 언어에서 반복문 또는 분기를 여러 번 해야하는 처리를 간단한 코드로 작성 가능하다. 즉, SQL을 잘 사용하기 위해서는 사고방식 단위를 레코드 에서 레코드의 집합 으로 발상의 전환이 필요하다.

(1) SQL에 존재하는 집약 함수 (aggregate function)

: COUNT, SUM, AVG, MAX, MIN (표준 집약 함수)

(2) 비집약 테이블을 집약하는 방법


A. UNION을 사용한 방법 (불가)

• 이 방법은 필드 수가 달라서 union으로 집약하는 것이 불가하고, 설사 가능하더라도 성능적으로 좋지 않음

SELECT id, data1, data2
FROM NonAggTbl
WHERE id = 'jim' AND type = 'A'

UNION

SELECT id, data3, data4, data5
FROM NonAggTbl
WHERE id = 'jim' AND type = 'B'

UNION

SELECT id, data6
FROM NonAggTbl
WHERE id = 'jim' AND type = 'C'

B. CASE 식과 GROUP BY 응용 (불가)

• 이 쿼리는 문법 오류 발생. GROUP BY 구로 집약 했을 때, SELECT 구에는 상수, GROUP BY에서 사용한 집약키, 집약 함수 만 사용 가능하다.
• 집합과 요소를 혼동한 것으로, SQL의 원리를 위배하는 것이다.

SELECT id
     , CASE WHEN type = 'A' THEN data1 ELSE NULL END AS data1
     , CASE WHEN type = 'A' THEN data2 ELSE NULL END AS data2
     , CASE WHEN type = 'B' THEN data3 ELSE NULL END AS data3
     , CASE WHEN type = 'B' THEN data4 ELSE NULL END AS data4
     , CASE WHEN type = 'B' THEN data5 ELSE NULL END AS data5
     , CASE WHEN type = 'C' THEN data6 ELSE NULL END AS data6
FROM NonAggTbl
GROUP BY id ; 

C. 정답 쿼리

• GROUP BY로 데이터를 자르는 시점에는 각 집합에 3개의 요소가 있다.
• 여기에 집약 함수가 적용되면 null을 제외하고, 하나의 요소만 있는 집합이 만들어진다.
• 여기에 MAX 함수를 사용하면 내부에 있는 하나의 요소를 선택하게 된다.

SELECT id
     , MAX(CASE WHEN tpye = 'A' THEN data1 ELSE NULL END) AS data1
     , MAX(CASE WHEN tpye = 'A' THEN data2 ELSE NULL END) AS data2
     , MAX(CASE WHEN tpye = 'B' THEN data3 ELSE NULL END) AS data3
     , MAX(CASE WHEN tpye = 'B' THEN data4 ELSE NULL END) AS data4
     , MAX(CASE WHEN tpye = 'B' THEN data5 ELSE NULL END) AS data5
     , MAX(CASE WHEN tpye = 'C' THEN data6 ELSE NULL END) AS data6
FROM NonAggTbl
GROUP BY id;

(3) 집약 쿼리의 실행 계획

집약 쿼리의 실행 계획을 살펴보면, 비집약 테이블을 모두 스캔하고 GROUP BY로 집약을 수행하는 단순한 실행 계획을 가지고 있다.

다만, GROUP BY의 집약 조작에는 모두 해시 알고리즘을 사용하고 있다.

집약에는 정렬을 사용하는 것이라고 생각할 수 있고 틀린 이야기는 아니다.

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

하지만 정렬과 해시 모두 메모리를 많이 사용하므로, 충분한 해시용 워킹 메모리가 확보되지 않으면 스왑이 발생한다.

예를 들어, 오라클에서 정렬 또는 해시를 위해 PGA라는 메모리 영역을 사용한다. 이때 PGA 크기가 집약 대상 데이터양에 비해 부족하면 일시 영역(저장소)을 사용해 부족한 만큼 채운다.

위 현상을 TEMP 탈락이라 한다. 이 현상이 발생하면 메모리만으로 처리가 끝나느 경우와 비교해 극단적으로 성능이 떨어지게 된다. 메모리와 저장소(일반적으로 디스크)의 접근속도가 굉장히 차이나기 때문이다.