데이터 그룹핑

목표

• 데이터를 그룹핑하는 방법을 배워 테이블에 있는 데이터의 일부분을 요약한다.
  이를 위해 SELECT 문의 GROUP BY와 HAVING 알아보자

목차

1. 데이터_그룹핑_이해하기
2. 그룹_필터링
3. SELECT문_순서

데이터_그룹핑_이해하기

수 많은 데이터 중 내가 원하는 각 판매처의 제품 수를 구하거나, 하나의 제품만을 판매하는 판매처 혹은 10개 이상을 파는 판매처가 몇 개인지 확인하고 싶다면 어떻게 해야 할까?

이럴 때 그룹핑이 필요하다.

• 그룹핑은 데이터를 논리적으로 나눠주기 때문에 각 그룹에 대한 집계 연산을 수행할 수 있다.

그룹 생성하기

그룹은 SELECT 문에서 GROUP BY 절을 사용해 생성할 수 있다.

SELECT vend_id, COUNT(*) AS num_prods 
FROM Products 
GROUP BY vend_id;

여기서 GROUP BY 절은 DBMS에게 vend_id로 그룹핑하고 데이터를 정렬하라고 명령한다.
이를 통해 이 절은 전체 테이블에 있는 데이터가 아니라 같은 vend_id가 나올 때마다 num_prods를 증가시킨다.

GROUP BY 절을 사용하면 자동으로 각 그룹에 대해 계산하기 때문에, 계산할 그룹을 따로 명시할 필요가 없다. GROUP BY 절은 DBMS에게 먼저 데이터를 그룹핑한 후, 각각의 그룹에 대해 계산하라고 지시한다.

 

GROUP BY 절을 잘 사용하기 위해 먼저 알아두어야 할 중요한 규칙이 있다.

 

GROUP BY 규칙

• GROUP BY 절는 원하는 만큼의 열을 써서, 중첩(nested) 그룹을 만들 수 있다. 이 방식은 데이터를 그룹핑하는 방식을 좀 더 세밀하게 제어할 수 있게 해준다.

 

• GROUP BY 절에 중첩된 그룹이 있다면, 데이터는 마지막으로 지정된 그룹에서 요약된다.
  즉, 지정된 열은 그룹핑할 때 같이 측정된다(그래서 각 열 단위로는 데이터를 얻지 못한다.)

 

• GROUP BY 절 있는 열은 가져오는 열이거나, 그룹 함수는 아니면서 유효한 수식이어야 한다.
  SELECT 절``에서 수식을 사용한다면, GROUP BY 절에도 같은 수식을 사용해야 한다. 별칭은 사용할 수 없다.(note. MySQL 8.0에서 별칭 사용이 되는 것을 확인함)

 

• 대부분의 SQL 실행 환경에서는 GROUP BY 절에서 문자나 메모와 같은 가변형 길이의 데이터형은 사용할 수 없다.

 

• 그룹 함수 명령문을 제외하고 SELECT 절에 있는 모든 열은 GROUP BY 절에 존재해야 한다.

 

• 그룹핑하는 열의 행에 NULL 값이 있다면, NULL 도 그룹으로 가져온다. 여러 행이 NULL 값을 가진다면, 모두 함께 그룹핑된다.

 

• GROUP BY 절은 WHERE 절 뒤에 그리고 ORDER BY 절 앞에 와야 한다.

[!NOTE] 열 위치로 지정하기
일부 SQL 실행 환경에서는 SELECT 절에 있는 위치로 GROUP BY절에 열을 지정할 수 있다.
예를 들어 GROUP BY 2,1은 가져온 열 가운데 두 번째 열로 그룹핑하고, 다시 첫 번째 열로 그룹핑하라는 의미이다.
이 방식은 문법이 쉽다는 장점이 있지만 모든 SQL 실행 환경에서 지원되지는 않는다. 또한 SQL 문을 수정할 때 에러를 양산할 소지가 있다.

그룹_필터링

GROUP BY 절은 데이터 그룹핑 뿐만 아니라 SQL은 어떤 그룹을 포함하고 어떤 그룹은 배제할 것인지 필터링도 가능하게 해준다.
기존에 사용하던 WHERE 절은 행을 필터링 하기 때문에 그룹에서는 적용할 수가 없고, 실제로 WHERE 절은 그룹이 무엇인지 모른다. 이에 SQL 문은 HAVING이라는 절을 제공한다.

 

HAVING 절은 WHERE 절과 매우 유사해 WHERE 절의 모든 유형은 HAVING 절에서도 사용할 수 있다.
단, 한 가지 차이점은 WHERE 절은 행을 필터링 하고, HAVING 절은 그룹을 필터링한다는 점이다.

[!NOTE]
WHERE 절 조건(와일드카드 조건과 여러 개의 연산자를 사용한 절 등)의 기법과 옵션은 모두 HAVING 절에도 적용할 수 있다.
문법은 동일하지만, 키워드만 다를 뿐이다.

