[SQL Query] Group by, Having

데이터 집계 시에 사용한다.
Ex) 고객 별 전체, 일별 주문수, 부문 별 월평균 매출랙 등..

■ 집계쿼리의 작동하는 순서

1. FROM 절에서 데이터 집합을 만든다.
2. WHERE 절은 FROM 절에서 만든 데이터 집합을 조건에 맞게 걸러 낸다.
3. GROUP BY 절은 WHERE 절에서 필터링한(조건에 맞는 데이터를 걸러 낸) 데이터 집합을 집계한다.
4. HAVING 절은 GROUP BY 절에서 집계한 데이터 집합을 다시 조건에 맞게 필터링한다.
5. SELECT 절은 집계하고 필터링한 데이터 집합을 변환(보통 집계 함수로 처리) 한다.
6 ORDER BY 절은 변환된 데이터 집합을 정렬한다.

■ 핵심 정리

 - SELECT 절에는 있지만 GROUP BY 절에는 기술되지 않은 컬럼들은 반드시 집계함수를 사용
   (계산결과가 집계나 상수 형태로 나올 수도 있지만) 해야 한다.
     - 집계함수 : COUNT(), SUM(), AVG(), MIN(), MAX() 등

 - GROUP BY 절에 컬럼을 과도하게 기술하면, 쿼리의 성능에 악영향을 미친다.

 - 집계와 세부 정보 조회 두 가지 목적을 달성해야 하는 쿼리를 작성할 때는 먼저 서브쿼리에서
   모든 집계를 수행한 후, 세부 데이터를 담은 테이블과 조인해 해당 정보를 가져온다.

 - GROUP BY 구로 집약했을때, SELECT 구에 입력할수 있는 것은 다음의 세가지 뿐이다
   . 상수
   . GROUP BY 구에서 사용한 집약 키
   . 집약 함수

 

■ 예제 쿼리

▷ 예제쿼리 1
   HotelRooms (호텔 테이블) 

room_nbr (방 번호)	start_date (도착일)	end_date (출발일)
101	2008-02-01	2008-02-06
101	2008-02-06	2008-02-08
101	2008-02-10	2008-02-13
202	2008-02-05	2008-02-08
202	2008-02-08	2008-02-11
202	2008-02-11	2008-02-12
203	2008-02-03	2008-02-17

이 테이블에서 사람들이 숙박한 날이 10일 이상인 방을 선택합니다.
숙박한 날의 수는 도착일이 2월1일, 출발일이 2월6일 이라면 5박이므로 5일입니다.

▷ 쿼리

SELECT toom_nbr,
	SUM(end_date - start_date) AS working_days
FROM HotelRooms
GROUP BY room_nbr
HAVING SUM(end_data - start_date) >= 10

 

---

 

▷ 예제쿼리 2
   Persons (인물 테이블)

name (이름)	age (나이)	height (키(cm))	weight (몸무게(kg))
Anderson	30	188	90
Adela	21	167	55
Bates	87	158	48
Becky	54	187	70
Bill	39	177	120
Chris	90	175	48
Darwin	12	160	55
Dawson	25	182	90
Donald	30	176	53

이 테이블에서 이름 첫 글자를 사용해 특정한 알파벳으로 시작하는 이름을 가진 사람이 몇 명인지 집계해 봅시다.

▷ 쿼리

SELECT SUBSTRING(name, 1, 1) AS label,
	COUNT(*)
FROM Persons
GROUP BY SUBSTRING(name, 1, 1)

▷ 실행결과

label	count(*)
A	2
B	3
C	1
D	3

---

이 테이블에서 나이를 기준으로 어린이(20세 미만), 성인(20~69세), 노인(70세 이상)으로 나누어 집계해 봅시다.

▷ 쿼리 (GROUP BY)

SELECT CASE WHEN age < 20 THEN '어린이'
			WHEN age BETWEEN 20 AND 69 THEN '성인'
            WHEN age >= 70 THEN '노인'
            ELSE NULL END AS age_class,
            COUNT(*)
FROM Persons
GROUP BY WHEN age < 20 THEN '어린이'
			WHEN age BETWEEN 20 AND 69 THEN '성인'
            WHEN age >= 70 THEN '노인'
            ELSE NULL END

▷ 실행결과

age_class	COUNT(*)
어린이	1
성인	6
노인	2

▷ 부가설명
    자르기의 기준이 되는 키를 GROUP BY 구와 SELECT 구 모두에 입력하는 것이 포인트입니다.
    GROUP BY 구에서 CASE 식 또는 함수를 사용해도 실행 계획에는 영향이 없습니다.
    물론 단순한 필드가 아니라 필드에 연산을 추가한 식을 GROUP BY 구의 키로 한다면 어느정도 CPU 연산에
    오버헤드가 걸릴 것입니다. 사실 집약 함수와 GROUP BY 의 실행 계획은 성능적인 측면에서, 해시(또는 정렬)에
    사용되는 워킹 메모리의 용량에 주의하라는 것 이외에 따로 할말은 없습니다.

▷ 쿼리 (PARTITION BY)

