[SQLD] SQL 기본 1-7. GROUP BY절, HAVING 절

1. 집계 함수

-여러 행의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수
-GROUP BY는 행들을 소그룹화
-SELECT, HAVING, ORDER BY절에 사용가능

데이터 분석 함수
(1) GROUP 함수 : ROLLUP, CUBE, GROUPING SETS
(2) WINDOW 함수

집계 함수명 ( [DISTINCT | ALL ] 칼럼이나 표현식)
- ALL : Default 옵션이므로 생략 가능
- DISTINCT : 같은 값을 하나의 데이터로 간주할 때 사용

집계 함수의 종류
COUNT(*)
COUNT(표현식)
SUM([DISTINCT | ALL] 표현식)
AVG([DISTINCT | ALL] 표현식)
MAX([DISTINCT | ALL] 표현식)
MIN([DISTINCT | ALL] 표현식)
STDDEV([DISTINCT | ALL] 표현식)
VARIAN([DISTINCT | ALL] 표현식)
기타 통계 함수

(3) GROUP BY 절 없이 사용 : 테이블 전체가 하나의 그룹인 경우
SELECT COUNT(*) “전체 행수”, COUNT(HEIGHT) “키 건수”,
MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER;

-> COUNT(HEIGHT)는 HEIGHT 칼럼이 NULL인 33건은 제외된 건수의 합

2. GROUP BY 절
: 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 사용
SELECT [DISTINCT] 칼럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 칼럼이나 표현식]
[HAVING 그룹조건식];

-GROUP BY 절을 통해 소그룹별 기준을 정해 SELECT 절에 집계 함수를 사용
-집계 함수의 통계는 NULL 값을 가진 행을 제외하고 수행
-GROUP BY절에서 SELECT절과 다리 ALIAS 사용 불가
-집계 함수는 WHERE절에는 올 수 없다. [GROUP BY 절보다 WHERE 절이 먼저 수행]
-WHERE절은 GROUP BY절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건 표시
-GROUP BY 절에 의한 소그룹별 집계 데이터 중 HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력
-HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치
-ORDER BY 절을 명시해야 데이터 정렬 수행

(1) K-리그 선수들의 포지션별 평균키 [GROUP BY 사용하지 않고 집계함수를 사용]
SELECT POSITON 포지션, AVG(HEIGHT) 평균키
FROM PLAYER;
SELECT POSITION 포지션, AVG(HEIGHT) 평균키 -> 단일 그룹의 집계함수가 아니다

-> 1행 오류 : GROUP BY 절에서 그룹 단위를 표시해야 그룹 단위 칼럼과 집계함수 사용 가능

(2) SELECT 절에서 사용된 포지션이라는 한글 ALIAS를 GROUP BY 절의 기준으로 사용
SELECT POSITION 포지션, AVG(HEIGHT) 평균키
FROM PLAYER
GROUP BY  POSITION 포지션;
SELECT POSITION 포지션, AVG(HEIGHT) 평균키 -> SQL 명령어 종료 오류

-> 3행 오류 : GROUP BY 절에서는 ALIAS 명 사용 불가

(3) 포지션별 최대키, 최소키, 평균키 출력
SELECT POSITION 포지션, COUNT(*) 인원수, COUNT(HEIGHT) 키대상, MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER GROUP BY POSITION;

-> ORDER BY 절이 없기 때문에 포지션 별로 정렬이 되지 않았다.
-> 포지션과 키 정보 없는 인원 3명, 포지션이 DF는 30명이 키정보가 없다. NULL 값인 경우 계산에서 제외.

3. HAVING 절

(1) K-리그 선수들의 포지션별 평균키를 구하면서, 평균키가 180 센티미터 이상인 정보만 표시
SELECT POSITION 포지션, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER WHERE AVG(HEIGHT) >= 180 GROUP BY POSITION;
WHERE AVG(HEIGHT) >= 180

-> 3행 오류 : 집계함수 사용불가
-> WHERE 절에는 AVG()라는 집계함수 사용불가
-> HAVING 절은 WHERE 절과 비슷하지만 그룹을 나타내는 결과 집합의 행에 조건이 적용

