데이터 모델과 SQL

[데이터 모델과 SQL] 12. Grouping data

영바이트 2023. 1. 16. 22:59

 

SUM( ), AVG( )와 같은 합 연산 함수들은 결과 셋의 모든 원소들을 대상으로 적용된다. 만약 결과 셋의 원소들을 원하는 기준으로 나누고 각 그룹에 대해 각각 별도로 합 연산을 적용하고 싶은 경우에는 어떻게 할까? Gouping 연산은 결과 셋의 원소들을 그룹으로 나누고 각각의 그룹을 따로 처리할 수 있도록 도와준다.

 

● GROUP BY

문법: SELECT <컬럼, 표현식> FROM <테이블> GROUP BY <컬럼 이름>
GROUP BY 구문은 GROUP BY 키워드에 지정한 컬럼 이름을 기준으로 결과 셋을 하위 그룹으로 묶어주는 기능을 수행한다.

 

예) 예능인 그룹 별로 그룹 이름, 계약 건수, 그리고 평균 계약 금액을 알고 싶다.

먼저, 서브 쿼리를 이용해서 구성한 검색식은 아래와 같다.

SELECT EntStageName, 
(SELECT COUNT(EngagementNumber) FROM engagements WHERE engagements.EntertainerID=entertainers.EntertainerID) AS NumOfContracts, 
(SELECT AVG(ContractPrice) FROM engagements WHERE engagements.EntertainerID=entertainers.EntertainerID) AS AverageContractPrice 
FROM entertainers ORDER BY EntStageName;

 

하지만 검색식을 GROUP BY 구문을 이용해서 구성할 수도 있다. 서브쿼리를 이용한 검색식 보다 간단하고 이해하기 쉽다.

SELECT EntStageName, COUNT(*), AVG(ContractPrice) 
FROM engagements LEFT OUTER JOIN entertainers ON engagements.EntertainerID=entertainers.EntertainerID 
GROUP BY EntStageName ORDER BY EntStageName;

*ORDER BY 구문은 위 두 검색식의 결과를 비교하기 위한 것으로 검색 결과 내용에는 영향을 미치지 않는다.

 

결국 '서브쿼리 + WHERE + 그룹 구성 기준'  =  'GROUP BY + 그룹 구성 기준'이 된다.

 

하나 이상의 기준을 사용하는 예를 살펴보자.
예) 모든 고객들의 이름, 계약 건수, 총 계약금액, 최소 계약금액, 최대 계약금액, 그리고 평균 계약금액을 알고 싶다.

SELECT CONCAT(CustFirstName, ' ', CustLastName) AS CustName, AVG(ContractPrice) 
FROM customers LEFT OUTER JOIN engagements ON customers.CustomerID=engagements.CustomerID 
GROUP BY customers.CustFirstName, customers.CustLastName GROUP BY CustName;

좋은 예라고는 하기 어렵지만 그룹핑 기준이 둘 이상인 경우 GROUP BY 구문의 대상을 콤마(,)로 구분하여 지정해 주면된다(위 예에서 customers.CustFirstName, customers.CustLastName).

 

사실 위 예는 아래와 같이 고객을 구분하는 기준으로 고객 테이블(customers)의 키key인 CutomerID를 GROUP BY의 대상으로 사용하는 것이 더 좋다.

SELECT CONCAT(CustFirstName, ' ', CustLastName) AS CustName, AVG(ContractPrice) 
FROM customers LEFT OUTER JOIN engagements ON customers.CustomerID=engagements.CustomerID 
GROUP BY customers.CustomerID GROUP BY CustName;

 

● 서브쿼리 안에서 GROUP BY 구문 사용하기

예) 다른 고객들의 계약금액 합보다 큰 계약들을 알고 싶다.

① 메인 쿼리: 

SELECT engM.EngagementNumber, engM.CustomerID, engM.ContractPrice FROM engagements AS engM GROUP BY engM.CustomerID, engM.EngagementNumber

②서브 쿼리: 

SELECT SUM(engS.ContractPrice) FROM engagements AS engS WHERE engS.CustomerID<>engM.CustomerID GROUP BY engS.CustomerID

 

