728x90
반응형

제 7절 GROUP BY, HAVING 절


1. 집계 함수(Aggregate Function)


여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 다중행 함수 중 집계 함수(Aggregate Function)의 특성은 다음과 같다.


- 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수

- GROUP BY 절은 행들을 소그룹화

- SELECT절, HAVING절, ORDER BY절에 사용 가능

- WHERE절에 사용하지 않음 

- 일반적으로 집계함수는 GROUP BY 절과 같이 사용되지만, 테이블 전체가 하나의 그룹이 되는 경우에는 GROUP BY 절 없이 단독으로 사용 가능 



집계함수명([DISTINCT | ALL] 칼럼이나 표현식)



- COUNT(*) 는 NULL 값을 포함한 행의 수를 출력하는 것을 주의

- COUNT(표현식)은 NULL값인 것을 제외한 행의 수를 출력함




2. GROUP BY 절


  WHERE 절을 통해 조건에 맞는 데이터를 조회하고나서, 2차 가공 정보가 필요할 수 있는데 이 때 GROUP BY 절을 사용한다. GROUP BY 절은 SQL 문에서 FROM 절과, WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용된다.


SELECT [DISTINCT] 칼럼명 [ALIAS명]

FROM 테이블명

[WHERE 조건식]

[GROUP BY 칼럼이나 표현식]

[HAVING 그룹조건식];


GROUP BY 절과 HAVING 절의 특성

- GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계함수를 사용

- 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행

- GROUP BY 절에서 SELECT 절과는 달리 ALIAS(별명)을 사용할 수 없음

- 집계함수는 WHERE 절에는 올 수 없음 ( 집계함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행)

- WHERE 절은 전체 데이터를 GROUP 으로 나누기 전에 행들을 미리 제거

- HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시

- GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력

- HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치

- GROUP BY, HAVING 절은 ORDER BY 절 없이 정렬이 불가능





3. HAVING 절


예제) K-리그 선수들의 포지션별 평균키를 구하는데, 평균키가 180 센티미터 이상인 정보만 표시


SELECT POSITION 포지션, ROUND(AVG(HEIGHT), 2) 평균키

FROM PLAYER

WHERE AVG(HEIGHT) >= 180

GROUP BY POSITION;


위 쿼리는 잘못된 쿼리이다. 즉, WHERE 절에는 AVG()라는 집계함수는 허용되지 않는다. WHERE 절은 FROM 절에 정의된 집합의 개별 행에 WHERE 절의 조건절이 먼저 적용되고, 그 조건에 맞는 행이 GROUP BY의 대상이 된다. 그 다음 HAVING 조건절이 적용된다. 즉, HAVING 조건 절을 만족하는 내용만 출력된다. 


올바른 쿼리문은 다음과 같다.


SELECT POSITON 포지션, ROUND(AVG(HEIGHT), 2) 평균키

FROM PLAYER

GROUP BY POSITON

HAVING AVG(HEIGHT) > 180;


GROUP BY절과 HAVING의 위치를 바꾸어도 결과는 같으나, 순서를 지킬 것을 권고한다.


예제) K-리그 선수들 중 삼성블루윙즈(K02)와 FC서울(K09)의 인원수는 얼마인가


SELECT TEAM_ID 팀ID, COUNT(*) 인원수

FROM PLAYER

WHERE TEAM_ID IN('K09', 'K02')

GROUP BY TEAM_ID;


SELECT TEAM_ID 팀ID, COUNT(*) 인원수

FROM PLAYER

GROUP BY TEAM_ID

HAVING TEAM_ID IN('K09', 'K02');


  GROUP BY 소그룹의 데이터 중 일부만 필요한 경우, GROUP BY 연산 전 WHERE 절에서 조건을 적용하여 필요한 데이터만 추출하여 GROUP BY 연산을 하는 방법과, GROUP BY 연산 후 HAVING 절에서 필요한 데이터만 필터링 하는 두 가지 방법을 사용할 수 있다.


  같은 실행 결과를 얻는 두 가지 방법 중 HAVING 절에서 TEAM_ID와 같은 GROUP BY 기준 칼럼에 대한 조건을 추가할 수도 있으나, 가능하면 WHERE 절에서 조건절을 적용하여 GROUP BY의 계산 대상을 줄이는 것이 효율적인 자원 사용 측면에서 바람직하다.


  또한, SQL에서 SELECT 절에서 사용되지 않은 MAX 집계 함수를 HAVING 절에서 조건절로 사용 할 수 있다. 즉, HAVING 절은 SELECT 절에서 사용되지 않은 칼럼이나 집계함수가 아니더라도 GROUP BY 절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있다. 


여기서 주의해야 할 점은 WHERE 절의 조건 변경은 대상 데이터의 개수가 변경되므로 결과 데이터 값이 변  경될 수 있지만, HAVING 절의 조건 변경은 결과 데이터 변경은 없고 출력되는 레코드의 개수만 변경될 수 있다. 






