SQLD 3-7 ] GROUP BY, HAVING 절
❏ 집계 함수 (Aggregate Function)
- 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수.
- GROUP BY 절의 행들은 소그룹화 한다.
- SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있다.
집계함수명 ( [DISTINCT/ALL] 칼럼이나 표현식 )
DISTINCT 옵션과 ALL 옵션은 그 결과들 중복된거 한번만 표시할껀지 그냥 다 표시할껀지 선택하는거.
일반적으로 집계 함수 들은 GROUP BY 절과 같이 사용되지만 아래와 같이 테이블 전체가 하나의 그룹이 되는 경우에는
GROUP BY 절 없이 단독으로도 사용 가능하다
SELECT COUNT(*) "전체 행수", COUNT(HEIGHT) "키 건수", MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키,
ROUND(AVG(HEIGHT),2) 평균키 FROM PLAYER;
전체 행수 키 건수 최대키 최소키 평균키
---------- ---------- ---------- ---------- ----------
480 447 196 165 179.31
테이블 전체가 하나의 그룹이 된다는 말은.
이따 나오겠지만. 소규모 그룹이 아니라 COUNT 같은거 보면 테이블 전체 상대로 집계 내기 때문에.
굳이 그룹을 안해줘도 된다는 뜻.
❏ GROUP BY
- 데이터들을 작은 그룹으로 분류해서 소그룹에 대한 통계 정보를 얻을 때 추가로 사용된다.
SELECT 칼럼명
FROM 테이블명
(WHERE 조건식)
(GROUP BY 칼럼이나 표현식)
(HAVING 그룹조건식) ;
GROUP BY 절과 HAVING 절의 특징
|
예제로 가보자
K-리그 선수들의 포지션별 평균키를 구하기.
GROUP BY 절을 사용 안하고 집계함수를 써보자
select POSITION as 포지션, avg(HEIGHT) as 평균키 from PLAYER:
단일 그룹의 집계 함수가 아닙니다. 라고 에러가 뜬다.
(포지션별 평균키가 아니라 그냥 평균키 구하려면 select avg(height) from player; 하면 된다. 단독으로 사용하면 가능하다!)
(왜냐면.. 표현할 때 POSITION 칼럼에 애들 나오는데.. 옆에 칼럼 AVG 는 뭘 표시해야할까..?)
group by 절에서 그룹 단위를 표시 해줘야 집계 함수를 사용할 수 있다.
select POSITION as 포지션, avg(HEIGHT) as 평균키 from PLAYER group by POSITION:
이렇게.
그냥 select POSITION as 포지션 from PLAYER;
했으면 결과가 있는대로 다 나올거를, group by POSITION 이라고 포지션 칼럼을 그룹지어 주세요! 라고 하니까
(POSITION 에는 GK, DF, FW, MF 이렇게 4가지 가 있었다.)
GK 별로 내용들이 싹, DF 별로, FW 끼리, MF 끼리.. 다 소규모 그룹으로 짝지어준다.
그럼 하나 더 해보자
포지션별 인원수, 키대상, 최대키, 최소키, 평균키 를 출력한다. (포지션별 이라고 했으니 group으로 짝지어야겠지)
select POSITION as 포지션, count(*) as 인원수, count(height) as 키대상,
max(height) as 최대키, min(height) as 최소키, round(avg(HEIGHT),2) as 평균키
from PLAYER
group by POSITION;
| 포지션 인원수 키대상 최대키 최소키 평균키 ---------- ---------- ---------- ---------- ---------- ---------- 3 0 GK 43 43 196 174 186.26 DF 172 142 190 170 180.21 FW 100 100 194 168 179.91 MF 162 162 189 165 176.31 |
포지션이 없는 3명을 발견할 수 있고, DF 중에 키가 입력 안된애들도 30명 이라는걸 알 수 있다.
❏ HAVING
- 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수.
- 조건 역할을 하는점에 있어서 where 절과 비슷하지만. 그룹의 결과 행에 조건이 적용된다는 점에서 차이가 있다.
- 포지션별 평균 키를 구하는데, 평균키가 180 이상인 정보만 표시하라!
select POSITION as 포지션, round(avg(HEIGHT),2) as 평균키
from PLAYER
where avg(HEIGHT) >= 180
group by POSITION;
맞는것일까? 포지션 이랑 평균키 2개를 뽑아내는데, 포지션 그룹도 했고. 조건. 키의 평균이 180 이상도 맞고.
틀리다. where 절에서는 집계함수를 쓸 수 없다!!
그러면 어떻게해야할까?
select POSITION as 포지션, round(avg(HEIGHT),2) as 평균키
from PLAYER
group by POSITION
having avg(HEIGHT) >= 180;
| 포지션 평균키 ---------- ---------- GK 186.26 DF 180.21 |
having 으로 조건을 달아준다. 소규모 그룹먼저 다 지어논다음에 그 후에서야 거기서 걸러내는 결과를 보는것이다.
원래 POSITION 소규모 그룹엔 GK, MF, DF, FW 4개가 있는데 나머지 2개는 평균키가 180이 안되서 출력이 안되는것이다.
다른 예제.
K-리그의 선수들 중 K02 와 K09 의 인원수는 얼마인가?
select team_id 팀_ID, count(*) 인원수
from player
where team_id in ('K02', 'K09')
group by team_id;
이렇게 팀과 인원수는 다 뽑는데 where 조건 에서 team_id 는 K02 랑 K09 만 뽑고 그걸 그룹으로.
이렇게 해도 똑같은 말이다.
select team_id 팀_ID, count(*) 인원수
from player
group by team_id
having team_id in ('K02', 'K09);그룹으로 다 묶은다음에 조건으로 쳐내기. 근데 효율적인건 먼저 다 쳐내고 그룹묶는게.. 더 .. 좋겠다
❏ CASE 표현을 활용한 월별 데이터 집계
- CASE 표현을 써서 월별 데이터 통계 내는것을 할 수 있다.
- 부서별(DEPTNO) 월별(MONTH) 입사자의 평균(AVG) 급여(SAL) 를 알고 싶다.
select DEPTNO,
avg(case MONTH when 1 then SAL end) as M01,
avg(case MONTH when 2 then SAL end) as M02,
avg(case MONTH when 3 then SAL end) as M03,
...
...
avg(case MONTH when 12 then SAL end) as M12
from (select ENAME, DEPTNO, extract(MONTH from HIREDATE) as MONTH, SAL from EMP)
group by DEPTNO;
보면.. from 은 원래 테이블명인데 저렇게 select 로 결과를 낸 테이블을 갖다 쓴것 같다. (나중에 인라인 뷰 라고 나온다.)
그래서 MONTH 라는 칼럼(별명) 을 위에서 case 에서 쓸수 있는것이고..
마지막에 나온 결과들을 그룹 지었다.
❏ 집계함수와 NULL 처리
- 빈칸을 NULL 이 아닌 0 으로 표현하기위해 NVL, ISNULL 을 쓰곤하는데, 다중행 함수를 사용할 경우에는 NVL 을 다중 행 함수 안에사용할 필요가 없다
- SUM(NVL(SAL,0)) : 불필요하게 시스템의 자원을 낭비하는 일. SAL 이 0이면 자동으로 SUM 연산에서 빠지는데 굳이 NVL로 0을 변환..
- NVL(SUM(SAL),0) : 이게 올바른 사용