SQLD - 과목2. (7) GROUP BY, HAVING 절

1. 집계함수 ( Aggregate Function )

▷ 집계함수

    : 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 '다중행 함수' 중 하나이다.

 

 

   < 집계함수 특성 >

      (1) 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려준다.

      (2) GROUP BY 절은 행들을 소그룹화 한다.

      (3) SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있다.

 

 

 

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

* ALL : Default 옵션이므로 생략 가능

* DISTINCT : 같은 값을 하나의 데이터로 간주할 때 사용하는 옵션

 

 

 

집계함수는 그룹에 대한 정보를 제공하므로 주로 숫자 유형에 사용된다.

MAX, MIN, COUNT 함수는 문자, 날짜 유형에도 적용이 가능하다.

 

 

 

 

 

 

※ 집계함수 종류

집계함수	사용목적
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 절 없이 단독으로 사용 가능하다.

 

(1) 전체 행수, 키 건수, 최대키, 최소키, 평균키 출력한다.

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

COUNT(*) 함수는 전체 칼럼이 NULL 인 행은 존재할 수 없기 때문에 전체 행의 개수를 출력한 것이다.

COUNT(HEIGHT) 는 HEIGHT 칼럼 값이 NULL 인건 제외된 건수이다.

 

 

 

 

 

 

 

 

 

 

---

2. GROUP BY 절

▷ GROUP BY

    : 2차 가공 정보가 필요할 때 사용.

      SQL 문에서 FROM 절과 WHERE 절 뒤에 오며,

     데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별로 통계 정보를 얻을 때 사용된다.

 

 

SELECT [DISTINCT] 칼럼명 [ALIAS명] FROM 테이블명 [WHERE 조건식] [GROUP BY 칼럼이나 표현식] [HAVING 그룹조건식] ;

 

 

 

   < GROUP BY 절  &  HAVING 절 특성 >

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

      (2) 집계함수의 통계 정보는 NULL 값의 행을 제외하고 수행한다.

      (3) GROUP BY 절에서는 SELECT 절과 달리 ALIAS 명을 사용할 수 없다.

           ( WHERE 절이 먼저 수행된다. )

      (4) WHERE 절은 전체 데이터를 GROUP 으로 나누기 전에 행들을 미리 제거시킨다.

      (5) HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.

      (6) GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터중,

          HAVING 절에서 제한 조건을 둔다.

      (7) HAVING 절은 GROUP BY 절 뒤에 위치한다.

 

 

 

 

 

 

---

(예제)

 

 

(1) 선수들의 포지션별 평균키는 어떻게 되는가.

 

 

* 오류사항 코드

 

   GROUP BY 절을 사용하지 않을 때.

SELECT POSITION, AVG(HEIGHT) 평균키 FROM PLLAYER;

ERROR: 단일 그룹의 집계 함수가 아니다.

 

 

 

 

* 올바른 코드

 

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

SELECT POSITION, AVG(HEIGHT) 평균키 FROM PLAYER GROUP BY POSITION;

 

 

 

 

 

 

(2) 포지션별 최대키, 최소키, 평균키를 출력한다.

    ( "포지션별" 이라는 소그룹의 조건 ) → GROUP BY 절 사용

SELECT POSITION, COUNT(*) 인원수, MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키,
       ROUND(AVG(HEIGHT),2) 평균키 FROM PLAYER GROUP BY POSITION;

 

 

 

 

 

 

 

---

3. HAVING 절

▷ HAVING

    :WHERE 절과 비슷하지만, 그룹을 나타내는 결과 집합의 행에 조건이 적용된다는 점이 다르다.

     HAVING 절은 결과 집합의 행에 HAVING 조건절이 적용된다.

     ( WHERE 절에는 집계 함수를 사용할 수 없다. )

     논리적으로 GROUP BY 절 뒤에 HAVING 절의 순서이다.

     순서를 바꾸어도 문법 에러가 없고, 결과물은 동일하지만 순서를 지키는 것을 권고한다.

 

 

 

---

(예제)

 

 

(1) HAVING 절을 이용해 평균키가 180cm 이상인 정보만 표시한다.

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

 

 

(2) K02 와 K09 의 인원수는 얼마인지 구한다.

    ① WHERE 절, GROUP BY 절 사용 - WHERE 절에서 조건을 적용, 데이터만 추출, GROUP BY 연산

    ② GROUP BY 절, HAVING 절 사용 - GROUP BY 연산 후, HAVING 절에서 데이터 필터링

 