4. CASE 표현을 활용한 월별 데이터 집계


"집계함수(CASE())~GROUP BY" 기능은 모델링의 제 1정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을, 정해진 칼럼 수만큼 확장해서 집계 보고서를 만드는 유용한 기법이다. 



자세한 사항은 책을 참고하길 바람






5. 집계함수와 NULL 처리


  리포트의 빈칸을 NULL이 아닌 ZERO 로 표현하기 위해 NVL / ISNULL 함수를 사용하는 경우가 많은데, 다중 행 함수를 사용하는 경우는 오히려 불필요한 부하가 발생되므로 굳이 NVL 함수를 다중 행 함수 안에 사용할 필요가 없다.


  다중 행 함수는 입력값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서 일부만 NULL인 경우는 NULL인 행을 다중 행 함수의 대상에서 제외한다. 예를 들면 100명 중 10명의 성적이 NULL 값일 때 평균을 구하는 다중 행 함수 AVG를 사용하면 NULL 값이 아닌 90명의 성적에 대해 평균값을 구하게된다.


  CASE 표현 사용 시 ELSE 절을 생략하게 되면 Default 값이 NULL이다. NULL은 연산의 대상이 아닌 반면 ZERO를 지정해버리는 경우, 불필요하게 0이 SUM과 같은 연산에서 사용이 되므로 자원의 사용이 많아진다. 같은 결과를 얻을 수 있다면 가능한 ELSE 절의 상수 값을 지정하지 않거나 ELSE 절을 작성하지 않도록 하는 경우 NULL이 디폴트 값으로 할당 된다.


  많이 실수하는 것 들 중 하나가 Oracle의 SUM(NVL(SAL, 0)), SQL Server의 SUM(ISNULL(SAL, 0)) 연산이다. 개별 데이터의 급여가 NULL인 경우는 NULL의 특성으로 자동적으로 SUM 연산에 빠지는데, 불필요하게 NVL / ISNULL 함수를 사용해 0으로 변환시켜 데이터 건수만큼의 연산이 일어나게 하는 것은 시스템 자원을 낭비시키는 일이다. 



[예제] SIMPLE_CASE_EXPRESSION 조건 - Oracle 


SELECT TEAM_ID, 

NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 ELSE 0 END),0) FW, 

NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 ELSE 0 END),0) MF, 

NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 ELSE 0 END),0) DF, 

NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 ELSE 0 END),0) GK, 

COUNT(*) SUM 

FROM PLAYER 

GROUP BY TEAM_ID;


[예제] SIMPLE_CASE_EXPRESSION 조건 - Oracle 

CASE 표현의 ELSE 0, ELSE NULL 문구는 생략 가능, 더 짧게 SQL 문장을 작성, Default 값 = NULL


SELECT TEAM_ID, 

NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 END),0) FW, 

NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 END),0) MF, 

NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 END),0) DF, 

NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 END),0) GK, 

COUNT(*) SUM 

FROM PLAYER

GROUP BY TEAM_ID;



[예제] SEARCHED_CASE_EXPRESSION 조건 - Oracle 


SELECT TEAM_ID, 

NVL(SUM(CASE WHEN POSITION = 'FW' THEN 1 END), 0) FW, 

NVL(SUM(CASE WHEN POSITION = 'MF' THEN 1 END), 0) MF, 

NVL(SUM(CASE WHEN POSITION = 'DF' THEN 1 END), 0) DF, 

NVL(SUM(CASE WHEN POSITION = 'GK' THEN 1 END), 0) GK, 

COUNT(*) SUM

FROM PLAYER

GROUP BY TEAM_ID;


[예제] SEARCHED_CASE_EXPRESSION 조건 - SQL Server 

SELECT TEAM_ID, 

ISNULL(SUM(CASE WHEN POSITION = 'FW' THEN 1 END), 0) FW, 

ISNULL(SUM(CASE WHEN POSITION = 'MF' THEN 1 END), 0) MF, 

ISNULL(SUM(CASE WHEN POSITION = 'DF' THEN 1 END), 0) DF, 

ISNULL(SUM(CASE WHEN POSITION = 'GK' THEN 1 END), 0) GK, 

COUNT(*) SUM

FROM PLAYER

GROUP BY TEAM_ID;



728x90
반응형

'Computer Science > SQL-D' 카테고리의 다른 글

[SQLD] 20. 조인(JOIN)  (2) 2016.08.18
[SQLD] 19. ORDER BY 절  (0) 2016.08.18
[SQLD] 17. 함수 (Function)  (0) 2016.08.18
[SQLD] 16. WHERE 절  (0) 2016.08.16
[SQLD] 2016년 SQLD 시험일정 및 시험내용  (0) 2016.08.13