메뉴 건너뛰기

bysql.net

제7절_GROUP_BY_HAVING_절

2011.09.29 08:14

실천하자 조회 수:7653

 

1. 집계함수(Aggregate Function)

  • 특성

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

 

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

 

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

 

 

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

- ALL : Default 옵션이므로 생략 가능함
- DISTINCT : 같은 값을 하나의 데이터로 간주할 때 사용하는 옵션임

 

  • 자주 사용되는 주요 집계

              - 주로 숫자 유형에 사용

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

              

 

[예제]     테이블 전체가 하나의 그룹이 되는 경우 - GROUP BY 절 없이 단독으로 사용 가능

SELECT    COUNT(*) "전체 행수",

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


 

전체행수  키건수  최대키  최소키  평균키

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

480       447     196     165     179.31

 

1개의 행이 선택되었다.

 

- COUNT(HEIGHT)는 NULL값이 아닌 키(HEIGHT) 칼럼의 건수만 출력

 

 

 

 

2. GROUP BY 절

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

 

SELECT [DISTINCT] 칼럼명 [ALIAS명]

FROM 테이블명

[WHERE 조건식]

[GROUP BY 칼럼(Column)이나 표현식]

[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 절을 사용하지 않고 집계 함수를 사용했을 때

K-리그 선수들의 포지션별 평균키

 

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

FROM      PLAYER;

 

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

1행에 오류:

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

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

 


 

[예제]     SELECT 절에서 사용된 ALIAS를 GROUP BY 절 사용

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

FROM      PLAYER

GROUP BY POSITION 포지션;

 

GROUP BY POSITION 포지션 *

3행에 오류:

ERROR: SQL 명령어가 올바르게 종료되지 않았다.

- GROUP BY 절에서는 ALIAS 명을 사용할 수 없다

 


 

[예제]    

포지션별 최대키, 최소키, 평균키를 출력한다. (포지션별이란 소그룹의 조건을 제시하였기 때문에 GROUP BY 절을 사용한다.)


SELECT    POSITION 포지션, COUNT(*) 인원수, COUNT(HEIGHT) 키대상, MAX(HEIGHT) 최대키,

                  MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키

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

 

5개의 행이 선택되었다.

- ORDER BY 절이 없기 때문에 포지션 별로 정렬은 되지 않았다.

- 일부 데이터베이스의 과거 버전에서 데이터베이스가 GROUP BY 절에 명시된 칼럼의 순서대로 오름차순 정렬을 자동으로 실시(비공식적인 지원이었음)하는 경우가 있었으나, 원칙적으로 ORDER BY 절을 명시해야 데이터 정렬이 수행된다. (관계형 데이터베이스 환경)


 

3. HAVING 절 

 

[예제]    

K-리그 선수들의 포지션별 평균키를 구하는데, 평균키가 180 센티미터 이상인 정보만 표시하라는 요구 사항이 접수되었으므로 WHERE 절과 GROUP BY 절을 사용해 SQL 문장을 작성한다.


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

FROM      PLAYER

WHERE    AVG(HEIGHT) >= 180

GROUP BY POSITION;

 

WHERE AVG(HEIGHT) >= 180 *

3행에 오류:

ERROR: 집계 함수는 허가되지 않는다.

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

- WHERE 절은 FROM 절에 정의된 집합(주로 테이블)의 개별 행에 WHERE 절의 조건절이 먼저 적용되고,

  WHERE 절의 조건에 맞는 행이 GROUP BY 절의 대상이 된다.

  그런 다음 결과 집합의 행에 HAVING 조건절이 적용 


[예제]    

HAVING 조건절에는 GROUP BY 절에서 정의한 소그룹의 집계 함수를 이용한 조건을 표시할 수 있으므로, HAVING 절을 이용해 평균키가 180 센티미터 이상인 정보만 표시한다.


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

FROM      PLAYER

GROUP BY POSITION

HAVING AVG(HEIGHT) >= 180;


포지션 평균키

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

GK     186.26

DF     180.21

 

2개의 행이 선택되었다.


 

[예제]     GROUP BY 절과 HAVING 절의 순서를 바꾸어서 수행


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

FROM     PLAYER

HAVING AVG(HEIGHT) >= 180

GROUP BY POSITION;


포지션 평균키

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

GK     186.26

DF     180.21

 

2개의 행이 선택되었다.


 

- 문법 에러도 없고 결과물도 동일한 결과 출력

- 그룹핑(GROUPING)되어 이후 적용된 결과 값에 대한 HAVING 절의 제한 조건에 맞는 데이터만을 출력하는 것이므로 논리적으로 GROUP BY 절과 HAVING 절의 순서를 지키는 것을 권고



 

[예제]     GROUP BY 소그룹의 데이터 중 일부만 필요한 경우

K-리그의 선수들 중 삼성블루윙즈(K02)와 FC서울(K09)의 인원수는 얼마인가란 요구 사항이 접수되었다. WHERE 절과 GROUP BY 절을 사용한 SQL과 GROUP BY 절과 HAVING 절을 사용한 SQL을 모두 작성한다.


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

FROM      PLAYER

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

GROUP BY TEAM_ID;

 

팀ID 인원수

---- -----

K02   49

K09   49

 

2개의 행이 선택되었다.

 

- 방법 1 : GROUP BY 연산 전 WHERE 절에서 조건을 적용하여 필요한 데이터만 추출하여 GROUP BY 연산

- 방법 2 : GROUP BY 연산 후 HAVING 절에서 필요한 데이터만 필터링

- 가능하면 WHERE 절에서 조건절을 적용하여 GROUP BY의 계산 대상을 줄이는 것이 효율적인 자원 사용 측면에서 바람직


 

 

[예제]     SELECT 절에서 사용하지 않는 집계 함수를 HAVING 절에서 조건절로 사용한 사례

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


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

FROM      PLAYER

GROUP BY POSITION

HAVING MAX(HEIGHT) >= 190;


포지션 평균키

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

GK     186.26

DF     180.21

FW     179.91

 

3개의 행이 선택되었다.


 

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

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



 

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

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

집계보고서를 만드는 기법.

 

      - 부서별로 월별 입사자의 평균 급여를 출력하는 요구 사항이 있다.

  • STEP1. 개별 데이터 확인  : 개별 입사정보에서 월별데이터를 추출 (월별정보가 있다면 생략 가능)

[예제]    

Oracle

SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) 입사월, SAL

