16. GROUP BY
SQL을 이용해서 테이블에 저장된 데이터를 그룹별로 묶는 방법
배경
우리는 지난 함수 (Functions) 글에서 SQL 에서 제공하는 함수를 이용해서 통계값을 구하는 방법에 대해서 알아보았습니다. 함수를 통해 통계값은 구할 수 있었지만, 한번 SQL 요청을 통해 오직 하나의 결과값만을 얻을 수 있었습니다. 다시 말해, COUNT 를 이용해 개수를 세든, AVG 를 통해 평균값을 구하든 결과 테이블에 표현되는 행의 개수는 언제나 1개였습니다.
만약, 사용자 테이블에서 남자가 몇명이고 여자는 몇명인지 구하고 싶다면 어떻게 해야할까요? 물론, WHERE 에 성별 조건을 바꿔가면서 2번 SQL 문을 실행하면 원하는 결과값을 구할 수는 있을 겁니다. 하지만, 이런 방법으로는 첫번째 돌린 결과값을 메모장이나 엑셀에 붙여놓고, 2번째 결과값과 비교해야할 것 입니다. 한눈에 여러개의 데이터를 눈으로 살펴보면서 데이터로부터 의미있는 통찰을 얻어내는 데이터분석 과정을 하는데 있어서 이런 식의 방법은 당연히 사용하지 않습니다.
이런 문제(?)를 해결하고자 SQL 은 데이터베이스에서 선택된 데이터를 그룹지어 통계값을 구할 수 있는 기능인 GROUP BY 를 제공하고 있습니다. 오늘은 이 GROUP BY 를 살펴보고자 합니다.
GROUP BY 문법
GROUP BY 를 사용하는 문법은 아래와 같습니다. GROUP BY는 데이터를 선택해서 보여줄 때, 어떻게 묶어서 보여줄지 설정하는 기능이므로 SELECT 문의 확장이라고 생각하시면 됩니다. 다시 말해, INSERT, UPDATE, DELETE 와는 관계가 없는 기능입니다. 아래와 같이 SELECT ~ FROM 뒤에 GROUP BY 키워드를 입력하고 이어서 뒤에 그룹으로 묶고 싶은 열의 이름을 적으면 됩니다.
예시를 통해 알아보도록 합시다. 사용자 테이블에서 남자와 여자의 키 평균값을 (한번의 SQL 실행으로) 구하고 싶다고 가정합시다. 그룹으로 묶고 싶은 열은 성별, 평균값을 구하고 싶은 열은 키로 SQL은 아래와 같습니다.
실제 데이터를 가지고 결과값이 어떻게 나오는지 살펴봅시다. 먼저, 데이터베이스 사용자 테이블에 아래와 같은 데이터 4개가 들어있다고 가정합시다.
이 데이터를 대상으로 남자와 여자의 키 평균값을 구해보도록 합시다. SQL 쿼리는 위에서 언급한 SQL문을 실행시키면 됩니다. 위의 SQL을 실행시키면 결과는 아래와 같이 나옵니다. 한번의 쿼리의 결과로 하나의 테이블에 남자와 여자의 평균 키가 구해진 것입니다.
지난 시간 함수를 배웠을때와 다시 한번 비교해서 생각해봅시다. 이 글 초반에 언급했듯이 GROUP BY 를 사용하지 않으면, 함수를 통해서 구해진 결과값은 언제나 1개입니다. (WHERE 문에 의해 선택된) 전체 데이터를 대상으로 함수를 적용하기 때문입니다. 하지만, GROUP BY를 사용하게 되면 그룹으로 묶고 싶은 열의 값의 종류만큼 결과값이 나오게 됩니다. 위의 예제에서는 그룹으로 묶은 열이 성별인데, 전체 데이터를 봤을때 성별에 해당하는 값의 종류는 male, female 2개이기 때문에 위 결과 처럼 2개의 행이 결과로 나온것입니다. 만약, 10개의 종류가 있는 열을 대상으로 GROUP BY를 실행하면 10개의 행이 결과로 나올 것 입니다.
GROUP BY 과정
DBMS 내부적으로는 어떤 과정을 통해서 GROUP BY가 실행되는 것일까요? 이 부분을 이해하는것이 GROUP BY 를 실행했을때 결과값을 명확히 이해하는데 큰 도움이 됩니다. SQL 문이 복잡해지는 가장 큰 지점중 하나가 바로 이 GROUP BY 입니다.
위의 예제를 이용해서 GROUP BY 진행과정을 살펴보도록 합시다. GROUP BY 를 진행하면, DBMS 내부적으로는 다음과 같은 4단계 작업을 진행합니다.
1. SELECT
첫 단계는 데이터를 선택하는 과정입니다. 현재는 테이블에 있는 전체 데이터 (4개)를 선택하기 때문에 테이블과 동일한 모습입니다. 하지만, WHERE 문을 추가해서 SELECT를 진행했다면 WHERE 필터 조건에 해당하는 데이터만 선택될 것 입니다.
2. GROUP BY
두번째 단계는 첫 단계에서 선택된 데이터를 그룹으로 묶는 작업입니다. GROUP BY 뒤에서 그룹으로 묶기로 한 열이 성별(gender)이기 때문에 동일한 성별 값을 가지고 있는 행끼리 묶어서 테이블을 만듭니다. 여기서 테이블은 데이터베이스 저장소에 저장되는 테이블이 아닌 DBMS 내부적으로 보관하는 임시 테이블입니다. 위의 예제에서는 2개의 성별 (male, female) 만 존재하기 때문에 임시로 만들어진 테이블이 2개지만, 만약 100개의 값의 종류를 가지는 열을 GROUP BY로 선택한다면, 2단계에서 만들어지는 임시 테이블의 개수가 100개일 것입니다.
3. Function
세번째 단계는 두번째 단계에서 나누어진 테이블 각각에 함수를 적용하는 단계입니다. 함수를 적용하면 1개 결과값이 나온다는 것을 기억하실 겁니다. 100개의 테이블 각각에 함수를 적용하면, 각 테이블마다 함수가 적용되 1개의 결과값을 가지는 100개의 테이블이 만들어질 것입니다. 위의 그림에서, 각 성별마다 1개의 결과값, 여기에서는 키의 평균값이 구해진 것을 보실 수 있습니다.
4. Merge
마지막 네번째 단계는 함수가 적용된 테이블들을 합치는 단계입니다. 100개의 테이블로 나누어져있던 임시 테이블 각각을 하나의 행으로 취급해서 합쳐진 결과값 테이블 한개로 만들어줍니다. 위의 예제에서는 2개의 테이블로 나누어졌기 때문에, 최종 결과값이 2개의 행으로 나타나는 것입니다.
GROUP BY를 사용하기 위해서는 반드시 SELECT 문에 함수를 적용해야합니다. 이유는 위의 과정에서 설명했던 것과 같이, 함수가 없다면 GROUP BY를 이용해서 테이블을 여러개로 나누었다가 다시 합칠 방법이 없기 때문입니다. GROUP BY를 사용할때 함수를 넣는 것을 잊지 마세요 :)
WHERE
GROUP BY와 WHERE 조건을 함께 사용하는 예시도 살펴보도록 합시다. 문법은 아래와 같습니다. 앞서 이야기했던 것처럼, WHERE는 데이터를 선택하는 과정 즉 SELECT와 관련이 있지, 데이터를 묶는 방법인 GROUP BY와 관련이 없습니다. 그러므로 SELECT문에 해당하는 FROM 뒤에 WHERE 조건문이 와야합니다.
예시로 사용자 테이블에서 나이가 20살 이상인 사용자 중 남자와 여자의 평균 키를 구하는 SQL 문은 아래와 같습니다.
ORDER BY
ORDER BY 역시 GROUP BY와 함께 사용할 수 있습니다. 문법은 아래와 같습니다. ORDER BY는 GROUP BY 과정에서 설명했던 네가지 단계 중 마지막 과정에서 사용되는 기능입니다. Merge를 한 이후에 이를 어떤 순서로 배열할지를 결정하기 때문입니다. 그러므로, GROUP BY보다 더 뒤에 ORDER BY 키워드가 위치하고 있습니다.
예를 들어, 사용자 테이블에서 나이가 20살 이상인 사용자 중 남자와 여자의 평균 키를 구하는데 이를 성별 이름의 오름차순으로 구하는 SQL 문은 아래와 같습니다.
LIMIT
마지막으로, LIMIT 까지도 포함시켜 SQL 문을 작성해 봅시다. GROUP BY 로 묶은 SQL 의 결과는 GROUP BY 에서 선택한 열이 가지고 있는 값의 종류만큼 결과값이 나옵니다. 값의 종류가 100개 인 경우에는 100개의 행이 결과로 나오겠죠. 이 경우에 50개만 보고 싶다면 LIMIT 으로 제한을 걸면 됩니다. 예를 들어, 사용자 테이블에서 남자를 대상으로 각 연령별 평균 키를 구하는데 나이가 제일많은 5개 그룹만 선택해서 본다면 아래와 같은 쿼리를 작성하면 됩니다.
마치며
오늘은 데이터를 그룹으로 묶어 각 그룹의 통계값을 얻는 방법인 GROUP BY 에 대해서 배워보았습니다. 배경 섹션에서 이야기했듯이, GROUP BY 를 사용하면 한 테이블에 각 그룹별 통계값이 보여지게 됩니다. 한 테이블에 각 그룹별 통계가 보이는 것은, 데이터 분석을 통해 통찰을 얻는데 매우 중요합니다. 데이터 분석은 통계값을 구하고 이를 보고 생각하는 과정에 가깝기 때문입니다.
GROUP BY를 배우는 과정에서 테이블이 나누어지는 과정을 시각화해서 보여드렸습니다. 데이터베이스는 테이블에서 시작해서 테이블로 끝납니다. SQL 문법은 저장되어있는 테이블을 다른 테이블로 만드는 문법에 불과합니다. SQL을 배우시는 동안 테이블이 변화하는 과정을 머릿속에 시각화하는 습관을 들이시면, 오류없이 데이터 분석을 진행하는데 큰 도움이 될 것입니다.
아래 질문을 GROUP BY 와 함수를 이용해서 SQL 을 작성해 보세요.
사용자 테이블에서 날짜별 가입한 사용자 수를 구하고 싶다.
사용자 테이블에서 남자를 대상으로 날짜별로 가입한 나이의 평균을 구하고 싶다.
사용자 테이블에서 나이별로 가입한 사용자의 숫자를 구하고 싶다.
