[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #7

<목차>

• 집계 함수 (Aggregate Function)

• GROUP BY 절

• HAVING 절

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

• 집계 함수와 NULL

---

<< GROUP BY / HAVING 절 >>

---

1. 집계 함수 (Aggregate Function)

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

* GROUP BY 절은 행들을 소그룹화 한다.

* SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있다.

집계함수의 종류

1. 예시

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

   SELECT COUNT(*) "전체 행수", COUNT(HEIGHT) "키 건수",  MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키
   FROM PLAYER;
    
   /*
   전체 행수 키 건수 최대키 최소키 평균키 
   -------- ------- ----- -----  ------ 
   480      447     196   165    179.31 
   1개의 행이 선택되었다.
    
   [부가설명]
   전체 행수가 480인데 키 건수가 447인 이유는 키 칼럼에서 NULL 값을 가지는 행이 33개 존재하기 때문.
   평균키를 구할 때 키 값이 NULL이 아닌 447개의 행에 대해서만 평균을 구함.
   ROUND는 평균 전체 키의 평균을 소수점 이하 2번 째 자리까지 반올림한 값임.
   */

 

2. GROUP BY 절

* 사용 시기

   : WHERE 절을 통해 조건에 맞는 데이터를 조회했지만 테이블에 1차적으로 존재하는 데이터 이외에 각 팀별로 선수가 몇 명인지, 선수들의 평균 키와 평균 체중이 어떻게 되는지, 각 팀에서 가장 키가 큰 선수는 누군지 등의 소그룹별로 2차 가공 정보가 필요할 때 사용됨.

* SQL 문에서 FROM 절과 WHERE 절 뒤에 위치하며, 데이터들을 소그룹화시켜 각 소그룹별로 통계 정보를 얻을 때 사용된다.

* GROUP BY 절과 HAVING 절의 특성

• GROUP BY 절을 통해 소그룹별 기준을 정하고, SELECT 절에 집계 함수를 사용.

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

• GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없음.

• 집계 함수는 WHERE 절에 위치할 수 없음. (GROUP BY 절보다 WHERE 절이 먼저 수행되기 때문에)

• WHERE 절은 전체 데이터를 소그룹화시키기 전에 1차적으로 행들을 제거.

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

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

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

* 예시 - 문제 (GROUP BY)

   : 포지션별 최대키, 최소키, 평균키를 출력하라.

 

* 예시 - 결과

SELECT POSITION 포지션, COUNT(*) 인원수, COUNT(HEIGHT) 키대상, MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키 
FROM PLAYER 
GROUP BY POSITION;
 
/*
포지션 인원수 키대상 최대키 최소키 평균키
----- ------ -----  ----- ----- -----
DF    172    142    190   170   180.21 
FW    100    100    194   168   179.91 
MF    162    162    189   165   176.31 
.....
5개의 행이 선택되었다.
*/

 

3. HAVING 절

* 예시 - 문제
   : K-리그 선수들의 포지션별 평균키를 구하는데, 평균키가 180cm 이상인 정보만 출력하라는 요구 사항이 있을 때, 아래처럼 작성할 수 있다.

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

 하지만 집계 함수인 AVG는 WHERE 절에 위치할 수 없기 때문에 에러를 발생시킨다.

WHERE 절의 조건절이 1차적으로 적용된 후에 GROUP BY 절에서 소그룹화 되기 때문이다. 집계 함수의 적용 이후 조건을 적용하기 위해서는 HAVING 절을 사용한다.

 

* 예시 - 결과

 SQL문을 수정하면 아래와 같다.

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

---> HAVING 절과 GROUP BY 절의 순서를 바꾸어도 에러가 발생하지는 않지만 논리적으로 GROUP BY 절의 결과에서 HAVING 절의 조건에 맞는 결과물이 출력되므로, 순서를 지키는 것을 권고한다.

 

* 예시 - 문제

   : K-리그 선수들 중 K02팀과 K09팀 각각의 인원수를 구하라.

   (WHERE + GROUP BY // GROUP BY + HAVING 둘 다 사용)

 

* 예시 - 결과 (WHERE + GROUP BY)

SELECT TEAM_ID 팀ID, COUNT(*) 인원수 
FROM PLAYER 
WHERE TEAM_ID IN ('K09', 'K02') 
GROUP BY TEAM_ID; 
 
/*
팀ID 인원수 
---- ----- 
K02   49 
K09   49 
2개의 행이 선택되었다.
*/

 

* 예시 - 결과 (GROUP BY + HAVING)

SELECT TEAM_ID 팀ID, COUNT(*) 인원수 
FROM PLAYER 
GROUP BY TEAM_ID
HAVING TEAM_ID IN ('K02', 'K09');
 
/*
팀ID 인원수 
---- ----- 
K02   49 
K09   49 
2개의 행이 선택되었다.
*/

---> 비교 : 다른 SQL문이지만 결과는 동일하지만 과정이 다르다.

   - WHERE 절에서 TEAM_ID 조건을 부여하면 1차적으로 데이터가 한 번 걸러진 후에 GROUP BY 절에서 소그룹화 된다.

   - HAVING 절에서 TEAM_ID 조건을 부여하면 모든 데이터가 들어오고 GROUP BY 절에서 소그룹화 된 후에 데이터가 걸러진다.

---> 결과 : WHERE 절을 통해 1차적으로 데이터를 걸러 소그룹화 대상을 줄이는 것이 더 효율적이다.

 

* 예시 - 문제

   : 포지션별 평균키만 출력하는데, 최대키가 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개의 행이 선택되었다.
*/

[주의사항]
 WHERE 절의 조건 변경은 대상 데이터의 개수가 변경되므로 결과 데이터 값이 변경될 수 있다. 하지만 HAVING 절의 조건 변경은 결과 데이터 변경은 없고 출력되는 레코드(행)의 개수만 변경된다.

 

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

* 부서별로 월별 입사자의 평균 급여를 출력하라.

 

STEP 1. 개별 데이터 확인

/* Oracle */
SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) 입사월, SAL 
FROM EMP;
 
/* SQL Server */
SELECT ENAME, DEPTNO, DATEPART(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 
....
14개의 행이 선택되었다.
*/

 

STEP 2. 월별 데이터 구분

* 추출된 MONTH 데이터를 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 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개의 행이 선택되었다.
*/

 

STEP 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 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개의 행이 선택되었다.
*/

 

* 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 ;
 
/*
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개의 행이 선택되었다.
*/

 

---> DECODE 함수 참고

SELECT DECODE(10, 1, '1입니다', 2, '2입니다', 3, '3입니다', '기타') 결과
FROM dual;
 
/*
DECODE의 인수는
(표현식, 기준값1, 값1, 기준값2, 값2, ...., 디폴트값) 으로 구성된다.
 
표현식이 기준값1 이면 값1, 기준값2 이면 값2를 출력하는 형식이다.
 
제일 마지막에는 디폴트 값을 설정할 수 있는데 
디폴트 값이 지정되지 않을 때 디폴트 값은 자동으로 NULL값으로 처리된다.
(인자가 홀수개라면 디폴트 값은 NULL, 짝수개라면 제일 마지막 값이 디폴트 값)
*/

 

5. 집계 함수와 NULL

* NULL 값을 0으로 표현하기 위해 NVL/ISNULL 함수를 사용하는 경우가 있는데, 다중행 함수를 사용하는 경우 NVL/ISNULL이 불필요한 부하를 발생시킬 수 있다.

---> 다중행 함수에서는 입력 값으로 전체 행이 NULL이 아닌 경우에는 NULL 값은 자동으로 계산에서 제외된다. 예를 들어 반 학생이 100명인데 10명의 점수가 NULL 값이라면 SUM, AVG, MIN, MAX 등과 같은 다중행 함수의 계산 과정에서 NULL 값을 가진 10명의 데이터는 무시된다. 그렇기 때문에 굳이 SUM(NVL(SCORE, 0)) 처럼 불필요한 부하를 발생시킬 이유가 없다.

 

---> 따라서 입력 값으로 전체 행이 NULL 인 경우에만 0으로 바꾸면 되기 때문에 NVL(SUM(SCORE), 0) 으로 전체 결과가 NULL인 경우에만 0으로 표기되게끔 NVL 함수를 한 번만 처리해주면 된다.

 

* 예시 - 문제

   : 팀별 포지션별 FW, MF, DF, GK 포지션의 인원수와 팀별 전체의 인원수를 구하는 SQL 문장을 작성하라. 단, 데이터가 없는 경우는 0으로 표기한다.

 

* 예시 - 결과

/* 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;
 
/* SQL Server - NVL 함수를 ISNULL 함수로 변경 */
 
/* 
1. CASE 표현에서 THEN 1 ELSE 0 으로 표현 가능.
 
2. CASE 표현에서 Searched_Case_Expression이 아닌 
Simple_Case_Expression인 CASE POSITION WHEN 'FW' 와 같이 사용 가능
*/
 
/*
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 
....
15개의 행이 선택되었다.
*/

 

 

 

출처

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

데이터 전문가 지식포털 DBGuide.net

질문은 댓글로 남겨주시면 되겠습니다. 감사합니다.