mysql group by 절 : 그룹별로 나눠서 집계한다.

 그룹 별로, 예를 들어서 카데고리 별로, 아니면 분반 별로 평균을 구하거나, max 값을 구할 수 없을까요? 그룹 별로 집계하기 위해서 group by 절을 씁니다. mysql을 설치하면 sakila 데이터베이스가 있는데요. 이는 영화 대여 시스템에 필요한 테이블들을 구축한 것으로 보입니다.

 

 JOIN문도 연습하기 좋은 듯 싶으니, 오늘 예제는 여기서 써 보도록 하겠습니다.

 

 

---

 

 먼저, film_category에서 데이터들을 뽑아봅시다.

 

 

 그러면 영화의 ID랑, 카데고리의 ID가 있네요. 여기에서 저는, 카데고리 별로 몇 개의 영화를 대여점에서 가지고 있는지를 그룹화 해서 출력하고 싶어요. 그러면 어떻게 해야 할까요? 무엇을 기준으로 group 하는 건가요? 카데고리 별로. category_id인가요? 네. 그러면, group by category_id로 묶어버리면 됩니다.

 

 

 그리고 우리는 category_id와 _count를 출력을 하는데요. 이 count 함수는, relation에서 총 몇 개의 레코드가 있는지 리턴을 해 주는 함수입니다. 그런데 우리는 groupping을 했습니다. 카데고리 id를 기준으로요. 그러면, 각 카데고리 당, 몇 개의 영화가 있는지를 나타내 줄 겁니다.

 

 

 쿼리의 실행 결과는 다음과 같습니다. 그런데, 실제로 어떤 카데고리인지 잘 모르겠단 말이죠. 예를 들어서, id가  1일 때, 호러인지, 아니면 id가 3일 때 공포인지, 액션인지, 우리가 알 길이 없습니다. 

 

 

 카데고리 테이블에서 내용들을 모두 추출해 봅시다.

 

 

 그러면, 각 카데고리 ID에 대해서, 분류의 name이 나옵니다. 예를 들어, 애니메이션, 게임, 호러, 액션 같은 것들이 나타납니다. 그러면, 이 category 테이블하고, 필름 카데고리 테이블 2개를 잘 가져오면 될 거 같지 않나요? 그러면 from 절에, 필름 카데고리와, 카데고리가 들어가면 될 겁니다.

 

 

 그렇게 뽑아오면, 우리는 두 테이블의 카티션 곱을 결과값으로 가질 거에요. 그런데 여기서, film.category_id와, 카데고리의 ㅑid 값이 같은 경우만 결과 값으로 뽑아 왔습니다. 그러면, film 테이블에 있는 카데고리 id를 V_id, 카데고리에 있는 id를 T_id라 했을 때, 결과 테이블은 <V_id, T_id>로 표현할 수 있을 거에요.

 

 여기서 V_id = T_id인 경우만 표현을 할 거에요. 그러면 결론적으로 필름 카데고리의 테이블의 결과에서, 추가적인 정보 열이 더 생성이 되게 됩니다. 예를 들어, name과 last_update 같은 것들이 추가가 될 겁니다. 그 상태에서, 카데고리의 id를 기준으로 그룹핑을 했습니다.

 

  

 어떻게 될까요? 요렇게 출력이 됩니다. 정상적으로 출력 되는 것을 볼 수 있어요. 여기서 궁금한 게 있는데요. 데이터 베이스 교과서에서는, 흔히 이렇게 이야기 합니다. group by 절에 존재하지 않는 속성들은, select 절 안에 존재한다면 집계 함수에 나타나야 한다고 되어 있어요. 그런데 name은 존재하지 않았네요? 어떻게 된 걸까요?

 

 여기서 핵심은 기준인 category_id가 같은데, 다른 name 값을 가질 수 있느냐입니다. 예를 들자면, 카데고리 id와 분류 name이 <2,'horror'>, <2, 'animation'>이 나타날 수 있느냐인데요.

 

 

 카데고리의 id가 primary key로 잡혀 있어요. 그러면, sakila의 category 테이블에 id값은 중복되어서 등장하지 않을 거에요. 교과서에서 말하는 상황하고는 다른 것을 알 수 있어요. 그러면 문제가 되는 상황은 어떤 경우일까요? 예를 들어서 A를 기준으로 그룹핑을 했다고 합시다. <A,B,C> 튜플이 있을 때, A값이 같은데, B값이 다른 경우가 여럿 있다고 해 봅시다. 예를 들어 <1,3,1>, <1,4,1> 이런 경우입니다.

 

 이 때, A를 기준으로 그룹핑을 해 놓고, select A, B를 하면 어떻게 될까요? 그런 경우에 문제가 될 거에요.

 

 

 

 이것도 그냥 이렇게만 설명하면 이해가 잘 안 가실 듯 싶으니, 다른 예제를 보면서 이야기 해 보도록 하겠습니다.

 

