SQL 개발자(SQLD)

SQL 개발자(SQLD) 시험 노트개념 정리13 MIN

집계 함수와 GROUP BY·HAVING 절

SQLD 2과목의 여섯째 자리입니다. COUNT(*)와 COUNT(컬럼)의 차이, SUM·AVG·MAX·MIN이 NULL을 다루는 방식, GROUP BY 절의 규칙, HAVING과 WHERE가 거르는 대상의 차이, GROUP BY 없이 쓰는 집계 함수를 정리합니다.

앞 노트까지의 함수는 행마다 결과를 하나씩 냈습니다. 이번에는 여러 행을 묶어 결과 하나를 내는 함수와, 무엇을 기준으로 묶을지를 정하는 GROUP BY입니다. 이 단원의 문항은 테이블을 주고 결과 값이나 결과 행 수를 세게 하는 꼴로 물을 수 있어, NULL이 섞인 데이터에서 직접 세어 보는 것이 가장 확실한 준비입니다.

이번에는 행을 둘 늘린 사원 테이블을 씁니다. 부서코드가 NULL인 사원이 한 명 있습니다.

사원명 부서코드 급여 상여
홍길동 D01 300 50
김철수 D01 250 NULL
이영희 D02 400 100
박민수 D02 350 NULL
최지은 D03 200 30
정대현 NULL 280 NULL

집계 함수

여러 행에서 값 하나

집계 함수는 여러 행의 값을 받아 결과 하나를 돌려주는 함수입니다. 다섯이 기본입니다.

함수 결과 위 테이블의 급여
COUNT 행 수 6
SUM 합계 1780
AVG 평균 약 296.67
MAX 최댓값 400
MIN 최솟값 200

SUM과 AVG는 숫자에만 쓰지만 MAX·MIN·COUNT는 문자와 날짜에도 씁니다. 문자의 MAX는 사전 순서에서 가장 뒤의 값이고 날짜의 MAX는 가장 늦은 날짜입니다.

DISTINCT와 함께 쓰기

집계 함수의 괄호 안에 DISTINCT를 넣으면 같은 값을 하나로 줄인 뒤 셉니다. COUNT(DISTINCT 부서코드)는 D01·D02·D03 세 값을 세어 3입니다. NULL은 값이 아니라서 여기서도 빠집니다.

COUNT와 NULL

COUNT(*)와 COUNT(컬럼)

COUNT는 괄호 안에 무엇을 넣느냐로 세는 대상이 갈립니다. COUNT(*)는 행 자체를 세므로 NULL이 든 행도 셉니다. COUNT(컬럼)은 그 컬럼에 값이 있는 행만 셉니다.

식 결과 이유
COUNT(*) 6 행이 여섯
COUNT(부서코드) 5 정대현의 NULL을 뺀다
COUNT(상여) 3 NULL 셋을 뺀다
COUNT(DISTINCT 부서코드) 3 중복과 NULL을 뺀다

집계 함수의 NULL 처리

COUNT(*)를 뺀 집계 함수는 모두 NULL을 건너뛰고 계산합니다. 이것이 평균에서 결과를 가릅니다.

AVG(상여)는 값이 있는 세 행만 보아 (50 + 100 + 30) / 3 = 60입니다. 상여가 없는 사람을 0으로 보고 여섯 명의 평균을 내려면 앞 노트의 NVL로 먼저 바꿔 AVG(NVL(상여, 0))으로 적어야 하고, 그 값은 180 / 6 = 30입니다. 같은 데이터에서 60과 30이 나뉘므로 보기에 둘이 함께 서 있을 수 있습니다. SUM(상여) / COUNT(*)도 180 / 6이라 30입니다.

반면 앞 노트에서 본 급여 + 상여 같은 행 안의 산술은 NULL에 전염됩니다. 행 안에서는 전염, 행 사이에서는 건너뜀이라고 갈라 두면 SUM(급여 + 상여)와 SUM(급여) + SUM(상여)가 다른 이유도 보입니다. 앞쪽은 상여가 NULL인 세 행의 합이 먼저 NULL이 되어 빠지므로 홍길동·이영희·최지은의 350 + 500 + 230 = 1080이고, 뒤쪽은 1780 + 180 = 1960입니다.

GROUP BY 절

그룹을 나누는 기준

GROUP BY 컬럼은 그 컬럼 값이 같은 행끼리 묶고, 집계 함수는 묶인 그룹마다 결과를 하나씩 냅니다.

