[SQLD 학습 자료 요약] SQL 기본 및 활용 1.7. GROUP BY, HAVING 절

본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.

SQL 전문가 가이드: 국내도서; 저자 : 한국데이터산업진흥원; 출판 : 한국데이터산업진흥원 2020.05.29

7. GROUP BY , HAVING 절

1. 집계 함수 (Aggregate Function)

집계 함수의 특성

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

COUNT(*)     >> NULL 포함한 행의 수 출력
COUNT(표현)  >> NULL 제외한 행의 수 출력

SUM([DISTINCT | ALL] 표현식) >> NULL 제외한 합계 출력
AVG([DISTINCT | ALL] 표현식) >> NULL 제외한 평균 출력

MAX([DISTINCT | ALL] 표현식) >> 최대값 출력
MIN([DISTINCT | ALL] 표현식) >> 최소값 출력

STDDEV([DISTINCT | ALL] 표현식) >> 표준 편차 출력
VARIAN([DISTINCT | ALL] 표현식) >> 분산 출력

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

SELECT COUNT(*)              "전체 행수",
       COUNT(HEIGHT)         "키 건수",
       MAX(HEIGHT)           최대키,
       MIN(HEIGHT)           최소키,
       ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER;

2. GROUP BY 절

GROUP BY 절은 SQL 문에서 FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 추가로 사용된다.

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 절 뒤에 위치한다.

SELECT POSITION 포지션, AVG(HEIGHT) 평균키 FROM PLAYER; "ERROR 발생"

GROUP BY 절에서 그룹 단위를 표시해 주어야 SELECT 절에서 그룹 단위의 칼럼과 집계 함수를 사용할 수 있다.

SELECT POSITION 포지션, AVG(HEIGHT) 평균키
FROM PLAYER
GROUP BY POSITION 포지션;  "ERROR 발생"

칼럼에 대한 ALIAS 는 SELECT 절에서 정의하고 ORDER BY 절에서는 재활용할 수 있지만, GROUP BY 절에서는 ALIAS 명을 사용할 수 없다는 것을 보여 주는 사례이다.

3. HAVING 절

SELECT POSITION 포지션, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER
WHERE AVG(HEIGHT) >= 180  "ERROR 발생"
GROUP BY POSITION;

WHERE 절에는 AVG()라는 집계 함수는 사용할 수 없다. WHERE 절은 FROM 절에 정의된 집합(주로 테이블)의 개별 행에 WHERE 절의 조건절이 먼저 적용되고, WHERE 절의 조건에 맞는 행이 GROUP BY 절의 대상이 된다. 그런 다음 결과 집합의 행에 HAVING 조건절이 적용된다. 결과적으로 HAVING 절의 조건을 만족하는 내용만 출력된다. 즉, HAVING 절은 WHERE 절과 비슷하지만 그룹을 나타내는 결과 집합의 행에 조건이 적용된다는 점에서 차이가 있다.

SELECT POSITION 포지션, ROUND(AVG(HEIGHT), 2) 평균키
FROM PLAYER
GROUP BY POSITION
HAVING AVG(HEIGHT) >= 180;

GROUP BY 절과 HAVING 절의 순서를 바꾸어서 수행하더라도 문법 에러도 없고 결과물도 동일한 결과를 출력한다. 그렇지만, SQL 내용을 보면, 포지션이란 소그룹으로 그룹핑(GROUPING)되어 통계 정보가 만들어지고, 이후 적용된 결과 값에 대한 HAVING 절의 제한 조건에 맞는 데이터만을 출력하는 것이므로 논리적으로 GROUP BY 절과 HAVING 절의 순서를 지키는 것을 권고한다.

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

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

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

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

월별 입사자의 급여 데이터를 확인한다.

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

최종적으로 리포트 작성을 위해 GROUP BY 절과 AVG 집계 함수를 사용한다.

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

5. 집계 함수와 NULL

다중 행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL 이 나오고 전체 건수 중에서 일부만 NULL 인 경우는 NULL 인 행을 다중 행 함수의 대상에서 제외한다.

CASE 표현 사용시 ELSE 절을 생략하게 되면 Default 값이 NULL 이다. NULL 은 연산의 대상이 아닌 반면, SUM(CASE MONTH WHEN 1 THEN SAL ELSE 0 END)처럼 ELSE 절에서 0(Zero)을 지정하면 불필요하게 0 이 SUM 연산에 사용되므로 자원의 사용이 많아진다. 같은 결과를 얻을 수 있다면 가능한 ELSE 절의 상수값을 지정하지 않거나 ELSE 절을 작성하지 않도록 한다. 같은 이유로 Oracle 의 DECODE 함수는 4 번째 인자를 지정하지 않으면 NULL 이 Default 로 할당된다.

리포트 가독성을 위해 NVL 함수로 위의 SQL 문장을 수정한다.

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

<null> 값을 모두 0으로 교체하여 리포트 가독성을 높인 결과 데이터

↓SQL 전문가 가이드 요약 목록

1장. 데이터 모델링의 이해

Part 1. 데이터 모델링의 이해

Part 2. 데이터 모델과 성능

2장. SQL 기본 및 활용

따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.

저작자표시 비영리 동일조건

'데이터 사이언스 > SQL' 카테고리의 다른 글

[SQLD 학습 자료 요약] SQL 기본 및 활용 1.9. 조인 (JOIN) (0)	2020.11.24
[SQLD 학습 자료 요약] SQL 기본 및 활용 1.8. ORDER BY 절 (0)	2020.11.23
[SQLD 학습 자료 요약] SQL 기본 및 활용 1.6. 함수 (FUNCTION) (0)	2020.11.22
[SQLD 학습 자료 요약] SQL 기본 및 활용 1.5. WHERE 절 (0)	2020.11.22
[SQLD 학습 자료 요약] SQL 기본 및 활용 1.4. TCL (0)	2020.11.22

배울 수 있는 모든 것

[SQLD 학습 자료 요약] SQL 기본 및 활용 1.7. GROUP BY, HAVING 절

7. GROUP BY , HAVING 절

1. 집계 함수 (Aggregate Function)

2. GROUP BY 절

3. HAVING 절

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

5. 집계 함수와 NULL

↓SQL 전문가 가이드 요약 목록

1장. 데이터 모델링의 이해

Part 1. 데이터 모델링의 이해

Part 2. 데이터 모델과 성능

2장. SQL 기본 및 활용

Part 1. SQL 기본

Part 2. SQL 활용

Part 3. SQL 최적화 기본원리

'데이터 사이언스 > SQL' 카테고리의 다른 글

티스토리툴바

[SQLD 학습 자료 요약] SQL 기본 및 활용 1.7. GROUP BY, HAVING 절

7. GROUP BY , HAVING 절

1. 집계 함수 (Aggregate Function)

2. GROUP BY 절

3. HAVING 절

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

5. 집계 함수와 NULL

↓SQL 전문가 가이드 요약 목록

1장. 데이터 모델링의 이해

Part 1. 데이터 모델링의 이해

Part 2. 데이터 모델과 성능

2장. SQL 기본 및 활용

Part 1. SQL 기본

Part 2. SQL 활용

Part 3. SQL 최적화 기본원리

'데이터 사이언스 > SQL' 카테고리의 다른 글

'데이터 사이언스/SQL' Related Articles

티스토리툴바