편집자 주
전문 용어는 특정의 학술 용어나 기술 용어를 말하는데, 대개 둘 이상의 단어가 결합하여 하나의 의미 단위에 대응하는 말, 곧 합성어의 성격으로 되어 있다. 아래와 같은 전문 용어는 단어별로 띄어 씀을 원칙으로 하나, 편의상 붙여 썼다.
1) 수행 결과 > 수행결과
2) 수행 시간 > 수행시간
3) 실행 계획 > 실행계획
지난 글에서는 ORDER BY를 파헤쳤습니다. 이번에는 ORDER BY만큼이나 자주 쓰이는 GROUP BY를 알아볼 시간인데요. GROUP BY는 컬럼 값을 그룹짓고(중복을 제거하고) 이에 대해 건수나 값의 합을 계산할 때 사용합니다.
지난 글 보기: 순서대로 척척, ORDER BY
지난 글 보기: 단일 TABLE을 SELECT하자!
GROUP BY의 기본적인 문법은 아래와 같습니다.
SELECT MBR_NM FROM test.TB_MBR_BAS GROUP BY MBR_NM;
실행계획은 아래와 같습니다. 테이블을 전부 읽어서 temp를 만들고 GROUP BY를 수행하라는 의미죠. GROUP BY가 수행되는 것은 Extra에 Using filesort가 표시된 것으로 유추할 수 있습니다.
참고로 Using filesort는 GROUP BY, ORDER BY, DISTINCT 등의 정렬과 관련한 작업을 수행하면 나타납니다. Query를 수행해볼까요?
위와 같은 결과가 나왔는데, 수행시간은 3.77초가 걸렸습니다. 이 Query는 MBR_NM의 중복을 제거해서 화면에 표시한 것입니다. 이번에는 아래의 Query를 수행해보겠습니다.
SELECT MBR_NM,COUNT(*) FROM test.TB_MBR_BAS GROUP BY MBR_NM;
바뀐 것이 있다면 SELECT 절에 COUNT(*) 가 추가된 것입니다. 실행계획은 다른 점이 없습니다.
COUNT(*)는 레코드의 건수를 계산할 때 사용합니다. 위의 계획은 MBR_NM의 값이 같은 건수를 출력하라는 의미입니다. 수행해보겠습니다.
수행시간은 3.64초로 비슷하게 나옵니다. 위의 내용을 보면 강나영 1437건, 강다은 1465건, 강도연 1445건 … 인 것을 알 수 있습니다. 만약 테이블의 전체 건수를 알고 싶다면 어떻게 할까요? 아래와 같이 수행해보세요.
SELECT COUNT(*) FROM test.TB_MBR_BAS;
수행결과는 다음과 같습니다.
이번에는 나이 컬럼을 추가하고 이름별 나이의 합을 구해보겠습니다. 아래의 명령으로 컬럼을 추가합니다.
ALTER TABLE test.TB_MBR_BAS ADD COLUMN AGE TINYINT UNSIGNED DEFAULT 0 COMMENT '나이';
컬럼이 추가되고, 다음과 같은 구조를 갖출 겁니다.
SELECT
*
FROM test.TB_MBR_BAS ;
0으로 들어간 값을 1에서 100 사이의 임의 값으로 변경하겠습니다. 만약 내용을 변경한다면 아래 예시와 같이 UPDATE문을 사용하세요.
UPDATE test.TB_MBR_BAS
SET AGE = TRUNCATE(RAND()*100,0)+1
;
test.TB_MBR_BAS 의 AGE 컬럼 내용을 변경하라는 명령을 하기 위해 RAND() 함수를 쓰고 임의의 값을 발생시겼습니다. UPDATE 및 SELECT를 수행하면 값이 변경된 것을 알 수 있습니다.
SELECT * FROM test.TB_MBR_BAS;
이번에는 이름이 같은 사람들의 나이 합을 구해볼까요? 합을 구할 때는 SUM 함수를 사용합니다.
SELECT
MBR_NM ,COUNT(*) ,SUM(AGE)
FROM test.TB_MBR_BAS
GROUP BY
MBR_NM ;
실행계획은 AGE 컬럼을 추가하기 전과 바뀐 것이 없다는 걸 알 수 있습니다. 실행결과를 보겠습니다.
수행시간은 4.3초 걸렸습니다. ‘강나영’이란 이름을 가진 사람의 건수는 1,437건이고, 나이의 합은 74,092인 것을 알 수 있습니다. 합산만 하면 의미가 없으니 평균 나이를 구해보겠습니다. 방법은 SUM / COUNT하는 방법과 AVG 함수를 이용하는 방법 두 가지가 있습니다.
SELECT MBR_NM,COUNT(*),SUM(AGE),SUM(AGE)/COUNT(*),AVG(AGE) FROM test.TB_MBR_BAS GROUP BY MBR_NM;
실행계획은 이전과 달라진 부분이 없습니다. 수행결과를 보도록 하죠.
수행시간은 5.6초 정도 걸렸습니다. 좀 더 빨리 수행하면 좋을 텐데 말이죠. 시간을 단축시키려면 어떻게 해야 할까요?
기본적인 방법은 GROUP BY할 컬럼으로 INDEX를 생성하는 것입니다. MBR_NM으로 INDEX를 생성해보겠습니다.
CREATE INDEX IX_MBR_BAS_02 ON test.TB_MBR_BAS (MBR_NM);
생성 후, 이전 Query를 수행합니다.
SELECT MBR_NM,COUNT(*),SUM(AGE),SUM(AGE)/COUNT(*),AVG(AGE) FROM test.TB_MBR_BAS GROUP BY MBR_NM;
아래의 실행계획이 달라진 것을 알 수 있습니다.
실행계획을 보면 전체를 읽어서 처리하는 부분은 사라졌습니다. 대신 IX_MBR_BAS_02 INDEX를 사용하는 것으로 나옵니다. 이미 정렬된 구조를 갖추고 있는 INDEX에서는 GROUP BY 수행 시, 또 정렬하지 않아도 됩니다. 그래서 별도 정렬인 Using filesort가 Extra에 나오지 않은 것이고, GROUP BY에 INDEX를 사용하는 것으로 해석할 수 있습니다. 그렇다면 시간은 얼마나 줄었을까요? 수행해보겠습니다.
0.5초 정도 걸렸습니다. 기존 5.6초보다 훨씬 많이 개선된 것을 알 수 있습니다. 시간은 단축되었는데 결과는 같습니다.
이번에는 IX_MBR_BAS_02를 기존 MBR_NM에서 MBR_NM, AGE로 생성해 보겠습니다.
DROP INDEX IX_MBR_BAS_02 ON test.TB_MBR_BAS;
CREATE INDEX IX_MBR_BAS_02 ON test.TB_MBR_BAS (MBR_NM,AGE);
INDEX를 생성하고 이전 Query를 수행합니다.
SELECT MBR_NM,COUNT(*),SUM(AGE),SUM(AGE)/COUNT(*),AVG(AGE) FROM test.TB_MBR_BAS GROUP BY MBR_NM;
달라진 것이 있다면 Extra에 Using index가 표시된 것입니다. 기존에 INDEX가 MBR_NM으로만 구축된 Query는 IX_MBR_BAS_02 INDEX로 GROUP BY하고, TB_MBR_BAS에서 AGE 합을 구한 것입니다. 하지만 INDEX가 MBR_NM, AGE로 구축된 이번 경우는 IX_MBR_BAS_02 INDEX를 이용해 GROUP BY 와 AGE의 합까지 구한 것이죠. 물론 결과는 같았지만, 수행속도는 0.3초로 개선되었습니다.
마지막으로 성이 김 씨인 경우에만 GROUP BY하여 값을 출력해보겠습니다. 위의 Query에서 WHERE로 조건만 더하면 되는데요.
SELECT MBR_NM,COUNT(*),SUM(AGE),SUM(AGE)/COUNT(*),AVG(AGE) FROM test.TB_MBR_BAS WHERE MBR_NM LIKE '김%' GROUP BY MBR_NM;
위의 이미지처럼 WHERE 조건이 들어가면서 type이 index에서 range로 바뀐 것을 알 수 있습니다. 이것을 해석하면 ‘ IX_MBR_BAS_02를 WHERE조건의 범위만큼 처리하라는 것’입니다. 실행결과를 보죠.
HAVING 절은 GROUP BY로 SUM, COUNT, AVG한 값을 필터 조건으로 걸고 싶을 때 사용합니다. 예시로 위의 Query에서 AVG(AGE) 값이 50보다 작은 것을 출력해보겠습니다.
SELECT MBR_NM,COUNT(*),SUM(AGE),SUM(AGE)/COUNT(*),AVG(AGE) FROM test.TB_MBR_BAS WHERE MBR_NM LIKE '김%' GROUP BY MBR_NM HAVING AVG(AGE) < 50>
결과를 출력하면 아래와 같습니다.
AVG(AGE)가 50보다 작은 값들이 출력된 것이 보이는군요.
글을 마치며간단한 예제를 소개해드렸지만 큰 규모로 GROUP BY를 하면 재미있는 결과들을 만날 수 있습니다. 예를 들어 대한민국 전체 국민을 대상으로 GROUP BY를 실행하면, 평균 나이가 가장 많은 성 씨를 찾을 수 있습니다. 인구통계학 분석에 적용하면 100년 안에 없어질 성 씨를 알 수도 있고요. 응용할 수 있는 범위가 아주 많겠죠? 이상으로 GROUP BY에 대한 소개를 마칩니다.
글
한석종 부장 | R&D 데이터팀
hansj@brandi.co.kr
브랜디, 오직 예쁜 옷만
#브랜디 #개발자 #개발팀 #인사이트 #경험공유
가입하기