(2) 평균키가 180센티미터 이상인 정보만 표시
SELECT POSITION 포지션, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER
GROUP BY POSITION
HAVING AVG(HEIGHT) >=180;

HAVING 조건절은 GROUP BY 절에서 정의한 소그룹의 집계함수 이용한 조건 표시가능

(3) SQL 문장의 수행 순서 -> GROUP BY 절과 HAVING 절의 순서 바꾸어서 수행
SELECT POSITION 포지션, AVG(HEIGHT) 평균키
FROM PLYAER HAVING AVG(HEIGHT) >= 180
GROUP BY POSITION;

-> GROUP BY 절과 HAVING 절 순서를 바꾸어도 동일한 결과 출력
먼저 포지션 소그룹으로 그룹핑 되어 통계 정보 생성
이후 적용된 결과 값에 대한 HAVING 절 제한 조건에 맞는 데이터 출력
-> GROUP BY절과 HAVING 절 순서

(4) 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 소그룹 데이터 중 일부만 필요할 땐
1.  WHERE 절에서 조건 적용해 필요한 데이터 추출한 후 GROUP BY 연산
2. GROUP BY 연산 후 HAVING 절에서 필요한 데이터만 필터링

가능하면 WHERE 절에서 조건절 적용해 GROUP BY 계산 대상 줄이기

(5) 포지션별 평균키 출력, 최대 키가 190cm이상인 선수를 가지고 있는 포지션 정보만 출력
SELECT POSION 포지션, ROUND(HEIGHT), 2) 평균키
FROM PLAYER GROUP BY POSITION
HAVING MAX(HEIGHT) >= 190;

-> SELECT 절에서 사용하지 않는 MAX 집계함수를 HAVING 절에서 조건절로 사용
-> WHERE 절의 조건 변경은 대상 데이터의 개수가 변경
-> HAVING 절의 조건 변경은 결과 데이터 변경은 없고 출력 레코드 개수만 변경 가능

4. CASE 표현을 활용한 월별 데이터 집계
: 집계함수 ((CASE( ))~GROUP BY
모델링의 제1정규화로 인한 레코드 집합을 정해진 칼럼 수 만큼 확장해 집계 보고서 만듦
-> 부서별로 월별 입사자 평균 급여를 알아내고자 할때
/입사 후 1년마다 급여 인상이나 보너스 지급과 같은 일정이 정기적으로 잡힌다면 업무적으로 중요한 정보가 될 수 있다.

STEP 1. 개별 데이터 확인
개별 입사정보에서 월별 데이터 추출 작업 진행
SELECT ENAME, DEPTNO, EXTRACT (MONTH FROM HIREDATE) 입사월, SAL
FROM EMP;

STEP 2. 월별 데이터 구분
추출된 MONTH 데이터를 SIMPLE_CASE_EXPRESSION 이용해 12개 월별 칼럼으로 구분
SELECT ENAME, DEPTNO,
CASE MONTH THEN 1 SAL END M01, CASE MONTH WHEN 2 THEN SAL END M02,
CASE MONTH THEN 3 SAL END M03, CASE MONTH WHEN 4 THEN SAL END M04,
CASE MONTH THEN 5 SAL END M05, CASE MONTH WHEN 6 THEN SAL END M06,
CASE MONTH THEN 7 SAL END M07, CASE MONTH WHEN 8 THEN SAL END M08,
CASE MONTH THEN 9 SAL END M09, CASE MONTH WHEN 10 THEN SAL END M10,
CASE MONTH THEN 11 SAL END M11 CASE MONTH WHEN 12 THEN SAL END M12,
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP;

STEP 3. 부서별 데이터 집계
부서별 평균값 : GROUP BY 절과 AVG 집계 함수 사용
직원 개인 정보는 제외, ORDER BY 절은 사용하지 않음

SELECT DEPTNO,
AVG(CASE MONTH THEN 1 SAL END) M01,
AVG(CASE MONTH WHEN 2 THEN SAL END) M02,
AVG(CASE MONTH THEN 3 SAL END) M03,
AVG(CASE MONTH WHEN 4 THEN SAL END) M04,
AVG(CASE MONTH THEN 5 SAL END) M05,
AVG(CASE MONTH WHEN 6 THEN SAL END) M06,
AVG(CASE MONTH THEN 7 SAL END) M07,
AVG(CASE MONTH WHEN 8 THEN SAL END) M08,
AVG(CASE MONTH THEN 9 SAL END) M09,
AVG(CASE MONTH WHEN 10 THEN SAL END) M10,
AVG(CASE MONTH THEN 11 SAL END) M11,
AVG(CASE MONTH WHEN 12 THEN SAL END) M12,
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP)
GROUP BY DEPTNO;