SELECT engM.EngagementNumber, engM.CustomerID, engM.ContractPrice 
FROM engagements AS engM 
WHERE engM.ContractPrice > ALL (SELECT SUM(engS.ContractPrice) FROM engagements AS engS WHERE engS.CustomerID<>engM.CustomerID GROUP BY engS.CustomerID) 
GROUP BY engM.CustomerID, engM.EngagementNumber;

 

● GROUP BY와 DISTINCT
아래 두 예를 살펴보자.
예 1) 고객들의 거주 도시를 알고 싶다.

SELECT CustCity FROM customers GROUP BY CustCity;

 

예 2) 고객들의 거주 도시를 알고 싶다.

SELECT DISTINCT CustCity FROM customers;

 

예 1과 예 2의 결과는 같다. 하지만 검색식을 처리하는 과정은 다르다.
예 1의 경우 전체 데이터를 CustCity 별로 나누고 각 그룹에서 CustCity 값을 출력하는 반면, 예 2의 경우 전체 CustCity 출력 결과 셋에서 중복된 것들을 제거한다.


결과 셋을 그룹 별로 나누고 각 그룹 별 특성을 얻고자 한다면 GROUP BY 키워드를 사용하고 어떤 값을 중복 없이 얻는 것이 목적이라면 명확하게 DISTINCT 키워드를 사용한다. 따라서 사용자의 관점에 따라 예 1이 더 좋을 수도 있고, 예 2가 더 좋을 수도 있다.

 

● GROUP BY 구문을 사용할 때 주의해야 할 점들


ⓐ 출력 대상이 합 연산과 같이 그룹을 대표하는 값이 아니라면 GROUP BY 구문 안에 반드시 포함시켜야 한다.
출력 대상은 그 형태가 같아야 한다. 예를 들어 검색식의 출력 대상들이 모두 하나 이상의 행으로 구성된 테이블 형태이거나 하나의 값 만을 가지는 스칼라 형태거나 출력 대상들은 모두 형태가 같아야 한다. 만약 GROUP BY 구문의 대상으로 출력 대상을 모두 지정해주지 않으면(합 연산 함수의 입력에 쓰이는 컬럼 이름은 제외) 어떤 출력은 값이 여러 개의 행인데 반해 함께 출력되어야 하는 어떤 출력 값은 값이 하나인 경우가 발생할 수 있고, 이 경우 출력 값이 정상적으로 조합되지 않는다.

 

ⓑ FROM 키워드와 WHERE 키워드의 대상들만 그룹 구분을 위한 조건들(컬럼 이름들)이 될 수 있다.
예) 워싱턴Washington(WA)에 거주하는 고객들의 전체 이름(full name), 전체 주소(full address), 가장 최근의 계약일, 그리고 고객 별 전체 계약 금액을 알고 싶다.

검색식 1)

SELECT CONCAT(Customers.CustFirstName, ' ', Customers.CustLastName) AS CustFullName, 
CONCAT(Customers.CustStreetAddress, Customers.CustCity, Customers.CustState) AS CustFullAddress, 
MAX(Engagements.EndDate), SUM(Engagements.ContractPrice) 
FROM Customers LEFT OUTER JOIN Engagements ON Customers.CustomerID=Engagements.CustomerID 
GROUP BY Customers.CustomerID, Customers.CustFirstName, Customers.CustLastName, Customers.CustStreetAddress, Customers.CustCity, Customers.CustState;

 

검색식 2)

SELECT CONCAT(Customers.CustFirstName, ' ', Customers.CustLastName) AS CustFullName, 
CONCAT(Customers.CustStreetAddress, Customers.CustCity, Customers.CustState) AS CustFullAddress, 
MAX(Engagements.EndDate), SUM(Engagements.ContractPrice) 
FROM Customers LEFT OUTER JOIN Engagements ON Customers.CustomerID=Engagements.CustomerID 
GROUP BY CustFullName, CustFullAddress;

 

SQL 표준에 따르면 검색식 1은 정상적인 검색식이고 검색식 2는 문법 오류에 해당한다. 왜냐하면 검색식 2에 GROUP BY 조건으로 사용된 CustFullName, CustFullAddress 항목들(SELECT 구문의 대상들)은 정작 FROM, WHERE, GROUP BY 구문을 처리한 후에 결과 셋에서 얻을 수 있는 것이기 때문이다. 즉, 처리 결과를 처리 과정에 참고하는 순환오류(reference error)를 발생시킨다.

 

■