[MySQL] GROUP BY , HAVING, WITH ROLLUP

 

GROUP BY

칼럼들을 어떠한 조건의 그룹으로 묶어서 그룹별 집계를 보고 싶을 땐 

GROUP BY를 사용 한다. 

GROUP BY 로 그루핑 해주고 집계함수를 사용하면 
그루핑 된 각 그룹에 대해서 집계함수 들이 각각 실행된다. 

 

(GROUP BY 를 쓰지 않았을 때는 테이블 전체 row 가 하나의 그룹인 것)

 

 

 

 

예시 쿼리문 >

SELECT gender, 
          COUNT (*) AS COUNT,  #COUNT (*) 는 조회 되는 row 의 개수를 구해주는 표현식
          AVG (height),
          MIN(weight) 
FROM member 
GROUP BY gender ;

출력 결과 >

gender	COUNT	AVG (height)	MIN(weight)
male	987	179	55.8
female	1523	165	41.3

예시 쿼리문 >

SELECT 
       SUBSTRING ( address, 1, 2)  AS region,     # SUBSTRING :  address 칼럼에서 1번째 문자부터 2개의 문자만 출력하란 의미
       gender, 
       COUNT (*)

FROM member 
GROUP BY 
          SUBSTRING ( address, 1, 2) ,
          gender ;

 

 

출력 결과 >

region	gender	COUNT (*)
서울	male	51
서울	female	67
경기	male	43
경기	female	49

 

 

 

 

HAVING 

# region 이 서울인 지역만 보고싶다면? 

# gender = 'male' 인 row만 보고싶다면?

HAVING 으로 조건을 걸어줄 수 있다.

 

 

예시 쿼리문>

SELECT 
       SUBSTRING ( address, 1, 2)  AS region,     
       gender, 
       COUNT (*)

FROM member 
GROUP BY 
          SUBSTRING ( address, 1, 2) ,
          gender 
HAVING region = '서울' ;

 

출력 결과 >

region	gender	COUNT (*)
서울	male	51
서울	female	67

 

예시 쿼리문>

SELECT 
       SUBSTRING ( address, 1, 2)  AS region,     
       gender, 
       COUNT (*)
FROM member 
GROUP BY 
          SUBSTRING ( address, 1, 2) ,
          gender 
HAVING 
      region = '서울' 
      AND gender ='male' ;

 

 

출력 결과 >

region	gender	COUNT (*)
서울	male	51

칼럼들을 최초에 SELECT에서 선택할 때는 WHERE 문으로 조건을 걸어서 필터링 하면 되고,
GROUP BY 로 생성된 그룹들 중에서 다시 필터링을 하고 싶을 때는 HAVING 을 사용해서 필터링 한다.

 

 

 

 

ORDER BY 로 정렬

SELECT 
       SUBSTRING ( address, 1, 2)  AS region,     
       gender, 
       COUNT (*)
FROM member 
GROUP BY 
          SUBSTRING ( address, 1, 2) ,
          gender 
HAVING 
      region = '서울' 
      AND gender ='male' 
ORDER BY
     region ASC,
     gender DESC;

 

 

 

 

WITH ROLLUP 

각 그룹의 부분 총계 구해주는 WITH ROLLUP

 

 

예시 쿼리문>

SELECT 
       SUBSTRING ( address, 1, 2)  AS region,     
       gender, 
       COUNT (*)
FROM member 
GROUP BY 
          SUBSTRING ( address, 1, 2) ,
          gender 
WITH ROLLUP   # region 이 gender 보다 먼저 나와서 region의 부분 총계를 구해줌          
HAVING 
      region IS NOT NULL
ORDER BY
     region ASC,
     gender DESC;

 

 

출력 결과 >

region	gender	COUNT (*)
경기	male	5
경기	female	4
경기	NULL	9
서울	male	7
서울	female	1
서울	NULL	8

region 컬럼만 기준으로 해서 (gender 는 구분없이) 각 region 의 총계를 구해준 것 

 

 

 

GROUP BY 뒤 기준들의 순서에 따라 WITH ROLLUP 의 결과도 달라진다.

 

그루핑 기준이 여러개 일때는 WITH ROLLUP 이 점차적으로 넓은 범위의 부분 총계를 보여준다.

 

즉 WITH ROLLUP 은

GROUP BY 뒤에 나오는 그루핑 기준의 등장 순서에 맞춰서 계층적인 부분 총계를 보여 준다. 

 

 

 

그럼 실제 NULL 이어서 NULL 로 쓰인 것과

부분 총계를 나타내기 위해 쓰인 NULL 은 어떻게 구분할까?

 

 

위와 같은 테이블에서 region 이 실제 NULL 인건지 부분 총계 NULL 인지 구분 할 수 있나?

구분 하기 어렵다. 

 

 

(1) 이게 원래 있는 NULL을 나타내는 건지,
(2) 부분 총계임을 나타내기 위해 쓰인 NULL인 건지

이 둘을 구분할 수 있게 해주는 함수가 있다. 

바로 GROUPING 이라는 함수다.

 

 

GROUPING 함수는 그 인자를 그루핑 기준에서 고려하지 않은 부분 총계인 경우에 1을 리턴하고,

그렇지 않은 경우 0을 리턴한다. 

 

 

 

 

예시 쿼리문>

SELECT 
      YEAR (sign_up_day) AS s_year, 
      gender,
      SUBSTRING (address, 1, 2) AS region, 
      GROUPING (YEAR (sign_up_day)) ,
      GROUPING (gender) ,
      GROUPING  (SUBSTRING (address, 1, 2)),  
      COUNT (*) 
FROM member
GROUP BY  
      YEAR (sign_up_day) AS s_year, 
      gender,
      SUBSTRING (address, 1, 2) 
WITH ROLLUP
ORDER BY s_year DESC ;

 

 

출력 결과 >

s_year	gender	region	GROUPING (YEAR (sign_up_day)	GROUPING (gender)	GROUPING  (SUBSTRING (address, 1, 2))	COUNT (*)
2017	m	NULL	0	0	1	2
2017	NULL	NULL	0	1	1	3
2018	f	경기	0	0	0	1
2018	f	NULL	0	0	0	1
...	...	...	...	...	...	...
NULL	NULL	NULL	1	1	1	458

 

 

 

위 출력 결과에서 보면 >

4번째 row 가 실제로 NULL을 나타내기 위해 쓰인 NULL 이어서 GROUPING 함수가 0 리턴,

나머지 NULL 들은 부분 총계를 나타내기 위해 표시된 NULL 이어서 GROUPING 함수가 1 리턴

전체 총계를 나타내는 마지막 row에서는 모든 GROUPING 함수가 1을 리턴

 

 

 

 

GROUP BY 쓸 때 중요한 규칙

GROUP BY 사용할 때는 , SELECT 절에는 GROUP BY 뒤에서 사용한 컬럼들 또는 집계함수 (COUNT, MAX, AVG 등) 만 쓸 수 있다. 
GROUP BY 뒤에 쓰지 않은 컬럼 이름은 SELECT 뒤에 쓸 수 없다. 

SELECT 절 뒤에  GROUP BY 뒤에 쓰지않은 컬럼명을 바로 쓰는 건 안되지만 
SELECT 절 뒤에  GROUP BY 뒤에 쓰지않은 컬럼명을 집계함수의 인자로 사용하는 건 가능

 

# 아래 쿼리문 처럼 쓰는 것은 안되고

SELECT gender, 
       height 
FROM member 
GROUP BY gender ;

# 이건 가능

SELECT gender, 
       AVG (height) 
FROM member 
GROUP BY gender ;