FROM EMP;


SQL Server

SELECT ENAME, DEPTNO, DATEPART(MONTH, HIREDATE) 입사월, SAL

FROM EMP;

 

SQL Server

SELECT ENAME, DEPTNO, MONTH(HIREDATE) 입사월, SAL

FROM EMP;


ENAME    DEPTNO 입사월   SAL

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

SMITH        20     12   800

ALLEN        30      2  1600

WARD         30      2  1250

JONES        20      4  2975

MARTIN       30      9  1250

BLAKE        30      5  2850

CLARK        10      6  2450

SCOTT        20      7  3000

KING         10     11  5000

TURNER       30      9  1500

ADAMS        20      7  1100

JAMES        30     12   950

FORD         20     12  3000

MILLER       10      1  1300

 

14개의 행이 선택되었다.

 

  • STEP2. 월별 데이터 구분  : 추출된 월별데이터를 case표현을 이용해서 12개의 월별 칼럼으로 구분

[예제]    

추출된 MONTH 데이터를 Simple Case Expression을 이용해서 12개의 월별 칼럼으로 구분한다. 실행 결과에서 보여 주는 ENAME 칼럼은 최종 리포트에서 요구되는 데이터는 아니지만, 정보의 흐름을 이해하기 위해 부가적으로 보여 주는 임시 정보이다. FROM 절에서 사용된 인라인 뷰는 2장 4절에서 설명한다.


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


ENAME  DEPTNO M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12

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

SMITH     20                                   800

ALLEN     30    1600           

WARD      30    1250           

JONES     20          2975         

MARTIN    30                         1250    

BLAKE     30             2850        

CLARK     10                2450       

SCOTT     20                   3000      

KING      10                               5000  

TURNER    30                     1500    

ADAMS     20               1100      

JAMES     30                                   950

