#7 [DML] MySQL의 다중행 함수중 GROUP BY, HAVING, ORDER BY, LIMIT 사용하기

본 포스트는 개인 스터디에 대한 정리 및 기록의 용도로써, 오개념이 존재 할 수 있습니다. 글은 상시 수정되며, 지적사항에 대해서 검토 후 수정하겠습니다.

본 포스트는 한국데이터진흥원 발간, SQL 전문가 가이드 2013Edition을 참고하였습니다.

일부 자료에서 국민대학교 김혁만 교수님의 수업자료를 발췌하여 사용했습니다.

---

이번 포스트에서는 다중행 함수에 대하여 정리해 볼 거다. 다중행 함수를 알아보기 전에, 다중행 함수의 종류와 앞서 설명했었던 #4 에서 SELECT을 정리하면서, 쿼리문들의 실행순서에 대해서 설명했었는데, 그점을 다시 짚어보고 정리를 시작하겠다. 단일행 함수와 다중행 함수의 차이점은 생각해보면 쉽다. 단일행 함수는 한 행 한행 실행하면서 그 결과를 보여주는 반면, 다중행 함수는 여러 행들을 다 보고나서 함수를 적용하고 계산한다. 예를 들어, 평균(AVG)을 구하고 싶다. 평균을 구하려면 일단 모든 행들을 다 훓어보면서, 구하고자하는 value들과 총 몇개의 대상이 존재하는지 살펴 봐야한다. 하나의 데이터만 보고 평균을 구할 수 없다는 것이다. 이게 다중행 함수와 단일행 함수의 차이다. 하나만 볼것인가, 전체를 볼것인가

 

---

쿼리문의 실행 순서

점점 쿼리문이 많아지기 때문에, 쿼리문의 실행 순서를 올바르게 숙지하고 있어야만 에러없이 원하는 데이터를 추출 할 수 있을것이다. 특히 MySQL은 에러를 표기하지 않고 NULL값이나 자체적으로 예외케이스들에 대해서 처리를 해주는 경우가 있는데, 이런 경우 내가 원하는 데이터와 다르지만 에러가 나지 않아 인지하지 못하고 넘어 갈 수도 있다.

쿼리문의 작동 순서이다.

GROUP BY 와 ORDER BY에 실행 순서를 다시금 기억하면서 공부해 보자.

 

---

 

1. 집계(집단)함수 (Aggregate Functions)

SELECT 절, HAVING 절, ORDER BY 절에 사용 할 수 있다.

WHERE절에서는 사용이 불가능 하다. -> 튜플에 조건을 다는 절이기 때문에

 

-집계 함수의 종류

COUNT(*)	행의 갯수 / wildcard(*)를 사용했기 때문이다.
COUNT(col)	col 값이 NULL값을 제외한 행의 개수
SUM(col)	col이 NULL 값인 것을 제외한  합계
AVG(col)	col이 NULL 값인 것을 제외한 평균
STDDEV(col)	col이 NULL 값인 것을 제외한 표준편차
VARIAN(col)	col이 NULL 값인 것을 제외한 분산
MIN(col)	col에서 가장 작은 최소값
MAX(col)	col에서 가장 큰 최대값

COUNT()를 사용할때 PR키를 가진 열에 대해서 사용하면, 해당 열은 NULL 값을 가진 값이 없기 때문에 COUNT(*)와 동일하게 모든 행의 총 갯수를 구할수 있다.

--PR키에 관해서는 COUNT는 전체 행의 갯수를 구할 수 있다.
SELECT COUNT(PLAYER_ID) AS 'PLAYER_ID',
	COUNT(*) AS 'COUNT(*)'
FROM PLAYER;


--PR키가 아닌 경우 NULL값의 데이터는 무시한다.
SELECT COUNT(PLAYER_ID) AS 'PLAYER_ID',
	HEIGHT(*)
FROM PLAYER;

실행 결과

이번에는 평균키를 구하는 방법에서, 이차이가 어떤 결과를 낳는지 봐보겠다.

--NULL 값에 신경써야하는 이유
SELECT ROUND(SUM(HEIGHT)/COUNT(*),1) AS ' 잘못된 평균키',
	ROUND(SUM(HEIGHT)/COUNT(HEIGHT),1) AS ' 올바른 평균키',
	ROUND(AVG(HEIGHT),1) AS 'AVG 이용한 평균키'
FROM PLAYER;

COUNT(*)와 COUNT(HEIGHT)의 차이점은 NULL값을 셌느냐 세지 않았느냐이다. 당연히 NULL값의 선수는 연산되면 안되지만, 나눠질때 그점을 고려하지 않았기 때문에 다른 결과를 보인다. 그렇기때문에 NULL값에 대해서 유의해야 한다는 것이다.

유의하자

---

2. GROUP BY 절

WHERE 조건절 다음 3번째 순서로 실해되는 GROUP BY절은 튜플들을 기준 속성 값에 따라 그룹별로 묶는 기능을 제공한다. 꼭 짚고 넘어가야 할 점은, 그룹화 하고 SELECT 한다는 점이다. 뒤에서 ORDER BY를 볼때 이런 순서때문에 생기는 에러가 있기 때문에 미리 설명했다.

