GROUP BY

왜 행을 그룹화해야 할까?

우리는 SQL을 통해 데이터를 그룹화할 수 있다는 점을 인지하고 있습니다.

근데 데이터를 왜 그룹화해야 할까요? 참고로, GROUP BY 절은 주로 집계 함수와 같이 사용되곤 합니다. 집계 함수는 여러 행의 값을 더하거나, 평균값을 내거나, 개수를 세는 등 여러 개의 데이터에 관한 계산을 합니다. 가장 대표적인 집계 함수에는 아래와 같습니다.

1. COUNT() : 행의 개수를 세어줌
2. AVG() : 행 안에 있는 값의 평균을 내어줌
3. MIN() : 행 안에 있는 값의 최솟값을 반환해줌
4. MAX() : 행 안에 있는 값의 최댓값을 반환해줌
5. SUM() : 행 안에 있는 값의 합을 내어줌

한 마디로 말해서, 우리는 다양한 계산을 하기 위해 행을 그룹화합니다.

GROUP BY 의 실행 원리

GROUP BY 는 같은 값을 가진 행끼리 하나의 그룹으로 뭉쳐줍니다. 예시를 보면 좀 더 이해하시기 쉬울 겁니다. 예를 들어 우리가 서점을 운영하고 있다고 가정해봅시다. 우리는 우리 서점이 몇 가지 장르의 책을 보유하고 있으며, 각 장르마다 몇 개의 책을 보유하고 있는지 알고 싶습니다. 우리의 데이터베이스는 1개의 테이블로 구성이 되어있는데, 해당 테이블은 책 이름과 장르 그리고 재고의 숫자를 저장하고 있습니다. 아래 그림은 GROUP BY 가 데이터를 그룹핑하는 것을 시각적으로 보여줍니다. 각 장르마다의 책 재고 개수를 알고 싶은 것이기 때문에, GROUP BY 가 장르를 기준으로 그룹을 나눈 후, 각 그룹에 해당하는 값(여기서는 재고의 총합)을 계산합니다. 

GROUP BY 절과 집계 함수 사용하기

group by 절에 속성(1)부터 속성(n)까지 좌르륵 넣어줍니다. 이는 나누는 기준이 속성(1),...,속성(n)임을 의미합니다. 예를 들어서, 나누는 기준이 년, 분기였다면, 2015년 1분기, 2015년 2분기, 2015년 3분기, 2015년 4분기, ... 등으로 그룹화가 된다는 것입니다. 그리고, 노란색에 나타난 attribute는 select 절에 똑같이 나타나야 합니다. 

SUM 집계 함수

SELECT SUM(ordamt) FROM order_h; 		-- 91,000
SELECT SUM(ordamt) FROM order_h  WHERE orderno = '202201000';	-- NULL
SELECT memid, SUM(ordamt) FROM order_h; 		-- 'hong2' 91,000

MAX & MIN 집계함수

• MAX() 함수 : 그룹화된 각 그룹에서 인수로 지정한 열의 값 중 최대값 반환
• MIN() 함수 : 그룹화된 각 그룹에서 인수로 지정한 열의 값 중 최소값 반환

SELECT MAX(ordamt), MIN(ordamt)
FROM order_h

집계 함수-AVG( )

그룹화된 각 그룹에서 인수로 지정한 열의 평균값을 반환

• AVG( [DISTINCT] 열이름 )

SELECT AVG(ordamt) FROM order_h;
-- DISTINCT 절을 사용하면 값이 같은 행을 제외하고 평균을 계산

집계 함수-COUNT( )

그룹화된 각 그룹에서 인수로 지정한 열의 행 수를 반환

• COUNT( [DISTINCT] 열이름 )

SELECT COUNT(*) FROM `order_h`; -- > 7

SELECT COUNT(DISTINCT memname) FROM members; -- > 5

예시 데이터

이 글에서 우리는 가상의 박물관을 설정하여 그곳에 방문하는 고객의 데이터를 분석할 것입니다. 우리는 우선 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 절에 언급되지 않은 열이 존재한다면 오류가 발생합니다. 

세 번째 : 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

결과를 보니 시간이 흐를수록 평균 머무르는 시간이 길어지고 있는 것을 확인할 수 있습니다. 

실습예제

실습문제1

일자별로 주문총액의 합계를 계산하여, 주문일자 orddt와 일자별 주문총액의 합계 SUM(ordamt)를 조회한다.