SELECT name, age, 
		CASE WHEN age < 20 THEN '어린이'
			WHEN age BETWEEN 20 AND 69 THEN '성인'
            WHEN age >= 70 THEN '노인'
            ELSE NULL END AS age_class,
            RANK() OVER(PARTITION BY CASE WHEN age < 20 THEN '어린이'
											WHEN age BETWEEN 20 AND 69 THEN '성인'
            								WHEN age >= 70 THEN '노인'
            								ELSE NULL END
            ORDER BY age) AS age_rank_in_class
FROM Persons
ORDER BY age_class, age_rank_in_class

▷ 실행결과

name	age	age_class	age_rank_in_class
Darwin	12	어린이	1
Adela	21	성인	1
Dawson	25	성인	2
Anderson	30	성인	3
Donald	30	성인	3
Bill	39	성인	4
Becky	54	성인	6
Bates	87	노인	1
Chris	90	노인	2

▷ 부가설명
    PARTITION BY구는 GROUP BY 구와 달리 집약 기능이 없으므로, 원래 Persons 테이블의 레코드가 모두 원래 형태로
    표시가 됩니다. 다르게 말하면, GROUP BY 구는 입력 집합을 집약하므로 전혀 다른 레벨의 출력으로 변환하지만,
    PARTITION BY 구는 입력에 정보를 추가할 뿐이므로 원본 테이블 정보를 완전히 그대로 유지합니다.

---

건강 진단 등을 하면서 BMI라는 몸무게 지표를 들어본 적이 있을 것입니다. BMI는 키를 t(m 단위), 몸무게를 w(kg단위)
라고 했을때 다음과 같은 방법으로 구합니다.
    BMI = W / t²
이 BMI수치를 바탕으로 아래의 기준으로 사람들의 체중을 분류하고 몇 명이 해당되는지 알아봅시다
 - 18.5미만           => 저체중
 - 18.5이상 25미만 => 정상
 - 25이상             => 과체중

▷ 쿼리

SELECT CASE WHEN weight / POWER(height / 100, 2) < 18.5							THEN '저체중'
			WHEN 18.5 <= POWER(height / 100, 2) AND POWER(height / 100, 2) < 25	THEN '정상'
			WHEN 25 <= POWER(height / 100, 2)									THEN '과체중'
            ELSE NULL END AS bmi,
		COUNT(*)
FROM Persons
GROUP BY CASE WHEN weight / POWER(height / 100, 2) < 18.5							THEN '저체중'
			WHEN 18.5 <= POWER(height / 100, 2) AND POWER(height / 100, 2) < 25	THEN '정상'
			WHEN 25 <= POWER(height / 100, 2)									THEN '과체중'
            ELSE NULL END

▷ 실행결과

bmi	COUNT(*)
저체중	2
정상	4
과체중	3

▷ 부가설명
    GROUP BY 구에는 필드 이름만 적을 수 있다고 생각하는 사람들이 많은데요.
    이렇게 복잡한 수식을 기준으로도 자를 수 있다는 것을 꼭 기억하세요

 

 

 

 

---

. 한 사람 밖에 없는 주소 선택

SELECT address, COUNT(*)
FROM Address
GROUP BY address
HAVING Count(*) = 1

SELECT c.CustomerID, c.CustFirstName,
	c.CustLastName, c.CustState,
    MAX(o.OrderDate) AS LastOrderDate,
    COUNT(o.OrderNumber) AS OrderCount,
    SUM(o.OrderTotal) AS TotalAmount
FROM Customer AS c
	LEFT JOIN Order AS o
    	ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID
    


SELECT c.CustomerID, c.CustFirstName, c.CustLastName, 
	c.CustState, o.LastOrderDate, o.OrderCount, o.TotalAmount
FROM Customer AS c
	LEFT JOIN (
    	SELECT t.CustomerID, MAX(t.OrderDate) AS LastOrderDate,
        	COUNT(t.OrderNumber) AS OrderCount,
            SUM(t.OrderTotal) AS TotalAmount
        FROM Order AS t
        GROUP BY t.CustomerID
        ) AS o
        ON c.CustomerID = o.CustomerID

 

 - 2015년 4분기 배송 시간이 평균보다 느린 판매자를 찾는 쿼리

SELECT v.VendName, AVG(DATEDIFF(DAY, p.OrderDate, p.DeliveryDate)) AS DeliveryDays
FROM Vendor AS v
INNER JOIN PurchaseOrders AS p
ON v.VenderID = p.VenderID
WHERE p.DeliveryDate IS NT NULL
AND p.OrderDate BETWEEN '2015-10-01' AND '2015-12-31'
GROUP BY v.VendName
HAVING AVG(DATEDIFF(DAY, p.OrderDate, p.DeliveryDate)) > (
	SELECT AVG(DATEDIFF(DAY, p2.OrderDate, p2.DeliveryDate))
	FROM PurchaseOrders AS p2
	WHERE p2.DeliveryDate IS NOT NULL
	AND p2.OrderDate BETWEEN '2015-10-01' AND '2015-12-31'
	)

 

 - 2015년 4분기 카테고리별 우량 판매자 찾기

 - 카테고리 별로 도수가 가장 높은 알코올을 추출

 -