GROUP BY (上) : 개념과 실제 사용 방법
SQL 공부를 시작하면 얼마 되지 않아 GROUP BY 라는 개념을 배우게 됩니다. 데이터를 그룹화하는 것, 즉 ㄷ이터를 집계하는 것은 데이터베이스 상에서 상당히 중요한 개념입니다. 이 글에서는, 실 예제들을 통해 GROUP BY를 어떤 식으로 사용할 수 있는지 살펴보려고 합니다. 총 5개의 예시가 있고, 쉬운 개념부터 살짝 복잡한 개념까지 훑어볼 예정입니다. 거기에 더불어 HAVING 절도 함께 공부할 것이랍니다.
SQL은 거의 50년 이상 데이터베이스에게 말을 걸 수 있는 언어로 활용되어 왔습니다. SQL의 핵심 개념 중 하나는 바로 데이터 그룹화입니다. 위에서 말했듯이, 데이터 집계라고도 말합니다. 만약 이 글을 읽고 계신다면, 아마 GROUP BY 절에 대해 들어보신 적이 있을 것입니다. 그러면 GROUP BY를 통해 실제 비즈니스 문제를 해결할 수 있는지 살펴보도록 하겠습니다.
왜 행을 그룹화해야 할까?
우리는 SQL을 통해 데이터를 그룹화할 수 있다는 점을 인지하고 있습니다. 근데 데이터를 왜 그룹화해야 할까요? 참고로, GROUP BY 절은 주로 집계 함수와 같이 사용되곤 합니다. 집계 함수는 여러 행의 값을 더하거나, 평균값을 내거나, 개수를 세는 등 여러 개의 데이터에 관한 계산을 합니다. 가장 대표적인 집계 함수에는 하기와 같습니다.
1. COUNT() : 행의 개수를 세어줌
2. AVG() : 행 안에 있는 값의 평균을 내어줌
3. MIN() : 행 안에 있는 값의 최솟값을 반환해줌
4. MAX() : 행 안에 있는 값의 최댓값을 반환해줌
5. SUM() : 행 안에 있는 값의 합을 내어줌
한 마디로 말해서, 우리는 다양한 계산을 하기 위해 행을 그룹화합니다.
GROUP BY 의 실행 원리
GROUP BY 는 같은 값을 가진 행끼리 하나의 그룹으로 뭉쳐줍니다. 예시를 보면 좀 더 이해하시기 쉬울 겁니다. 예를 들어 우리가 서점을 운영하고 있다고 가정해봅시다. 우리는 우리 서점이 몇 가지의 장르의 책을 보유하고 있으며, 각 장르마다 몇 개의 책을 보유하고 있는지 알고 싶습니다. 우리의 데이터베이스는 1개의 테이블로 구성이 되어있는데, 해당 테이블은 책 이름과 장르 그리고 재고의 숫자를 저장하고 있습니다. 아래 그림은 GROUP BY 가 데이터를 그룹핑하는 것을 시각적으로 보여줍니다. 각 장르마다의 책 재고 개수를 알고 싶은 것이기 때문에, GROUP BY 가 장르를 기준으로 그룹을 나눈 후, 각 그룹에 해당하는 값(여기서는 재고의 총합)을 계산합니다.
우리가 사용할 데이터
이 글에서 우리는 가상의 박물관을 설정하여 그곳에 방문하는 고객의 데이터를 분석할 것입니다. 우리는 우선 visit 테이블을 사용할 것입니다. 각 행에는 박물관 방문과 관련된 정보가 저장되어 있습니다. 몇 개의 행을 샘플로 먼저 살펴보도록 합시다.
| date | price | duration |
| 2020-05-01 | 2 | 15 |
| 2020-05-01 | 4 | 37 |
| 2020-05-06 | 7 | 24 |
| ... |
보시면 아시다시피, 해당 테이블은 그렇게 복잡하지 않습니다. 딱 3개의 열만 존재하니까요. 각 열에 관한 설명을 해보자면, date 는 한 고객이 박물관을 방문한 날짜를 나타내고, price 는 그 고객이 박물관 입장을 하기 위해 지불한 금액을 나타냅니다. 동일한 방물관인데 가격이 다른 이유는 날짜에 따라 프로모션이 붙을 수도 있고, 특정 할인을 받아 이용한 고객도 있을 것이기 때문입니다. 마지막 열인 duration 은 그 고객이 박물관에 머무른 시간을 나타냅니다.
GROUP BY 예시
이제 차례대로 GROUP BY의 사용법 5가지를 살펴보도록 하겠습니다.
첫 번째 : GROUP BY + 1개의 열
첫 번째 예시니까 간단한 예시로 시작해 봅시다. 우리는 위의 visit 테이블을 통해 날짜별로 얼마나 많은 사람들이 방문했는지 살펴보고 싶습니다. 즉, 우리가 구해야 할 것은 날짜와 그 날짜에 해당하는 방문객의 숫자인 것이죠. 해당 결과를 얻으려면 다음과 같은 쿼리를 작성할 수 있습니다.
SELECT
date,
COUNT(*)
FROM visit
GROUP BY date;
보시면 우리는 2개의 열만 추출할 것입니다. 이 중 COUNT(*)는 모든 행의 개수를 센다는 것을 의미합니다. 우리는 날짜를 기준으로 해당 날짜에 방문한 고객의 총 인원수를 확인하고 싶은 것이기 때문에 GROUP BY 뒤에 date 를 넣어주었습니다. 위의 쿼리를 실행하면, 아래와 같은 결과를 얻을 수 있습니다.
| date | count |
| 2020-06-29 | 7 |
| 2020-05-23 | 6 |
| 2020-06-23 | 5 |
| ... |
잘하셨습니다. 이제 날짜별 방문객의 수를 알 수 있겠네요. 부연설명을 하자면, COUNT 함수의 괄호 안에 모든 행의 개수를 세라는 의미의 * 대신에 열 이름을 넣어도 됩니다. 예를 들어, COUNT(duration) 처럼 말입니다. * 와 열 이름을 넣는 것의 차이는, 전자는 NULL 값이 포함된 행까지 센다면 후자는 NULL 값을 제외한 행의 개수만을 셉니다. 만약 데이터베이스 내에 NULL 값이 존재하지 않는다면, COUNT(*)을 실행하든 COUNT(열 이름)을 실행하든 둘의 결과값은 같게 됩니다.
두 번째 : GROUP BY + 여러 개의 열
이제 우리가 알고 싶은 건 월별 티켓의 평균 금액입니다. 이를 구하려면, 아까보다는 조금 복잡한 쿼리를 작성해야 합니다. 한 번 살펴보시죠.
SELECT
EXTRACT(year FROM date) AS year,
EXTRACT(month FROM date) AS year,
ROUND(AVG(price), 2) AS avg_price
FROM visit
GROUP BY
EXTRACT(year FROM date),
EXTRACT(month FROM date);
날짜 데이터로부터 연도와 월만 추출하고자 EXTRACT 함수를 사용하였습니다. SELECT 문을 보면 총 3개의 열이 있습니다. 그중 2개는 날짜 데이터를 담고 있는 연, 월 열이고, 마지막 열은 티켓 금액의 평균값을 구하는 함수가 적혀 있습니다. 이 마지막 열은 총 2개의 함수가 섞여 있는데요. 제일 안에 AVG 함수를 통해 티켓의 평균 금액을 구하고, 이를 ROUND 함수를 통해 소수점 둘째 자리까지 반올림해주었습니다.
우리는 각 연도별에 따른 월별 평균 티겟 금액을 보고 싶으므로 총 2개의 열을 기준으로 데이터를 그룹 지어 주었습니다. 첫 번째를 연도로 그룹 했기 때문에 동일한 연도별로 먼저 그룹을 짓고, 그렇게 연도별로 그룹 지어진 데이터를 그다음 기준이 월별로 데이터를 또 한 번 그룹 지어 줍니다.
그래서 쿼리를 실행하면 아래와 같은 결과를 얻게 됩니다. 결과를 살펴보니까 5월 대비 6월의 평균 금액이 하락한 것을 확인할 수 있습니다. 이렇게 되면 박물관의 매출에 타격을 줄 수도 있겠네요.
| year | month | avg_price |
| 2020 | 5 | 7.52 |
| 2020 | 6 | 6.70 |
참고로, GROUP BY 뒤에 열 이름을 직접 다 적는 대신 열의 위치로 대신하여 적을 수 있습니다. 지금 우리 쿼리문을 보면 SELECT 문에 EXTRACT 함수를 사용한 열이 있기 때문에 GROUP BY에 이를 다시 적을 때도 상당히 내용이 깁니다. 이럴 땐 아래와 같이 쿼리문을 작성해줘도 이전 쿼리문과 동일한 결과값을 보여줍니다.
SELECT
EXTRACT(year FROM date) AS year,
EXTRACT(month FROM date) AS month,
ROUND(AVG(price), 2) AS avg_price
FROM visit
GROUP BY 1, 2;
GROUP BY 절을 사용할 때 이 점은 꼭 기억해 주셔야 합니다. SELECT 문에 있는 모든 열은 집계 함수가 되거나 GROUP BY 절에 나타나야 합니다. 우리 예시를 가지고 이야기해보자면, SELECT 문 중에서 EXTRACT 함수를 활용한 2개의 열은 GROUP BY 절에 사용이 되었고, 마지막 열은 COUNT 집계 함수를 사용했습니다. GROUP BY 절을 사용하는데 만약 SELECT 문에 집계 함수를 사용하지 않거나 GROUP BY 절에 언급되지 않은 열이 존재한다면 오류가 발생합니다. 이에 대해서는 SQL GROUP BY(下) : 오류문 해결하기 편에서 더 자세히 다루도록 하겠습니다.
세 번째 : GROUP BY 랑 ORDER BY 같이 사용
이번에는 월별 평균 머무른 시간을 알고 싶습니다. 그리고 각 행의 시간 순대로 정렬되었으면 합니다. 아래 쿼리문은 이전의 쿼리문과 거의 유사합니다. 유일한 다른 점은 ORDER BY 절이 사용되었다는 것입니다.
SELECT
EXTRACT(year FROM date) AS year,
EXTRACT(month FROM date) AS month,
ROUND(AVG(duration), 2) AS avg_duration
FROM visit
GROUP BY 1, 2
ORDER BY 1, 2;
ORDER BY 절은 우리가 보고 싶은 대로 행의 순서를 정해주는 것입니다. 위 쿼리문을 보면, 첫 번째 열인 연도를 첫 번째 정렬 기준으로 삼았고, 그다음 정렬 기준으로 월로 삼았습니다. ORDER BY 열 이름 뒤에 아무 말도 적지 않으면 오름차순으로, ORDER BY 열 이름 뒤에 DESC 를 적어주면 내림차순으로 정렬해 줍니다. 이제 위의 쿼리문을 실행해 보도록 하겠습니다.
| year | month | avg_duration |
| 2020 | 5 | 47.61 |
| 2020 | 6 | 51.33 |
결과를 보니 5월보다 6월에 고객이 머무른 시간이 더 긴 것을 확인할 수 있습니다.
네 번째 : GROUP BY 랑 HAVING 같이 사용
이번에는 좀 새로운 문제에 맞닥트렸습니다. 우리는 일별 평균 티겟 금액을 보고 싶습니다. 근데 여기서 추가 조건이 있어요. 방문 고객 수가 3명보다 적은 날짜는 제외하고 싶습니다. 이 조건을 달려면 우리는 쿼리문을 조금 다르게 작성해야 합니다. 한 번 보시죠.
SELECT
date,
ROUND(AVG(price), 2) AS avg_price
FROM visit
GROUP BY date
HAVING COUNG(*) > 3
ORDER BY date;
위 쿼리문에서 새로운 부분은 HAVING COUNT(*) > 3 입니다. HAVING 절은 GROUP BY 를 통해 데이터를 그룹핑 한 행에만 사용할 수 있습니다. 이 경우, 우리는 날짜로 이미 데이터를 그룹화하였기에 HAVING 절을 사용할 수 있었습니다. 날짜별로 그룹 지어진 데이터들의 개수가 3개보다 많아야 우리가 원하는 결과를 얻을 수 있습니다. 만약 그룹 지어진 데이터들이 이 조건을 만족하지 못한다면 결과에는 나타나지 않습니다. 위의 쿼리문을 실행하면 아래와 같은 결과를 얻게 됩니다.
| date | avg_price |
| 2020-05-01 | 5.80 |
| 2020-05-15 | 7.00 |
| 2020-05-23 | 6.67 |
| ... |
다섯 번째 : GROUP BY, HAVING 그리고 WHERE 까지 같이 사용
마지막으로, 우리는 이런 문제를 해결해보고자 합니다. 우리는 일별 평균 머무른 시간을 알고 싶습니다. 근데 추가 조건이 있습니다. 일별 방문 고객 수가 3명보다 많아야 합니다. 그리고 해당 방문의 머무른 시간이 5분보다 길었으면 합니다. 해당 결과를 얻으려면 우리는 아래와 같이 쿼리문을 작성해야 합니다.
SELECT
date,
ROUND(AVG(duration), 2) AS avg_duration
FROM visit
WHERE duration > 5
GROUP BY date
HAVING COUNT(*) > 3
ORDER BY date;
새로운 부분은 WHERE 절입니다. WHERE 절을 사용한 이유는 머무른 시간이 5분보다 많은 데이터만 필터링하기 위함이었습니다. WHERE 절과 HAVING 절은 상당히 비슷해 보입니다. 데이터를 필터링한다는 측면에서 바라보면요. 하지만 둘은 분명한 차이가 존재합니다. WHERE 절을 행들이 그룹 지어지기 전 단일 행들을 필터링하는 데 사용합니다. 그래서 쿼리문에서도 WHERE 절이 GROUP BY 절 전에 적혀 있는 것을 확인할 수 있습니다. 반면, HAVING 절은 행들이 그룹 지어진 후의 행들을 필터링하는 데 사용됩니다. 그래서 쿼리문에서도 HAVING 절은 GROUP BY 절 뒤에 적혀 있고요.
| date | avg_duration |
| 2020-05-01 | 29.80 |
| 2020-05-15 | 55.75 |
| 2020-05-23 | 32.17 |
| 2020-05-29 | 69.50 |
| 2020-06-02 | 39.83 |
| 2020-06-04 | 48.67 |
| 2020-06-09 | 48.50 |
| 2020-06-23 | 51.60 |
| 2020-06-29 | 57.86 |
결과를 보니 시간이 흐를수록 평균 머무르는 시간이 길어지고 있는 것을 확인할 수 있습니다. 아무래도 박물관이 사람들의 입을 타 꽤나 유명해진 것 같군요.
SQL의 GROUP BY 절은 참으로 다재다능한 아이라 여러분이 분석을 진행하실 때 상당히 유용하게 써먹게 될 개념입니다. 지금까지 GROUP BY 를 통해 다양한 비즈니스 문제들을 효과적으로 해결할 수 있는 방법들을 살펴보았습니다.