예제를 보면서 정리해 보자.

 

#포지션 별 데이터 분류

--GROUP BY
SELECT POSITION AS '포지션', COUNT(*) AS '인원수', COUNT(HEIGHT) AS '키 측정 대상',
	MAX(HEIGHT) AS '최대 키', MIN(HEIGHT) '최소 키', ROUND(AVG(HEIGHT),1) AS '평균 키'
FROM PLAYER
GROUP BY POSITION;


--TEAM_ID 에 대한 SELECT는 에러이다
SELECT POSITION AS '포지션', COUNT(*) AS '인원수', COUNT(HEIGHT) AS '키 측정 대상',
		MAX(HEIGHT) AS '최대 키', MIN(HEIGHT) '최소 키', ROUND(AVG(HEIGHT),1) AS '평균 키', TEAM_ID
FROM PLAYER
GROUP BY POSITION;

흐름대로 정리하자면 먼저 PLAYER 테이블에 접근하여 POSITION이 같은 데이터들에 대하여 하나의 그룹으로 연결 시킨다. 그 과정속에서 DF,FW,NULL,MF,GK로 그룹화 될것이고, 이 그룹들에 대하여 SELECT에 집계연산자들이 적용된다.

왼쪽 임시테이블을 기준으로 오른쪽 테이블이 출력 됐다.

이때 GROUP BY절을 실행 하면 해당 그룹으로만 이루어진 임시 테이블이 생선된다. 이 임시 테이블은 집단 함수에서 사용하는 컬럼만으로 구성된다.  TEAM_ID를 SELECT 하려 하지만 그룹핑 된 임시테이블에는 TEAM_ID 가 없기 때문에 오류가 난다. 참고한 강의 문서에서 MySQL에서는 에러를 표현하지 않고 가장 첫번째 값이 나온다고 했으나, 실습 해본 결과 에러가 출력됐다.

---

3.HAVING 절

GROUP BY에 의해서 만들어진 절 중에서 그룹 조건식을 주어 조건을 만족하는 그룹을 골라 내는 문이다.

아주 작은 관점에서 보면 WHERE와 비슷한 역할은 하는데 그 대상이 튜플이냐, 그룹이냐의 차이이고, 집단함수의 사용 유무이다.

 

#예제를 통하여 알아보자.

--HAVING절을 사용하지 않음.
SELECT TEAM_ID AS '팀 아이디' , COUNT(*) AS '인원 수'
FROM PLAYER
GROUP BY TEAM_ID;

--HAVING절을 사용하여 그룹을 걸러 주었다.
SELECT TEAM_ID AS '팀 아이디' , COUNT(*) AS '인원 수'
FROM PLAYER
GROUP BY TEAM_ID HAVING TEAM_ID IN ('K09','K02');

실행 결과

 

그룹화에 조건식을 주어, 조건에 맞는 그룹들만 그룹화 되게 하였다. 당연하게도 같은 결과를 WHERE에 조건을 주어서 출력 할 수있다.

--HAVING 대신 WHERE 사용하기
SELECT TEAM_ID AS '팀 아이디', COUNT(*) AS '인원수'
FROM PLAYER
WHERE TEAM_ID IN ('K09','K02')
GROUP BY TEAM_ID;

위와 같은 결과가 나온다. 이때 속도의 차이가 있는데 쿼리문의 실행 순서를 생각해보면, WHERE절이 GROUP BY보다 먼저 실행 되기 때문에 1차로 걸러준 값에 대해서는 GROUP BY을 실행해주기 때문에 더 빠르다고 볼 수 있다.

 

#동명이인 구하기 예제

SELECT PLAYER_NAME AS '선수 이름', COUNT(*) AS ' 동명이인의 수'
FROM PLAYER
GROUP BY PLAYER_NAME HAVING COUNT(PLAYER_NAME) >= 2;

슬슬 헷갈리기 시작한다..

 

#팀 별 각각의 생월에 대해 선수의 평균 키를 구함.

--STEP 1 
SELECT PLAYER_NAME AS '선수 이름', MONTH(BIRTH_DATE) MONTH, HEIGHT
FROM PLAYER;

--STEP 2
SELECT PLAYER_NAME AS '선수 이름', TEAM_ID,BIRTH_DATE,
	CASE MONTH(BIRTH_DATE) WHEN 1 THEN HEIGHT END M01,
    CASE MONTH(BIRTH_DATE) WHEN 2 THEN HEIGHT END M02,
    CASE MONTH(BIRTH_DATE) WHEN 3 THEN HEIGHT END M03,
    CASE MONTH(BIRTH_DATE) WHEN 4 THEN HEIGHT END M04,
    CASE MONTH(BIRTH_DATE) WHEN 5 THEN HEIGHT END M05,
    CASE MONTH(BIRTH_DATE) WHEN 6 THEN HEIGHT END M06,
    CASE MONTH(BIRTH_DATE) WHEN 7 THEN HEIGHT END M07,
    CASE MONTH(BIRTH_DATE) WHEN 8 THEN HEIGHT END M08,
    CASE MONTH(BIRTH_DATE) WHEN 9 THEN HEIGHT END M09,
    CASE MONTH(BIRTH_DATE) WHEN 10 THEN HEIGHT END M10,
    CASE MONTH(BIRTH_DATE) WHEN 11 THEN HEIGHT END M11,
    CASE MONTH(BIRTH_DATE) WHEN 12 THEN HEIGHT END M12,
    CASE WHEN MONTH(BIRTH_DATE) IS NULL THEN HEIGHT END 생일모름 