SELECT 부서코드, COUNT(*), SUM(급여)
  FROM 사원
 GROUP BY 부서코드;
-- D01   2   550
-- D02   2   750
-- D03   1   200
-- NULL  1   280

부서코드가 NULL인 정대현은 버려지지 않고 NULL끼리 한 그룹이 됩니다. 그래서 결과는 네 행입니다. WHERE에서 NULL이 조용히 빠지던 것과 다른 동작이라 행 수를 셀 때 놓치기 쉽습니다. 그리고 GROUP BY는 결과를 정렬해 준다는 약속을 하지 않으므로, 차례가 필요하면 다음 노트의 ORDER BY를 따로 적어야 합니다.

SELECT 목록의 규칙

GROUP BY를 쓰면 SELECT 목록에는 그룹 기준 컬럼이나 집계 함수만 올 수 있습니다. 위 문장에 사원명을 더하면 오류입니다 — D01 그룹에는 홍길동과 김철수가 함께 있어서 한 행에 어느 이름을 찍을지 정할 수 없기 때문입니다. 반대로 기준 컬럼을 SELECT에 안 적는 것은 됩니다.

또 Oracle에서는 GROUP BY 절에 SELECT에서 붙인 별칭을 쓸 수 없습니다. GROUP BY 절이 SELECT보다 먼저 처리되기 때문인데, 이 처리 순서는 다음 노트에서 한 줄로 정리합니다.

HAVING과 WHERE

거르는 대상

두 절 모두 조건으로 거르지만 거르는 대상이 다릅니다. WHERE는 그룹을 만들기 전의 행을 거르고, HAVING은 그룹을 만든 뒤의 그룹을 거릅니다.

구분 WHERE HAVING
거르는 것 행 그룹
처리 시점 GROUP BY 앞 GROUP BY 뒤
집계 함수 조건 못 쓴다 쓴다

WHERE SUM(급여) > 500은 오류입니다. 행을 거르는 시점에는 그룹이 아직 없어 합계를 낼 수 없기 때문입니다. 집계 결과로 거르려면 HAVING SUM(급여) > 500으로 적습니다. HAVING의 조건에는 SELECT 목록에 없는 집계 함수도 쓸 수 있습니다.

두 절을 함께 쓸 때

같은 「두 명 이상인 부서」라도 WHERE가 먼저 행을 덜어 내면 그룹의 크기가 달라집니다.

-- ㉠
SELECT 부서코드, COUNT(*) FROM 사원
 GROUP BY 부서코드 HAVING COUNT(*) >= 2;
-- D01 2 / D02 2

-- ㉡
SELECT 부서코드, COUNT(*) FROM 사원
 WHERE 급여 >= 300
 GROUP BY 부서코드 HAVING COUNT(*) >= 2;
-- D02 2

㉡은 급여 300 이상인 홍길동·이영희·박민수만 남긴 뒤 묶으므로 D01이 한 명이 되어 HAVING에서 빠집니다. 그룹을 만들기 전에 걸 수 있는 조건은 WHERE에 두는 편이 묶을 행이 줄어 처리도 가볍습니다.

GROUP BY 없는 집계

테이블 전체가 한 그룹

GROUP BY를 안 적고 집계 함수를 쓰면 테이블 전체를 한 그룹으로 보아 결과가 한 행입니다. 그래서 이때도 SELECT 사원명, SUM(급여) FROM 사원처럼 일반 컬럼을 섞으면 오류입니다. HAVING도 GROUP BY 없이 쓸 수 있고, 그러면 전체 한 그룹에 조건을 걸어 한 행 또는 0행이 나옵니다.

행이 하나도 없을 때

조건에 맞는 행이 하나도 없어도 GROUP BY가 없으면 결과는 한 행입니다. 그 한 행에서 COUNT(*)는 0이고 SUM·AVG·MAX·MIN은 NULL입니다.

SELECT COUNT(*), SUM(급여) FROM 사원 WHERE 급여 > 1000;
-- 0   NULL

SELECT 부서코드, COUNT(*) FROM 사원 WHERE 급여 > 1000
 GROUP BY 부서코드;
-- (결과 없음)

GROUP BY가 붙으면 만들 그룹이 없으므로 0행입니다. 「결과 행 수」를 묻는 문항에서 1과 0이 이렇게 갈립니다.

