즐겁게 배우는 SQL 3. GROUP BY

본 포스트는 박재호님의 유튜브 강의(링크)를 보고 일부 발췌하여 정리한 내용입니다.
사용된 자료, 샘플 데이터 등은 모두 SQLite Tutorial에서 확인할 수 있습니다.

1. GROUP BY

group by는 선택한 컬럼에서 같은 행들을 묶어준다.
또한 거기에 aggregate function(MIN, MAX, AVG, SUM 등)을 사용해 각 그룹에 대한 정보도 추가로 나타낼 수 있다.

SELECT 
    column_1,
    aggregate_function(column_2) 
FROM 
    table
GROUP BY 
    column_1,
    column_2;

group by는 위와 같이 사용할 수 있고, 만약 WHERE절이 있다면 group by는 반드시 그 뒤에 나와야 한다.

우선 간단한 예제부터 시작하자.

SELECT
	albumid,
	COUNT(trackid)
FROM
	tracks
GROUP BY
	albumid;

위와 같은 쿼리문을 통해 데이터를 앨범id별로 묶고 각 앨범의 트랙 수를 계산할 수 있다.

한 걸음 더 나아가, 테이블이 2개일 때 inner join과 함께 group by를 사용해보자.

위와 같은 데이터 구조에서 기존의 albumId와 COUNT(trackId) 외에 앨범의 제목도 같이 넣어서 확인하고 싶다면 어떻게 해야 할까?

SELECT
	tracks.albumid,
	title,
	COUNT(trackid)
FROM
	tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY
	tracks.albumid;

albumid처럼 양쪽 테이블에 있는 데이터는 앞에 테이블 명을 붙여서 데이터를 한정해야 한다.

만약 artists 테이블을 하나 더 넣어서 가수의 이름도 보고 싶다면, JOIN 포스팅에서 했던 것처럼 INNER JOIN artists ON artists.artistid = albums.artistid inner join을 한 번 더 추가해 주면 된다.

COUNT만 쓰는 것도 지겨우니 다른 aggregate function도 사용해 보자.
사용법이랄 것도 없이, count와 같은 방식으로 원하는 컬럼명을 인자로 넣어주면 알아서 계산해준다.

다음은 좀 전에 사용한 데이터에서 milliseconds 컬럼의 최대, 최소, 평균을 구하는 쿼리문이다.

SELECT
	tracks.albumid,
	title,
	min(milliseconds),
	max(milliseconds),
	round(avg(milliseconds),2)
FROM
	tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
GROUP BY
	tracks.albumid;

group by는 반드시 한 컬럼이 기준이 될 필요는 없다.
여러개의 컬럼을 기준으로 group by를 할 수도 있는데, 이 경우 대상이 되는 컬럼들을 쌍으로 하는 tuple을 만들게 된다.

SELECT
   MediaTypeId, 
   GenreId, 
   COUNT(TrackId)
FROM
   tracks
GROUP BY
   MediaTypeId, 
   GenreId;

결과를 보면 MediaTypeId와 GenreId 데이터의 쌍 (1,1), (1,2)...으로 나오는 것을 알 수 있다.

2. HAVING

select 쿼리문에서 where절을 통해 조건을 제한하듯, group by는 having절과 함께 사용함으로써 조건을 걸어 그룹을 필터링할 수 있다.

사용법은 간단한데, where처럼 group by 뒤에 필터링 하고 싶은 조건을 적어 주면 된다.

이제는 익숙한 tracks 테이블을 사용해서 albumid가 1인 그룹만 track 개수를 나타내 보면 다음과 같다.

SELECT
	albumid,
	COUNT(trackid)
FROM
	tracks
GROUP BY
	albumid
HAVING albumid = 1;

당연히 inner join에 group by를 사용한 테이블에서도 having절을 사용할 수 있다.
albumid를 기준으로 tracks 테이블의 각 milliseconds 합이 일정 숫자 이상인 데이터만을 나타내면 다음과 같다.

SELECT
	tracks.AlbumId,
	title,
	SUM(Milliseconds) AS length
FROM
	tracks
INNER JOIN albums ON albums.AlbumId = tracks.AlbumId
GROUP BY
	tracks.AlbumId 
HAVING
	length > 60000000;