그루핑

그루핑

그루핑은 row들을 여러개의 그룹으로 나누는 것이다.

GROUP BY

GROUP BY 기준컬럼

SELECT gender FROM school.member GROUP BY gender;

gender
m
f

겉으로 봤을땐 결과값이 DISTINCT()와 비슷하지만 내부적으로 봤을 때 완전히 다르다.
gender의 데이터가 m과 f두종류가 있다고 하면 m과 f가 출력되는데 내부적으론 gender컬럼의 값이 m인 모든값이 m에 들어가고, gender컬럼의 값이 f인 모든값이 f에 들어가 있다고 보면된다.

SELECT 
    gender,
    COUNT(*)
FROM school.member 
GROUP BY gender;

COUNT를 해보면

gender	COUNT(*)
m	7
f	5

이런식으로 테이블 전체가 아니라 gender의 m그룹과 f그룹에 각각 실행되어 카운트를 하는것을 볼 수 있다.

만약 row마다 고유한 값을 가진 컬럼을 기준으로 그룹핑을 하면 아무의미가 없어진다.
이럴때는 주요 단어를 추려서 그룹핑하면 된다.

SELECT 
    SUBSTRING(address, 1, 2) AS region,
    COUNT(*)
FROM school.member 
GROUP BY SUBSTRING(address, 1, 2);

region	COUNT(*)
서울	5
경남	3

이런식으로 SUBSTRING을 사용하여 유의미한 그룹핑을 할 수 있다.

그룹핑 기준에는 여러개의 컬럼을 사용 가능하다.

SELECT 
    SUBSTRING(address, 1, 2) AS region,
    gender,
    COUNT(*)
FROM school.member 
GROUP BY 
    SUBSTRING(address, 1, 2),
    gender;

region	gender	COUNT(*)
서울	m	3
서울	f	2
경남	f	2
경남	m	1

이렇게 address를 기준으로 그룹핑하고 거기서 gender를 기준으로 또 그룹핑한다.
데이터가 보기 힘들게 나왔을때는 ORDER BY를 사용해 깔끔하게 정렬해주자.

HAVING

원하는 그룹의 데이터만 조회하고 싶을때 사용한다.

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

region	gender	COUNT(*)
서울	m	3

region컬럼의 값이 ‘서울’이고 gender컬럼의 값이 ‘m’인 데이터만 조회하게 된다.

HAVING과 WHERE

HAVING은 이미 조회된 row들을 다시 그룹핑했을때 생성된 그룹들 중에서 다시 필터링 할때 쓰는 구문이고,
WHERE는 테이블에서 맨처음 row들을 조회할때 조건을 설정하기 위한 구문이니 적절한 구문을 선택해야 오류가 나지 않는다.
각 절의 실행 순서를 보면 확실하게 이해할 수 있다.

ONLY_FULL_GROUP_BY

sql_mode라는 시스템 변수에 ONLY_FULL_GROUP_BY가 설정되어 있다면 지켜야할 규칙이 있다.
SELECT절에는 GROUP BY뒤에서 사용한 컬럼들 또는 집계함수만 사용할 수 있다.

SELECT 
    SUBSTRING(address, 1, 2) AS region,
    age,
    gender,
    COUNT(*)
FROM school.member 
GROUP BY 
    SUBSTRING(address, 1, 2),
    gender;

위의 SQL문을 보면 GROUP BY뒤에 age컬럼이 쓰이지 않았지만 SELECT절에 작성이 된것을 볼 수 있는데 이러면 에러가 나게된다.
GROUP BY로 그룹핑 한 값들은 하나의 값이 아니라 여러 값들이 존재하는 것인데, 그룹핑 기준으로 사용하지 않은 컬럼을 SELECT로 조회하려고 하면, 각 그룹의 row중에서 어떤 row를 가져와야 하는지 알 수가 없기 때문이다.
하지만 집계함수는 사용이 가능한데 특정 row를 사용하는게 아니라 그룹내의 모든 row를 사용하기 때문에 에러가 나지 않는다.
그래서 SELECT에 GROUP BY에서 쓰이지 않는 컬럼을 바로 사용할 수는 없지만 집계함수의 인자로서 사용하는 것은 문제가 없다. 예시) SELECT AVG(age) ~

실행 순서

각 절의 작성 순서

SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT

각 절의 실행 순서

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT

FROM - 어느 테이블을 대상으로 할지 정하고
WHERE - 해당 테이블에서 조건을 만족하는 row를 선별한다.
GROUP BY - row들을 그루핑한다.
HAVING - 그루핑 후 생성된 그룹중에서 조건을 만족하는 그룹을 선별.
SELECT - 모든 또는 특정컬럼을 조회. SELECT에서 alias를 사용했다면 이후 단계(ORDER BY, LIMIT)부터 해당alias 사용가능.
ORDER BY - 각 row를 기준에 따라서 정렬.
LIMIT - 일정 갯수의 row를 추린다.

위 순서는 SQL표준이고 모든 DBMS에서 SQL표준을 지키지 않기 때문에 편차가 있을 수 있다.

ROLLUP

SELECT 
    SUBSTRING(address, 1, 2) AS region,
    gender,
    COUNT(*)
FROM school.member 
GROUP BY 
    SUBSTRING(address, 1, 2),
    gender
WITH ROLLUP
ORDER BY
    region ASC,
    gender DESC;

region	gender	COUNT(*)
경남	m	1
경남	f	2
경남	null	3
서울	m	3
서울	f	2
서울	null	5
null	null	8

ROLLUP은 세부 그룹들을 좀 더 큰 단위의 그룹으로 합쳐 부분 총계를 보여준다.
GROUP BY뒤 기준들의 순서에 따라 WITH ROLLUP의 결과도 달라진다.
그리고 그룹핑 기준이 여러개 일때는 점차적으로 넓은 범위의 부분 총계를 보여준다.

GROUPING

ROLLUP으로 총계를 나타낼때 애초에 NULL이 들어있던 컬럼들도 NULL로 나타나게 되는데, 이러면 이 값이 원래 있던 NULL을 나타내는 건지 부분총계를 나타내는 NULL인지를 구분할 수가 없게된다.
이럴때 GROUPING을 사용하면 되는데 실제 NULL을 나타내기 위해 쓰인 NULL에는 0을 리턴하고 부분총계를 나태니기 위해 표현된 NULL은 1을 리턴하게 된다.

SELECT 
    GROUPING(gender),
    GROUPING(SUBSTRING(address, 1, 2)),
    COUNT(*)
FROM school.member 
GROUP BY
    gender,
    SUBSTRING(address, 1, 2)
WITH ROLLUP;