연습 문제

  1. 위 사원 테이블에서 COUNT(*), COUNT(상여), COUNT(DISTINCT 부서코드)를 차례로 구하면?
    ① 6, 6, 4
    ② 6, 3, 3
    ③ 5, 3, 3
    ④ 6, 3, 4
    ②. COUNT(*)는 행 여섯, 상여는 값이 있는 셋, 부서코드는 NULL을 뺀 서로 다른 값 셋입니다.
  2. 위 사원 테이블에서 AVG(상여)와 AVG(NVL(상여, 0))을 차례로 구하면?
    ① 30, 30
    ② 60, 60
    ③ 60, 30
    ④ 30, 60
    ③. 앞쪽은 값이 있는 세 행의 평균 180 / 3, 뒤쪽은 NULL을 0으로 바꾼 여섯 행의 평균 180 / 6입니다.
  3. 집계 함수에 대한 설명으로 옳지 않은 것은?
    ① COUNT(*)는 NULL이 든 행도 센다
    ② MAX는 문자 컬럼에도 쓸 수 있다
    ③ SUM은 NULL을 0으로 보고 더한다
    ④ 집계 함수는 WHERE 절의 조건에 쓸 수 없다
    ③. SUM은 NULL을 0으로 바꾸는 것이 아니라 건너뜁니다. 합계에서는 같아 보여도 AVG에서는 나누는 수가 달라집니다.
  4. 위 사원 테이블에서 아래 문장의 결과 행 수는?
    SELECT 부서코드, AVG(급여) FROM 사원
     GROUP BY 부서코드 HAVING AVG(급여) >= 270;
    ① 1
    ② 2
    ③ 3
    ④ 4
    ③. 그룹별 평균이 D01 275, D02 375, D03 200, NULL 280이므로 270 이상은 D01·D02·NULL 그룹 셋입니다. NULL도 한 그룹이라는 점을 놓치면 2로 셉니다.
  5. 다음 중 오류가 나는 문장은?
    ① SELECT COUNT(*) FROM 사원 GROUP BY 부서코드
    ② SELECT 부서코드, MAX(급여) FROM 사원 GROUP BY 부서코드
    ③ SELECT 사원명, SUM(급여) FROM 사원 GROUP BY 부서코드
    ④ SELECT SUM(급여) FROM 사원 HAVING SUM(급여) > 1000
    ③. 사원명은 그룹 기준도 집계 함수도 아닙니다. ①처럼 기준 컬럼을 안 적는 것과 ④처럼 GROUP BY 없이 HAVING을 쓰는 것은 됩니다.
  6. 위 사원 테이블에서 아래 문장의 결과로 옳은 것은?
    SELECT 부서코드, COUNT(*) FROM 사원
     WHERE 상여 IS NOT NULL
     GROUP BY 부서코드;
    ① D01 2, D02 2, D03 1
    ② D01 1, D02 1, D03 1
    ③ D01 1, D02 1, D03 1, NULL 1
    ④ D01 2, D02 2, D03 1, NULL 1
    ②. 상여가 있는 홍길동·이영희·최지은만 남은 뒤 묶이므로 세 그룹에 한 명씩입니다. 정대현은 상여가 NULL이라 WHERE에서 빠집니다.
  7. 위 사원 테이블에서 SUM(급여 + 상여)의 결과는?
    ① 1960
    ② 1780
    ③ 1080
    ④ NULL
    ③. 상여가 NULL인 세 행은 덧셈이 먼저 NULL이 되어 합계에서 빠지므로 350 + 500 + 230입니다.
  8. 행이 하나도 남지 않는 조건을 건 SELECT COUNT(*), MAX(급여) FROM 사원 WHERE 1 = 2의 결과는?
    ① 결과 행이 없다
    ② 0, 0 한 행
    ③ 0, NULL 한 행
    ④ NULL, NULL 한 행
    ③. GROUP BY가 없으면 전체가 한 그룹이라 한 행이 나오고, COUNT는 0을, 나머지 집계 함수는 NULL을 돌려줍니다.

다음 노트는 결과를 늘어놓는 차례를 정하는 ORDER BY와, 지금까지 본 절들이 실제로 처리되는 순서입니다. 이 노트에서 GROUP BY에 별칭을 못 쓰는 이유와 WHERE에 집계 함수를 못 쓰는 이유가 그 순서 한 줄로 함께 풀립니다.

SQL 개발자(SQLD) 시험 노트 전체 보기