SELECT orddt, SUM(ordamt)
FROM order_h
GROUP BY orddt
ORDER BY orddt;

실습문제2

주문 연도와 월별로 주문총액의 합계를 계산하여, 주문년월 CONVERT(orddt, CHAR(7))과 주문총액의 합계 SUM(ordamt)를 조회한다.

SELECT CONVERT(orddt, CHAR(7)) AS ‘연월’, 
    SUM(ordamt) AS ‘합계’
    FROM order_h 
    GROUP BY CONVERT(orddt, CHAR(7))
    ORDER BY CONVERT(orddt, CHAR(7));

실습문제3

모든 주문 기본정보를 담고 있는 order_h 테이블로부터 주문자별로 주문총액의 합계를 계산하여, 주문자 memid와 주문총액의 합계 SUM(ordamt)를 조회한다.

SELECT memid AS '주문자', SUM(ordamt) AS '합계'
FROM order_h
GROUP BY memid
ORDER BY memid;

실습문제4

모든 주문의 기본정보를 담고 있는 order_h 테이블로부터 주문자별로 주문총액의 합계를 계산하여, 주문자ID memid와 주문자 이름 memname 그리고 주문총액의 합계 SUM(ordamt)를 조회한다.

SELECT a.memid AS '주문자', 
	b.memname AS '주문자명', 
	SUM(a.ordamt) AS '합계'
FROM order_h a 
	INNER JOIN members b ON a.memid = b.memid
GROUP BY a.memid
ORDER BY a.memid;

실습문제5

연월별로 그리고 상품 코드별로 주문수량의 합계를 계산하여, 주문 연월 CONVERT(orddt, CHAR(7)), 상품 코드 goodscd 그리고 주문수량의 합계 SUM(qty)를 조회한다.

SELECT CONVERT(a.orddt, CHAR(7)) AS ‘연월’, 
    b.goodscd AS ‘상품코드’, 
    SUM(b.qty) AS ‘주문수량’
FROM order_h a 
	INNER JOIN order_d b ON a.orderno = b.orderno
GROUP BY CONVERT(a.orddt, CHAR(7)), b.goodscd
ORDER BY CONVERT(a.orddt, CHAR(7)), b.goodscd;

실습문제6

실습문제5에서 GROUP BY 절과 ORDER BY 절에 기술된 열의 순서를 바꾸어 실습한다.

SELECT b.goodscd AS '상품코드',
	CONVERT(a.orddt, CHAR(7)) AS '연월',
	SUM(b.qty) AS '주문수량'
FROM order_h a 
	INNER JOIN order_d b ON a.orderno = b.orderno
GROUP BY b.goodscd, CONVERT(a.orddt, CHAR(7))
ORDER BY b.goodscd, CONVERT(a.orddt, CHAR(7));

실습문제7

2022년도에 주문한 월별 주문 건수와 주문총액의 최대값과 최소값을 찾아 연월 CONVERT(orddt, CHAR(7)), 주문 건수 COUNT(), 최대값 MAX(ordamt), 최소값 MIN(ordamt)을 조회한다.

*SELECT CONVERT(a.orddt, CHAR(7)) AS '연월',
COUNT(*) AS '건수',
MAX(ordamt) AS '최대값',
MIN(ordamt) AS '최소값'
FROM order_h a
WHERE YEAR(orddt) = 2022
GROUP BY CONVERT(a.orddt, CHAR(7))
ORDER BY CONVERT(a.orddt, CHAR(7));

실습문제8

2022년도에 주문한 주문의 월별 주문총액의 평균을 계산하여, 연월 CONVERT(orddt, CHAR(7)), 주문총액의 평균 AVG(ordamt)을 조회한다.

SELECT CONVERT(a.orddt, CHAR(7)) AS ‘연월’, AVG(ordamt) AS ‘평균값’
FROM order_h a
WHERE YEAR(orddt) = 2022
GROUP BY CONVERT(a.orddt, CHAR(7))
ORDER BY CONVERT(a.orddt, CHAR(7));

 

참고

https://kimsyoung.tistory.com/entry/SQL-GROUP-BY-%E4%B8%8A-%EA%B0%9C%EB%85%90%EA%B3%BC-%EC%8B%A4%EC%A0%9C-%EC%A0%81%EC%9A%A9-%EB%B0%A9%EB%B2%95