FORD      20                                  3000

MILLER    10 1300            

 

14개의 행이 선택되었다.

 

 

  • STEP3. 부서별 데이터 집계  : 부서별 평균 급여값 구함

[예제]    

최종적으로 보여주는 리포트는 부서별로 월별 입사자의 평균 급여를 알고 싶다는 요구사항이므로 부서별 평균값을 구하기 위해 GROUP BY 절과 AVG 집계 함수를 사용한다. 직원 개인에 대한 정보는 더 이상 필요 없으므로 제외한다. ORDER BY 절을 사용하지 않았기 때문에 부서번호별로 정렬이 되지는 않았다.


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 ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL

              FROM EMP)

GROUP BY DEPTNO ;


 

DEPTNO M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12

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

30       1425      2850         1375       950

20             2975      2050            1900

10    1300            2450            5000  

 

3개의 행이 선택되었다.


 

- 하나의 데이터에 여러 번 CASE 표현을 사용하고 집계 함수가 적용되므로 SQL 처리 성능 측면에서 나쁜 것이 아니냐는 생각을 할 수도 있다. 그렇지만, 같은 기능을 하는 리포트를 작성하기 위해 위 방법을 활용하면 복잡한 프로그램이 아닌 하나의 SQL 문장으로 처리 가능하므로 DBMS 자원 활용이나 처리 속도에서 훨씬 효율적이다.

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



 

[예제]     Oracle의 DECODE 함수를 사용하여 Simple Case Expression으로 표현된 위의 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 ;

 

 

     * case표현과 oracle의 DECODE 함수는 표현상 장단점이 있으므로 선택은 사용자의 몫 

 

5. 집계 함수와 NULL 처리

  • 다중행 함수에 NVL함수(NULL 처리)를 사용하면 부하 발생 --> 굳이 사용할 필요 없음.
  • 다중 행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서 일부만 NULL인 경우는 NULL인 행을 다중 행 함수의 대상에서 제외한다.
    (100명 중 10명의 성적이 NULL 값일 때 다중 행 함수 AVG를 사용하면 90명의 성적에 대해서 평균값) 
  • CASE 표현 사용시는 ELSE 절을 생략하면 Default 값이 NULL. 같은 결과를 얻을 수 있다면 가능한 ELSE 절의 상수값을 지정하지 않거나 ELSE절을 작성하지 않도록 한다.
  • Oracle DECODE 는 4번째 인자를 지정하지 않으면 Default 값이 NULL.
    잘못된 예 )  oracle - SUM(NVL(SAL,0)),  SQL Server - SUM(ISNULL(SAL,0))
    0표시 ) NVL(SUM(SAL),0)   이나  ISNULL(SUM(SAL),0) 
  •  

[예제]    

팀별 포지션별 FW, MF, DF, GK 포지션의 인원수와 팀별 전체 인원수를 구하는 SQL 문장을 작성한다. 데이터가 없는 경우는 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;


 

TEAM_ID  FW  MF  DF  GK SUM

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

K14       0   1   1   0   2

K06      11  11  20   4  46

K13       1   0   1   1   3

K15       1   1   1   0   3

K02      10  18  17   4  49

K12       1   0   1   0   2

K04      13  11  18   4  46

K03       6  15  23   5  49

K07       9  22  16   4  51

K05      10  19  17   5  51

K08       8  15  15   4  45

K11       1   1   1   0   3

K01      12  15  13   5  45

K10       5  15  13   3  36

K09      12  18  15   4  49

 

15개의 행이 선택되었다.


 

[예제]    

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;


미드필더 포워드 디펜더 골키퍼 전체평균키

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

176.31   179.91 180.21 186.26 179.31

 

1개의 행이 선택되었다.


 

 

 

 

   ※ 자료출처 : DBguide.Net

http://www.dbguide.net/db.db?cmd=view&boardUid=148404&boardConfigUid=9&categoryUid=216&boardIdx=132&boardStep=1


  • SQL 개발자 자격증 (bysql.net 2011년 2차 스터디)
  • 작성자: 이주영 (suspace)
  • 최초작성일: 2011년 09월 28 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^