---

 먼저 payment에서 데이터를 뽑아와 봅시다.

 

 

 이 쿼리문을 수행하면, 고객들이 대여를 한 정보를 모두 출력할 수 있게 되는데요.

 

 

 보시면, customer_id가 1인 고객이, 76번 주문과, 573번 주문과 1185번과 1422번, ... 등의 주문을 했다는 것을 알 수 있어요. 이 때, 다음과 같은 쿼리를 작성하면 어떻게 될까요?

 

 

 음. 보시면 customer_id를 기준으로 그룹을 지어주었습니다. 그런데 rental_id랑 customer_id도 같이 select 하라고 되어 있어요. 그러면 어떻게 되나요? c_id랑 r_id가 1:1 관계인가요? 아닙니다. 고객 1명이 여러 번 빌릴 수 있어요. 1:n 관계입니다. 그렇기 때문에, 정보가 손실이 되는데요.

 

 

 주문 번호 573, 1185, 1422, ... 등이 나타나지 않았음을 알 수 있어요. 이 점은 조심해야 합니다. 쿼리에서, 1:1 관계인지, 1:n 관계인지 정도는 파악하시는 게 좋습니다.

 

 

 이런 쿼리를 생각해 봅시다. 각 고객별로, 대여를 한 횟수를 구해야 합니다. 어떻게 작성하면 되나요? 어떤 걸 기준으로 그룹을 지어야 하나요? 고객. 고객 번호를 기준으로 그룹을 지어주면 됩니다. 따라서, group by에 customer_id가 들어가면 됩니다. 그리고, 무슨 함수로 집계하면 좋나요? count. 쿼리 다 작성했네요.

 

 

 각 고객별로 얼마나 많이 빌려갔는지 출력이 됩니다. 빌려갈 때 borrow rate가 있습니다. 대여 가격이 있을 거에요. 그러면 각 고객별로 얼마나 많은 돈을 냈는지도 집계할 수 있을까요?

 

 

 amount는 각 주문이 체결되었을 때, 받은 가격을 의미합니다. 그러면 id를 기준으로 그룹을 지어주고, sum(amount)를 하면, 고객별로 지불한 것의 합을 구할 수 있을 거에요.

 

 

 쿼리에 대한 결과값은 위와 같습니다. 그러면, 이 가게에서 영화를 borrow 하기 위해서, 가장 많이 돈을 쓴 고객의 id와, 가격은 어떻게 구하면 좋을까요? 아까 작성한 쿼리에서 뭘 기준으로 정렬하면 되나요? sum(amount)를 기준으로 sort 하면 되나요?

 

 

 그런데 큰 값부터 작은 값 순서로 정렬해야 하니까, 내림차순으로 들어가야 합니다. 따라서, order by rent_count desc를 하면 됩니다. 그리고, 1개 행만 출력하면 되므로 limit 1구문을 쓰시면 됩니다.

 

 

 

실행 결과는 위와 같습니다.