FROM PLAYER;

--STEP 3
SELECT TEAM_ID, COUNT(*) AS 선수수,
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 1 THEN HEIGHT END),2) M01,
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 2 THEN HEIGHT END),2) M02, 
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 3 THEN HEIGHT END),2) M03, 
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 4 THEN HEIGHT END),2) M04, 
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 5 THEN HEIGHT END),2) M05, 
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 6 THEN HEIGHT END),2) M06, 
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 7 THEN HEIGHT END),2) M07, 
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 8 THEN HEIGHT END),2) M08, 
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 9 THEN HEIGHT END),2) M09, 
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 10 THEN HEIGHT END),2) M10, 
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 11 THEN HEIGHT END),2) M11, 
	ROUND(AVG(CASE MONTH(BIRTH_DATE) WHEN 12 THEN HEIGHT END),2) M12, 
	ROUND(AVG(CASE WHEN MONTH(BIRTH_DATE) IS NULL THEN HEIGHT END),2) 생일모름
FROM PLAYER
GROUP BY TEAM_ID;

단계별 실행 결과

한번에 다 구하기 보다는 순서대로 생각해 보고 하나씩 조건을 맞춰가면서 구해보는걸 추천한다.

 

 

#팀별 각 포지션에 대한 인원수와, 팀 전체 인원수, 데이터가 없을 경우 0으로 표시

--STEP 1
SELECT PLAYER_NAME 선수이름, TEAM_ID,
	CASE POSITION WHEN 'FW' THEN 1 ELSE 0 END FW,
    CASE POSITION WHEN 'MF' THEN 1 ELSE 0 END MF,
    CASE POSITION WHEN 'DF' THEN 1 ELSE 0 END DF,
    CASE POSITION WHEN 'GK' THEN 1 ELSE 0 END GK,
    CASE WHEN POSITION IS NULL THEN 1 ELSE 0 END UNDECIED
FROM PLAYER;

--STEP 2
SELECT TEAM_ID,
	SUM(CASE POSITION WHEN 'FW' THEN 1 ELSE 0 END) FW,
	SUM(CASE POSITION WHEN 'MF' THEN 1 ELSE 0 END) MF,
	SUM(CASE POSITION WHEN 'DF' THEN 1 ELSE 0 END) DF,
	SUM(CASE POSITION WHEN 'GK' THEN 1 ELSE 0 END) GK,
	SUM(CASE WHEN POSITION IS NULL THEN 1 ELSE 0 END) UNDECIDED,
    COUNT(*) SUM
FROM PLAYER
GROUP BY TEAM_ID;

단계별 실행 결과

---

4. ORDER BY

오름차순으로 정렬한것인가, 내림차순으로 정렬할 것인가. SELECT 실행 후 가장 마지막에 실행됨

--오름차순(ASC)[DEFAULT], DESC(내림차순)
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER
WHERE BACK_NO IS NOT NULL
ORDER BY 백넘버 DESC, 포지션, 선수명;

등번호가 NULL이 아닌 선수들에 대하여 백넘버를 내림차순으로 정의하고, 만약 같을 시에는  포지션을 오름차순순으로 비교, 그마저 같으면 선수명으로 비교하는 로직이다.

 

*주의사항

ORDER BY절에서는 SELECT 목록에 나와 있지 않은 컬럼을 사용 할 수 있지만, GROUP BY와 함께 사용할 경우, 임시테이블이 생기기 때문에 사용 할 수 없다.

---

5. LIMIT 절

몇개를 보여줄지 결정한다.

--LIMIT 몇개만 보여줄것인지 제한 가능 LIMIT n,m
SELECT STADIUM_ID, STADIUM_NAME, SEAT_COUNT
FROM STADIUM
ORDER BY SEAT_COUNT DESC, STADIUM_NAME
LIMIT 3;

-- 11번째부터 5개를 보여줌 (11, 12, 13, 14, 15)
SELECT STADIUM_ID, STADIUM_NAME, SEAT_COUNT
FROM STADIUM
ORDER BY SEAT_COUNT DESC, STADIUM_NAME
LIMIT 10,5;

LIMIT 때문에 3개만 보여준다 ( 0, 3)

---

뭔가 오늘부터 조금 어려워진 느낌이다. 생각보다 쿼리문들이 익숙하지 않아서 예제에 나오는 데이터들을 뽑아내기 어려웠다. 조금 공부를 더 하고 예제 문제를 많이 풀어보면서 익숙해지는 시간을 가져야겠다. 특히 순서에 유의하지 않으면 실수가 나올거 같아서 꼭 숙지해야겠다는 생각이 들었다.