-> 하나의 데이터에 여러번 CASE 표현 사용 후 집계 함수 적용
-> 하나의 SQL 문장으로 처리해 효율적

(+) SIMPLE_CASE_EXPRESSION으로 표현된 위의 SQL과 같은 내용으로 Oracle의 DECODE 함수를 사용한 SQL 문장 작성
SELECT DEPTNO,
AVG(DECODE (MONTH,  1, SAL)) M01,
AVG(DECODE (MONTH, 2, SAL)) M02,
AVG(DECODE (MONTH, 3, SAL)) M03,
AVG(DECODE (MONTH, 4, SAL)) M04,
AVG(DECODE (MONTH, 5, SAL)) M05,
AVG(DECODE (MONTH, 6, SAL)) M06,
AVG(DECODE (MONTH, 7, SAL)) M07,
AVG(DECODE (MONTH, 8, SAL)) M08,
AVG(DECODE (MONTH, 9, SAL)) M09,
AVG(DECODE (MONTH, 10, SAL)) M10,
AVG(DECODE (MONTH, 11, SAL)) M11,
AVG(DECODE (MONTH, 12, SAL)) M12,
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP)
GROUP BY DEPTNO;

 

5. 집계 함수와 NULL

: 다중 행 함수를 사용할 경우 NVL를 사용하지 않는다.

 

다중 행 함수는 전체 건수가 NULL 값인 경우에만 함수 결과가 NULL이며, 일부만 NULL인 경우는 제외한다.

 

-> CASE 표현 사용시 ELSE 절을 생략한다면 Default 값이 NULL이다.

NULL은 연산 대상이 아니지만 SUM처럼 ELSE절에서 0을 지정한다면 불필요한 자원의 사용이 많아진다.

 

-> 같은 값의 경우 ELSE 절에 상수값을 지정하지 않거나 ELSE 절을 작성하지 않는다.

 

-> SUM(NVL(SAL, 0))과 SUM(ISNULL (SAL, 0))연산의 경우 자동적으로 NULL이 제외되지만, 불필요하게 NVL/ISNULL 함수를 사용해 자원을 낭비 시키는 경우이다.

 

-> 리포트 출력시 NULL이 아닌 0을 표시하고 싶은 경우 NVL(SUM(SAL), 0)이나 ISNULL(SUM(SAL), 0)처럼 전체 SUM의 결과가 NULL인 경우 (즉 대상 건수가 모두 NULL인 경우) NVL/ISNULL 함수를 사용한다.

 

(1) 팀별 포지션별 인원수와 팀별 전체 인원수를 구한다. 데이터가 없는 경우 0으로 표시

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;

-----------------------------------------------------------------------------------------------------

SELECT TEAM_ID,

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

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

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

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

COUNT(*) SUM

FROM PLAYER
GROUP BY TEAM_ID;

 

-> ORDER BY 절이 적용되지 않아 TEAM_ID 별로 정렬이 되어있지 않다.

-> TEAM_ID 'K08'의 경우 POSITION이 NULL인 3건이 분류에는 빠져있고 COUNT(*)에는 추가

 

(2) GROUP BY절 없이 전체 선수들의 포지션별 평균 키 및 전체 평균키 출력

SELECT ROUND(AVG(CASE WHEN POSITION = 'MF' THEN HEIGHT END), 2) 미드필더,

ROUND(AVG(CASE WHEN POSITION = 'FW' THEN HEIGHT END), 2) 포워드,

ROUND(AVG(CASE WHEN POSITION = 'DF' THEN HEIGHT END), 2) 디펜더,

ROUND(AVG(CASE WHEN POSITION = 'GK' THEN HEIGHT END), 2) 골키퍼,

ROUND(AVG(HEIGHT), 2) 전체평균키

FROM PLAYER;