[DB][SQL] 그룹화와 집계 함수

※ 해당 포스팅은 개인의 공부 정리용 글입니다. 틀린 내용이 있다면 추후 수정될 수 있습니다.

※ 해당 포스팅은 Mycrosoft SQL Server에서 사용하는 T-SQL 문법을 기반으로 하고 있으며, 예시는 SSMS(SQL Server Management Studio) 환경에서 작성하고 있습니다. 

 

 

 

 

이번 포스팅에서는 데이터를 그룹화하는 GROUP BY 키워드와 집계 함수에 대해 알아보겠습니다.

0. 서문

 이번 포스팅부터는 실제 데이터베이스와 비슷한 환경으로 많은 데이터를 다루는 연습을 하기 위해 샘플 데이터베이스를 사용하려고 합니다.

https://www.sqlskills.com/sql-server-resources/sql-server-demos/

SQL Server Demos and Sample Databases | SQLskills.com

 해당 사이트에서 제공하는 'Baseball Stats Sample Database'에 포함된 테이블을 예시로 사용할 예정입니다. 

 

 

1. 그룹화(GROUP BY)

 이전 포스팅에서 데이터를 검색하기 위한 기초 문법을 알아보았습니다. 하지만 실제 데이터베이스는 많은 데이터를 다루기 때문에, 데이터를 특정 기준에 따라 그룹화(grouping)할 필요성이 생깁니다. 이 때 사용하는 것이 GROUP BY 키워드입니다. 해당 키워드는 다음과 같이 사용합니다.

SELECT 속성1, 속성2, ...
FROM 테이블명
WHERE 조건식1 (AND/OR) 조건식2 ...
GROUP BY 속성1, 속성2, ...

 단, GROUP BY 키워드를 사용하고 나면 그룹화의 기준이 되는 속성 외에는 평범하게 출력할 수 없습니다. 예를 들어 1학년 전체 학생들에 대한 테이블이 있고 이를 학급별로 그룹화 한다면, 학생들 개개인의 이름은 출력할 수가 없습니다. 이미 데이터가 학급별로 묶여있는 상태이기 때문입니다. 대신 학급별 수학 성적의 평균과 같은 그룹별 집계 데이터를 도출해내기 좋을 것입니다. 따라서 GROUP BY 키워드는 보통 후술할 집계 함수와 함께 사용합니다.

 

 

2. 집계 함수

 집계 함수는 주로 GROUP BY 키워드와 함께 사용하며, 대표적인 집계 함수에는 COUNT(총 데이터 수), AVG(평균), SUM(합계), MAX(최대값), MIN(최소값) 등이 있습니다. 집계 함수는 SELECT 절에서 다음과 같이 사용합니다.

SELECT COUNT(속성1), SUM(속성2), AVG(속성3) ...
FROM 테이블명
GROUP BY 속성1, 속성2, ...

 

 다음은 이번 예제로 사용할 salaries 테이블입니다.

그림 1. salaries 테이블

 해당 테이블은 각 연도 및 팀마다 플레이어가 받은 연봉을 저장하고 있으며, 행 수(카디널리티)가 23141개나 됩니다. 이렇게 데이터가 많은 테이블은 그냥 봐서는 유의미한 정보를 도출해내기 어렵습니다. 여기서 그룹화와 집계 함수를 이용해 '연도별 데이터 수'와 같은 유의미한 정보를 도출해낼 수 있습니다. 

-- 연도별(GROUP BY yearID)
-- 데이터 수(COUNT(yearID))
SELECT yearID, COUNT(yearID) AS countOfData
FROM salaries
GROUP BY yearID;

그림 2. 연도별 데이터 수

 참고로 집계 함수의 결과는 속성명을 별도로 지정해주지 않을 경우 (No Column Name)과 같은 이름으로 나옵니다. 따라서 집계 함수 사용 시, 해당 속성이 어떤 정보인지 알 수 있도록 AS 키워드로 별칭을 붙여주는 것이 좋습니다. 

 

 또한 GROUP BY 키워드에서 그룹화 기준을 두개 이상 설정하여 좀 더 세분화된 그룹에 집계 함수를 사용할 수도 있습니다. 예를 들어 teamID, yearID를 그룹화 기준으로 사용할 경우, '각 팀의 연도별 선수들 연봉 평균'과 같은 정보를 도출해낼 수 있습니다.

-- 각 팀의(GROUP BY teamID) 연도별(yearID)
-- 선수들 연봉 평균(AVG(salary))
SELECT teamID, yearID, AVG(salary) AS avgOfSalary
FROM salaries
GROUP BY teamID, yearID;

그림 3. 각 팀의 연도별 선수들 연봉 평균

 

 다만 주의해야 할 것은, 집계 함수는 경우에 따라 산술 오버플로가 발생할 수 있습니다. 위의 예시에서 teamID, yearID로 세분화하지 않고 teamID만으로 그룹화한 뒤 평균을 구하려고 하면, 다음과 같이 산술 오버플로가 발생했다는 에러 메시지를 확인하게 됩니다.

-- 각 팀별(GROUP BY teamID)
-- 연봉 평균(AVG(salary))
SELECT teamID, AVG(salary) AS avgOfSalary
FROM salaries
GROUP BY teamID;

그림 4. 산술 오버플로

  그룹화를 teamID 기준으로만 했기 때문에, 팀 별 연봉 평균은 즉 해당 팀의 모든 연도, 모든 선수들의 연봉을 합한 뒤 나누어야 합니다. 이 과정에서 INT로 표현할 수 있는 최대값을 넘어가버렸기 때문에 위와 같이 산술 오버플로가 발생한 것입니다. 따라서 집계 함수 사용 시 오버플로가 발생했다면, GROUP BY 뒤에 그룹화 기준을 추가하여 정보를 좀 더 세밀하게 그룹화하여 주는 것이 좋습니다.

 

 

3. 그룹별 조건(HAVING)

 HAVING은 WHERE와 마찬가지로 검색 대상의 조건을 거는 키워드로, 차이점은 GROUP BY 키워드로 그룹화된 대상에 거는 조건입니다. 따라서 항상 GROUP BY와 함께 사용하며, 단독으로는 사용할 수 없습니다. 기존의 검색 키워드들과 같이 사용할 경우 순서는 다음과 같습니다.

SELECT 속성1, 속성2, ...
FROM 테이블명
WHERE 조건식1 (AND/OR) 조건식2 ...
GROUP BY 속성1, 속성2, ...
HAVING 조건식1 (AND/OR) 조건식2 ...
ORDER BY 속성1 (ASC/DESC), 속성2 (ASC/DESC), ...

 

 예를 들어 salaries 테이블에서 2012년도 데이터를 팀별로 그룹화한 뒤, 선수들 평균 연봉이 3,000,000 이상인 팀을 확인하고 싶다면 다음과 같이 사용할 수 있습니다.

-- 2012년도(WHERE yearID = 2012)
-- 각 팀별(GROUP BY teamID) 
-- 평균 연봉이 3000000 이상(HAVING AVG(salary) >= 3000000)
-- 평균 연봉 기준 내림차순 정렬(ORDER BY AvgOfSalary DESC)
SELECT teamID, AVG(salary) AS AvgOfSalary
FROM salaries
WHERE yearID = 2012
GROUP BY teamID
HAVING AVG(salary) >= 3000000
ORDER BY AvgOfSalary DESC;

그림 5. 2012년도 각 팀별 연봉(3,000,000 이상)