[ 살펴보기 ] SQL

특정 테이블에 존재하는 user의 구매 기록을 집계하거나 올해 판매했던 제품을 특정 카테고리 별로 판매량을 집계하거나 등과 같이 특정 기준으로 원시 데이터를 통해 집계 처리를 하기 위해서 그룹화 작업이 필요하다

Mysql에서 제공하는 Sakila라는 테스트용 데이터 베이스 기준으로 그룹화 작업을 해보자.

먼저 film_actor라는 테이블에 actor_id와, film_id 그리고 update_date라는 세 가지 column이 존재한다.

SELECT actor_id, film_id FROM film_actor ORDER BY actor_id

그리고 위와 같은 query를 실행했을 때 결과는 다음과 같다

위의 결과에서 볼 수 있듯이 actor_id 1이라는 데이터를 가진 actor가 출현했던 film의 id list를 확인할 수 있다. 위의 상황에서 actor별로 출현했던 영화의 숫자를 카운트하고 싶으면 어떻게 할 수 있을까? 그럴 때는 group by를 사용해 그룹화 작업을 할 수 있다. 다음 예제를 살펴보자

SELECT actor_id, COUNT(film_id) FROM film_actor GROUP BY actor_id ORDER BY actor_id

위의 결과에서 볼 수 있듯이 group by를 통해 actor_id 데이터를 기준으로 그룹화하여 각 user_id마다 film_id가 몇 개인지 count 함수를 통해 조회하고 있다.

그룹화된 데이터 기준 필터 적용하기

위 처럼 그룹화가 된 데이터를 기준으로 조건을 적용하여 특정 데이터만 필터해내고 싶을 수도 있을 것이다. 원시 데이터가 아닌 그룹화된 데이터를 기준으로 필터를 적용하려면 where이 아닌 having으로 필터를 추가할 수 있다

만약 위의 데이터를 기준으로 영화를 30편 넘게 찍은 배우를 필터하고 싶으면 다음과 같이 처리할 수 있을 것이다

SELECT actor_id, COUNT(film_id) AS filmCount FROM film_actor GROUP BY actor_id HAVING filmCount > 30

위 예제에서는 fim_id를 count한 필드를 filmCount로 rename하고 해당 결과 값과 having statement를 통해 필터를 적용하고 있다. 그리고 그에대한 결과로 영화를 30편 넘게 찍은 배우의 data만 필터하고 있다

집계 함수 살펴보기

데이터 베이스마다 가지고 있는 집계 함수가 조금씩 다를 수 있지만 대부분 공통적으로 가지고 있는 함수는 다음과 같다

max(): 특정 데이터 내에 최대값
min(): 특정 데이터 내에 최소값
avg(): 특정 데이터 내에 평균값
sum(): 특정 데이터의 총합
count(): 특정 데이터의 전체 record 수

만약 amount라는 column을 가지고 있는 payment table에서 amount 값이 가장 큰 데이터와 가장 작은 데이터 그리고 amount의 평균값을 구하고자 한다면 다음과 같이 처리할 수 있을 것이다

SELECT MAX(amount), MIN(amount), AVG(amount) FROM payment

위의 query는 payment라는 테이블 전체 데이터에서 최대, 최소, 평균값 데이터를 조회한다. 그렇다면 user 정보별로 최대, 최소, 평균값을 나눠서 조회하고 싶으면 어떻게 해야할까? 위의 예제에서 보았듯이 이런 경우에는 user를 구분하는 column을 기준으로 그룹화한다. 그리고 payment 테이블에서 user 구분을 위해 customer_id라는 column을 사용한다

SELECT customer_id, MAX(amount), MIN(amount), AVG(amount) FROM payment GROUP BY customer_id

집계함수를 사용하여 어떤 값을 count할 때 주의할 점은 특정 column을 count로 계산을 할 때 중복 데이터를 포함할지 혹은 고유한 값만 count를 할지에 따라 query 방법이 조금 달리진다. 다음 예제를 살펴보자

SELECT count(customer_id) FROM payment

위의 query를 payment table에서 customer가 몇 명 존재하는가를 찾기위해 사용한다면 의도한 바와는 다른 결과가 나올 것이다. 왜냐하면 위의 코드는 customer_id가 중복이 되어도 payment table의 customer_id가 있는 모든 행의 숫자를 count한다.

만약에 중복 count없이 고유한 customer_id 숫자만 count하고 싶다면 다음과 같이 처리한다

SELECT count(DISTINCT customer_id) FROM payment

한 개 이상의 table에서 데이터를 그룹화하기

특정 데이터를 그룹화 할 때 한 개 이상의 table에서 데이터를 그룹화 해야할 때가 있다. 예를들어 영화배우가 영화 등급에 따라 얼마나 많은 영화를 찍었는지 확인하고 싶을 때는 다음과 같이 처리할 수 있을 것이다

SELECT film_actor.actor_id, film.rating, count(*) FROM film_actor
INNER JOIN film 
ON film.film_id = film_actor.film_id  
GROUP BY film_actor.actor_id, film.rating 
ORDER BY film_actor.actor_id

위의 예제에선 join을 통해 film과 film_actor라는 table에서 actor_id와 rating colum을 조회하고 그룹화하여 actor가 영화 등급별로 몇 개의 작품을 찍었는지 count 집계함수를 통해 조회하고 있다

위의 같은 방법을 통해 한 개 이상의 table에서 데이터를 그룹화하여 집계처리를 할 수 있다.