[MYSQL] Aggregtion(집계)과 GROUP(그룹)

집계함수: Aggregation

 

집계 함수는 말 그대로 어떤 열에 대해, 여러개의 행을 집계한 값을 반환한다.

 

SELECT COUNT(*) FROM student;

와 같이 사용하면 결과값의 행은 1개이고, 거기에 student 행의 갯수를 출력한다.

 

count(*)

3

 

단, 집계 함수를 사용하면 SELECT 출력에서 일반 열은 사용할 수 없고 집계 함수만 사용할 수 있다.

 

집계 함수의 종류

MAX(열 이름): 해당 열의 최댓값 출력

AVG(열 이름): 해당 열의 평균값 출력

MIN(열 이름): 해당 열의 최솟값 출력

 

등 여러가지가 있다.

 

집계 함수에서 DISTINCT

단순히 집계함수(열 이름)하면 모든 열에 대해 계산한 값을 하나의 행에 전달한다.

 

category	hospital_name	city
정형외과	가나다병원	서울
정형외과	마바사병원	부산
피부과	아자차병원	충청

위의 hospital 테이블에서, '병원의 종류는 몇 가지인지'를 구하려면

단순 count(category)해서는 안된다.

 

count(category)는 카테고리 열 값이 있는 행 갯수를 구하는 것이기 때문에

'병원의 종류는 몇 가지인지'를 구할 수는 없다.

(count에서 category값이 NULL인 경우 세지 않는다)

 

count(DISTINCT category)하면, category를 중복되지 않게 count해서

우리가 원하는 답인 2를 얻을 수 있게 된다.

 

GROUP BY

특정 열에 대하여 같은 값을 가진 행끼리 그룹화할 수도 있다.

 

SELECT category, COUNT(category) as hospital_count
  FROM hospital
  GROUP BY category;

GROUP BY로 그룹화할 열 이름을 입력한다.

 

GROUP BY category는 같은 카테고리를 가진 행을 그룹화한다는 것을 의미한다.

 

다시 SELECT로 돌아가보면

SELECT category, COUNT(category) as hospital_count 처럼

집계 함수와 일반 열이 같이 쓰여있는 것을 볼 수 있는데,

category는 일반 열이 아닌 '그룹화 대상 열' 이기 때문에 사용할 수 있다.

 

위 SQL문은 카테고리와, 카테고리에 속한 행 갯수를 hospital_count라는 별칭으로 출력한다.

 

category	hospital_count
정형외과	2
피부과	1

(쿼리 결과 테이블)

 

2개 이상의 열에 대한 GROUPING

 

2개 이상의 GROUP에 대해 그룹화할 수도 있다.

 

GROUP BY에 열을 나열하는데, 순서대로 대그룹, 중그룹, 소그룹... 처럼 작동한다.

 

만약 '카테고리별 병원의 갯수를 구하라'에서 '카테고리별로 지역마다의 병원의 갯수를 구하라'는 것으로 요구사항이 변하면 다음과 같이 GROUP BY에 사용되는 열의 갯수가 추가된다.

SELECT category, city, COUNT(category) as hospital_count
  FROM hospital
  GROUP BY category, city;

 

category	hospital_count	city
정형외과	1	서울
정형외과	1	부산
피부과	1	충청

(쿼리 결과 테이블)

 

이제 지역별로 어떤 종류의 병원이 몇 개나 있는지를 알 수 있게 되었다.

 

그룹화 전 조건: WHERE

열값이 원하는 값인 행일 때만 GROUP 대상에 포함할 수도 있다.

 

가령, '서울에 있는 병원의 종류 갯수'를 알고 싶다고 한다면 WHERE city='서울'을 사용한다.

SELECT category, COUNT(category) as hospital_count
  FROM hospital
  WHERE city='서울'
  GROUP BY category;

그룹화 후 조건: HAVING

그룹화하고 난 후의 결과 행에 대해서 조건적 출력이 필요할 경우

HAVING 조건절을 사용한다.

SELECT category, COUNT(category) as hospital_count
  FROM hospital
  GROUP BY category
  HAVING hospital_count>1;

여기에선 종류별 병원의 종류가 2개 이상인 병원의 종류, 갯수를 출력한다.

category	hospital_count
정형외과	2

(쿼리 결과 테이블)

 

WHERE VS HAVING

SELECT category, COUNT(category) as hospital_count
  FROM hospital
  WHERE hospital_name IS NOT NULL
  GROUP BY category
  HAVING hospital_count>1;

위 쿼리를 기준으로 설명한다.

 

WHERE는 그룹화 전의 조건문이다.

따라서 그룹화 전에 사용할 수 있는 컬럼은 hospital 테이블에 있는 category, hospital_name이다.

 

HAVING은 그룹화 후의 조건문이다.

따라서 그룹화 후에 사용할 수 있는 컬럼은 SELECT 키워드 옆에 명시된 열들인 category, hospital_count이다.

 

정렬: ORDER BY

정렬 역시 그룹화 후에 이루어지는 작업이므로,

SELECT 키워드 옆에 명시된 열들인 category, hospital_count만 사용할 수 있다.

SELECT category, COUNT(category) as hospital_count
  FROM hospital
  WHERE hospital_name IS NOT NULL
  GROUP BY category
  HAVING hospital_count>1
  ORDER BY hospital_count DESC;

복잡한 GROUP키워드가 포함된 쿼리의 해석

SELECT category, COUNT(category) as hospital_count
  FROM hospital
  WHERE hospital_name IS NOT NULL
  GROUP BY category
  HAVING hospital_count>1
  ORDER BY hospital_count DESC;

1. FROM hospital: hospital 테이블에서

2. WHERE hospital_name IS NOT NULL: hospital_name이 NULL이 아닌 행을 대상으로만

3. GROUP BY category: category 행을 기준으로 같은 값끼리 그룹화하고

4. SELECT category, COUNT(category) as hospital_count: category 컬럼과 원래 hospital 테이블에서 category에 대해 같은 값을 가지는 행의 갯수를 출력하되

5. HAVING hospital_count>1: hospital_count가 1 이상인 행만 출력하는데,

6. ORDER BY hospital_count DESC: 출력되는 테이블은 hospital_count를 기준으로 내림차순 정렬한다.

 

해석 순서가 중요한 이유는 위에서 말했듯이,

WHERE에서 사용할 수 있는 열 이름,

HAVING에서 사용할 수 있는 열 이름,

ORDER BY에서 사용할 수 있는 열 이름을 잘 알고 있어야

오류가 발생하더라도 빠르게 잡아낼 수 있기 때문이다.