3-1. GROUP BY절
튜플을 기준 속성값에 따라 그룹별로 묶는다. 만약 HAVING절을 추가하면 원하는 특정 그룹만 선택할 수 있다.
Format
SELECT [ALL|DISTINCT] {{컬럼명 [[AS] 컬럼_별칭],}⁺|*}
FROM 테이블_리스트
[WHERE 투플_조건식]
[GROUP BY 컬럼명];집계함수를 같이 사용해서 그룹별 통계 정보를 생성한다.
❗ 참고로, 집계 함수는 WHERE절에 사용할 수 없다.
Example
SELECT POSITION 포지션, COUNT(*) 인원수, COUNT(HEIGHT) 키대상,
MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키,
ROUND(AVG(HEIGHT),2) 평균키
FROM PLAYER
GROUP BY POSITION;
포지션 DF에서 인원수가 172고 키대상이 143이라는 것은, 포지션이 DF인 선수가 총 172명이고 그 중 키가 등록되어 있는 사람이 143명이라는 뜻이다.
집계 함수
Format
집계함수명 ([ALL|DISTINCT] 컬럼명|표현식)
- COUNT(*별) : PK 컬럼 값의 개수 (전체 행의 개수) -> 애초에 PK를 COUNT하면 PK는 NULL일 수가 없기 때문에 PK 컬럼 값의 개수가 나온다.
- COUNT([DISTINCT] col) : NULL 값을 제외한 컬럼 값의 개수
- SUM(col) : NULL 값을 제외한 컬럼 값의 합계
- AVG(col) : NULL 값을 제외한 컬럼 값의 평균
- STDDEV(col) : NULL 값을 제외한 컬럼 값의 표준편차
- VARIAN(col) : NULL 값을 제외한 컬럼 값의 분산
- MIN(col) : NULL 값을 제외한 컬럼의 최소값
- MAX(col) : NULL 값을 제외한 컬럼의 최대값
그래서 구분하려면 COUNT(*별)도 해보고 COUNT(NON_PK)도 해봐야 PK/NON_PK를 알 수 있고, NULL 값이 몇개인지 알 수 있다.
이 경우, COUNT(HEIGHT) = 448 이고, HEIGHT는 NON_PK인 것을 알 수 있다.
GROUP BY절로 생성되는 임시 테이블
'그룹핑 기준 컬럼(POSITION)'과 '집계 함수에서 사용하는 컬럼(HEIGHT)'만으로 구성된 임시 테이블을 생성한다.
따라서, 이들 이외의 컬럼을 SELECT절에서 사용하면 아무 의미 없는 값이 나온다.
SELECT POSITION 포지션, COUNT(*) 인원수, COUNT(HEIGHT) 키대상,
MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키,
ROUND(AVG(HEIGHT),2) 평균키, TEAM_ID
FROM PLAYER
GROUP BY POSITION;
3-2. HAVING절
GROUP BY절로 생성한 그룹 중! HAVING절의 그룹 조건식을 만족하는 그룹만 선택한다.
Format
SELECT [ALL|DISTINCT] {{컬럼명 [[AS] 컬럼_별칭],}⁺|*}
FROM 테이블_리스트
[WHERE 투플_조건식 ]
[GROUP BY 컬럼명 [HAVING 그룹_조건식] ];WHERE절은 튜플을 필터링한다면, HAVING절은 그룹을 필터링한다.
Example
SELECT TEAM_ID 팀아이디, COUNT(*) 인원수
FROM PLAYER
GROUP BY TEAM_ID HAVING TEAM_ID IN ('K09', 'K02');
선수를 TEAM_ID 별로 묶고, 그거에 대해서 TEAM_ID가 'k09'이거나 'k02'인 그룹을 선택한다.
다음은 동명이인 수를 검색하는 코드이다.
SELECT PLAYER_NAME AS '선수이름',
COUNT(PLAYER_NAME) AS '동명이인의 인원수'
FROM PLAYER
GROUP BY PLAYER_NAME HAVING COUNT(PLAYER_NAME) >=2;HAVING절에 집계함수가 들어있다. 이 때는 WHERE절로 대체해서 쓸 수 없다. 왜냐하면 WHERE절은 집계함수를 사용할 수 없기 때문이다.
⭐ 통계치로 필터링 할 수 있는건 GROUP BY절만 가능하다!
집계함수(CASE절) + GROUP BY절 활용 1
팀 별로 각각의 생월(태어난 달)에 대한 선수의 평균 키를 구해라.
Step 1
SELECT PLAYER_NAME, TEAM_ID, BIRTH_DATE,
MONTH(BIRTH_DATE) AS MONTH, HEIGHT
FROM PLAYER;
MONTH(BIRTH_DATE)는 별칭 MONTH를 사용했다. 각 선수마다 생월과 키를 출력한다.
Step 2: Simple Case Expression
SELECT PLAYER_NAME, 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;
MONTH(BIRTH_DATE)가 1~12월일 때, 키를 M01~M12로 출력한다.
생월 별칭 컬럼을 기준으로 키를 모두 출력한다.
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;
각 컬럼에 대해 Avg 값을 구한다.
집계함수와 NULL 처리
집계함수에서는 NULL값을 갖는 튜플은 제외하고 실행한다. 그러므로 집계함수 내에서 COALESCE() 함수를 써줄 필요가 없다.
차라리 집계함수 밖에서 사용해서 NULL값 대신 0을 디스플레이 하도록 한다.
COALESCE(SUM(HEIGHT),0)집계함수(CASE절) + GROUP BY절 활용 2 (기말 기출)
팀별로 각 포지션(FW,MF,DF,GK)에 대한 인원수, 그리고 팀의 전체 인원수를 구하라. 단 데이터가 없는 경우는 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 UNDECIDED
FROM PLAYER
ORDER BY TEAM_ID, PLAYER_NAME;
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;
여기서 TEAM_ID가 PK가 된다. SUM() 집계함수 사용시 NULL 값은 제외하고 구해지니 팀별 각 포지션에 대한 인원수가 출력된다.
COUNT(*) SUM 사용 시 COUNT(TEAM_ID)를 SUM으로 출력하겠다는 것과 같다.