SELECT cust_id, COUNT(*) AS orders 
FROM Orders 
GROUP BY cust_id HAVING COUNT(*) >= 2;

여기선 특정 행의 값이 아닌 그룹핑된 집곗값으로 필터링되었다.

[!NOTE] HAVING 절과 WEHRE 절의 차이점
WHERE 절은 데이터가 그룹핑되기 전에 필터링하고, HAVING 절은 데이터가 그룹핑된 후에 필터링한다는 점이 다르다.

이는 매우 중요한 차이점으로 WHERE 절에서 필터링 되어 제거된 행은 그룹에 포함되지 않는다. 이것은 계산된 값을 변화시킬 수 있는데, HAVING 절에서는 그룹이 그 변경된 값으로 필터링하기 때문에 필터링 결과에 영향을 줄 수 있다.

SELECT vend_id, COUNT(*) AS num_prods 
FROM Products 
WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;

[!info]
앞서 언급한것처럼 HAVING 절과 WHERE 절은 매우 유사하므로 대다수 DBMS에서 GROUP BY가 명시되지 않는다면
똑같이 처리한다.
그렇더라도 이 두가지는 구별하여 사용하는 것이 좋기 때문에 GROUP BY 절이 있을 때만 HAVING 절을 사용하고,
행 단위 필터링은 WHERE 절을 사용하자.

그룹핑과 정령

GROUP BY 절과 ORDER BY 절이 같은 결과를 가져온다고 할지라도, 이 둘은 매우 다르다는 것을 이해하는 것이 중요하다.

ORDER BY	GROUP BY
결과를 정렬한다.	행을 그룹핑한다. 결과는 그룹 순서대로 출력되지 않을 수 있다.
어떤 열이라도(가져오지 않은 열도) 사용할 수 있다.	선택된 열이나 수식만 사용할 수 있고 선택된 열이나 수식을 꼭 사용해야한다.
필수 항목은 아니다.	그룹 함수와 함께 사용하는 열(또는 수식)이 있는 경우 필수 항목이다.

[!NOTE] ORDER BY를 잊지 말자
GROUP BY 절을 사용할 때마다 ORDER BY 절을 명시해야 한다,
그렇게 해야 데이터가 제대로 정렬되었다고 확신할 수 있다.
GROUP BY 절이 데이터를 정확히 정렬해 줄 것이라고 기대하지 말자.

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;

SELECT문_순서

SELECT 문에 여러 절이 있을 때 순서를 명확히 사용해야 한다. 다음 표를 참고하자

절	설명	필수
SELECT	가져올 열이나 수식	YES
FROM	데이터를 가져올 테이블	테이블에서 데이터를 가져올 때 사용한다.
WHERE	행 레벨 필터링	NO
GROUP BY	그룹 지정	그룹핑한 데이터로 집계 계산을 할 때 사용한다.
HAVING	그룹 레벨 필터링	NO
ORDER BY	정렬 순서	NO

/*  OrderItems 테이블은 각각의 주문에 대한 개별 항목을 포함하고 있다. 
주문 번호(order_num)에 해당하는 줄 수를 order_lines라고 표시하고,  
결과를 order_lines로 정렬하는 SQL문을 작성하라. */ 

SELECT order_num, COUNT(*) AS order_lines 
FROM OrderItems 
GROUP BY order_num 
ORDER BY order_lines;  

/* Products 테이블에 있는 prod_price를 사용하여,  
각 판매처에서 취급하는 가장 저렴한 항목을 cheapest_item이라고 이름 짓고  
가격순(최저가에서 최고가)으로 정렬하는 SQL문을 작성하라. */ 

SELECT vend_id, MIN(prod_price) AS cheapest_item 
FROM Products 
GROUP BY vend_id 
ORDER BY cheapest_item;  

/* 최고의 고객을 식별하는 게 중요하므로 100개 이상의 항목을 주문한 주문 번호 
(OrderItems 테이블의 order_num 이용)를 가져오는 SQL문을 작성하라. */ 

SELECT order_num 
FROM OrderItems 
GROUP BY order_num HAVING SUM(quantity) >= 100 
ORDER BY order_num;  

/* 얼마만큼 비용을 지출했는지가 최고의 고객을 판단하는 또 다른 방법이다. 
주문액의 합이 1000 이상인 모든 주문의 주문 번호(OrderItems 테이블의 order_num)를 가져와 
주문 번호로 정렬하는 SQL 문을 작성하라 이를 위해선 item_price와 수량을 곱한 값을 모두 합해야 한다. */ 

SELECT order_num, SUM(item_price * quantity) AS total_price 
FROM OrderItems 
GROUP BY order_num HAVING SUM(item_price*quantity) >= 1000 
ORDER BY order_num;

---