SELECT TEAM_ID, COUNT(*) 인원수 FROM PLAYER WHERE TEAM_ID IN ('K09','K02') 
                                GROUP BY TEAM_ID;
                                
SELECT TEAM_ID, COUNT(*) 인원수 FROM PLAYER GROUP BY TEAM_ID
                                HAVING TEAM_ID IN ('K09','K02');

가능하면 WHERE 절에서 조건절을 적용 후 GROUP BY 의 계산 대상을 줄이는 것이 자원 사용 측면에서 효율적이다.

 

 

 

 

(3) 포지션별 평균키만 출력.. 최대키가 190cm 이상인 선수를 가지고 있는 포지션의 정보만 출력한다.

SELECT POSITION, ROUND(AVG(HEIGHT),2) 평균키 FROM PLAYER
       GROUP BY POSITION
       HAVING MAX(HEIGHT) >= 190;

 

 

 

 

 

 

 

 

 

 

 

---

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

▷ 집계 함수 (CASE( )) ~ GROUP BY

    : 모델링의 제 1 정규화로 인해 반복되는 칼럼의 경우, 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을,

      정해진 칼럼 수 만큼 확장해서 집계 보고서를 만드는 유용한 기법이다.

 

 

 

 

---

(예제)

 

STEP1. 개별 데이터 확인

STEP2. 월별 데이터 구분

STEP3. 부서별 데이터 집계

 

 

(1) 개별 입사정보에서 월별 데이터를 추출한다. ( 월별 정보가 있다면 생략 가능 )

# Oracle
SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) 입사월, SAL FROM EMP;

# SQL Server
SELECT ENAME, DEPTNO, DATEPART(MONTH, HIREDATE) 입사월, SAL FROM EMP;

 

 

(2) 추출된 월별 데이터를 Simple CASE Expression 을 이용하여 12개의 월별 칼럼으로 구분한다.

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 12 THEN SAL END M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP);

 

 

(3) 부서별로 월별 입사자의 평균 급여를 구한다.

    부서별 평균값을 구하기 위해 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 12 THEN SAL END) M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH,
      SAL FROM EMP)
GROUP BY DEPTNO;

하나의 데이터에 여러 번 CASE 표현을 사용하고 집계함수가 적용되므로 SQL 처리 성능 측면에서 나쁘다고 생각될 수 있지만,

하나의 SQL 문장으로 처리 가능하므로 DBMS 자원 활용이나 처리 속도에서 훨씬 효율적이다.

데이터의 건수가 많아질수록 처리 속도의 차이는 더 커질 수 있다.

 

 

(4) 위와 같은 내용으로 Oracle 의 DECODE 함수를 사용한다.

SELECT DEPTNO, AVG(DECODE(MONTH, 1, SAL)) M01,
               AVG(DECODE(MONTH, 2, SAL)) M02,
               ...,
               AVG(DECODE(MONTH, 12, SAL)) M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL FROM EMP)
GROUP BY DEPTNO;

더 짧은 코드가 가능하다.

 

 

 

 

 

 

 

 

 

 

---

5. 집계 함수와 NULL

▷ 집계 함수  &  NULL

    : 다중행 함수에서 NVL / ISNULL 함수는 불필요한 부하가 발생하므로 사용할 필요 없다.

      불필요하게 NVL / ISNULL 함수를 사용해 0 (ZERO) 로 변환시켜 데이터 건수만큼의 연산이 일어나는 것은 시스템 자원 낭비이다.

      100명 중 10명이 NULL 이라도,  다중행 함수 AVG를 사용하면 NULL 값이 아닌 90명의 정보의 평균값을 구한다.

      가능한 ELSE 절의 상수값을 지정하지 않거나, ELSE 절을 사용하지 않는다.

 

 

 

 

---

(예제)

 

 

(1) 팀별 포지션별 FW, MF, DF, GK 포지션의 인원수와 팀별 전체 인원수를 구하는 SQL 문장을 작성한다.

     데이터가 없는 경우 0 으로 표시한다.

# Oracle - SIMPLE_CASE_EXPRESSION 조건
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,
      COUND(*) SUM FROM PLAYER
GROUP BY TEAM_ID;

( ELSE 0 , ELSE NULL 문구는 생략 가능하다 )

 

# SQL Server - SEARCHED_CASE_EXPRESSION 조건
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,
      COUND(*) SUM FROM PLAYER
GROUP BY TEAM_ID;

( ELSE 0 문구는 생략했다 )

 

 

 

(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;

 

 

 

 

 

 

 

 

 

---