NULL이 있는 SUM과 AVG

부주의한 지원자를 곤란하게 만드는 고전적인 면접 질문이 있습니다. "일부 값이 NULL인 급여 열이 있습니다. AVG(salary)는 무엇을 계산하며, 그것이 업무상 원하는 값입니까?"

이 질문에 대한 솔직한 답은 집계 함수가 NULL을 무시한다는 사실을 이해하는지 보여 줍니다. 이 때문에 평균의 분모가 달라집니다. 이를 운영 환경에서 잘못 처리하면 보고되는 평균이 모르는 사이에 부풀려집니다.

이 동작을 명확하게 살펴보겠습니다.

이 수업 전체에서 NULL을 허용하는 bonus 열이 있는 employees 테이블을 사용하겠습니다.

• 앨리스, 보너스 100
• 밥, 보너스 200
• 캐럴, 보너스 NULL
• 댄, 보너스 300

네 개의 행, NULL이 아닌 보너스 세 개, NULL 하나입니다. 이 데이터를 대상으로 SUM과 AVG를 실행하며 NULL이 어떻게 처리되는지 살펴보겠습니다.

SUM(bonus)은 NULL이 아닌 값만 더합니다. 100 + 200 + 300 = 600입니다. NULL인 행은 아무것도 더하지 않습니다. 단순히 건너뛰며, 산술적으로 개수를 변경하는 의미에서 0으로 처리되는 것은 아닙니다.

실질적인 효과는 NULL이 없는 것처럼 처리하는 것과 같습니다. SUM은 NULL 때문에 오류를 내지 않으며, 모든 입력이 NULL인 경우가 아니면 NULL을 반환하지 않습니다.

AVG(bonus)가 핵심입니다. NULL이 아닌 값의 합을 NULL이 아닌 값의 개수로 나누어 계산합니다. 600 / 3 = 200입니다.

분모는 4가 아니라 3입니다. NULL인 행은 분자와 나눗셈의 분모에서 모두 제외됩니다. 이것이 바로 AVG가 사람들을 당황하게 하는 이유입니다. 평균은 모든 행이 아니라 값이 있는 행을 대상으로 계산됩니다.

NULL인 보너스의 업무상 의미가 "보너스를 받지 않음" = 0이라고 가정해 보십시오. 그러면 실제 평균은 600 / 4 = 150이어야 하지만 AVG(bonus)는 200을 보고합니다.

면접에서 올바른 답은 다음과 같습니다. "AVG는 NULL을 무시하므로 보너스가 있는 직원만 대상으로 평균을 계산합니다. NULL이 0을 의미한다면 먼저 NULL을 0으로 변환해야 합니다." 이 차이를 명확히 말하는 것이 점수를 얻는 부분입니다.

NULL을 0으로 처리하여 모든 행을 대상으로 평균을 계산하려면 열을 COALESCE(bonus, 0)으로 감싸십시오. 이제 모든 행에 숫자 값이 있으므로 분모는 4가 됩니다.

결과는 600 / 4 = 150입니다. 핵심은 다음과 같습니다. AVG(col)과 AVG(COALESCE(col, 0))은 서로 다른 업무상 질문에 답합니다. 의도에 맞게 선택하십시오.

유용한 등식이 하나 있습니다. AVG(col)은 SUM(col) / COUNT(col)와 같습니다 — COUNT(*)가 아니라 COUNT(col)이라는 점에 주의하십시오. AVG와 해당 COUNT는 모두 NULL을 건너뛰기 때문입니다.

실수로 SUM(col) / COUNT(*)를 작성하면 전체 행을 기준으로 한 평균인 150을 얻게 되며, 이는 AVG의 결과인 200과 다릅니다. 면접관은 올바른 COUNT를 선택하는지 확인하기 위해 AVG를 직접 재구성해 보라고 요청하기도 합니다.

평균을 직접 계산할 때 발생할 수 있는 미묘한 오류가 있습니다. 많은 데이터베이스에서는 두 정수를 나누면 정수 나눗셈이 수행되어 소수점 이하가 잘립니다. 7 / 2의 결과가 3.5가 아니라 3이 될 수 있습니다.

AVG 자체는 대개 소수를 반환하지만, 정수 열에 대해 SUM / COUNT로 다시 계산하면 정밀도를 잃을 수 있습니다. 먼저 1.0을 곱하거나 소수 유형으로 변환하십시오.

면접관이 좋아하는 경계 사례가 있습니다. 모든 값이 NULL이거나 필터와 일치하는 행이 없으면 어떻게 될까요?

• NULL이 아닌 입력이 없을 때 SUM은 0이 아니라 NULL을 반환합니다.
• AVG도 NULL을 반환합니다. 개수가 0인 값으로 나누는 것은 정의되지 않기 때문입니다.
• 반면 COUNT는 0을 반환합니다.

숫자 기본값이 필요하다면 결과를 COALESCE(SUM(col), 0)으로 감싸십시오.

동일한 NULL 규칙은 GROUP BY 내부에서도 적용됩니다. 각 그룹의 AVG는 해당 그룹에서 NULL이 아닌 값의 개수로 나눕니다. 보너스가 전부 NULL인 그룹에서는 해당 그룹의 AVG가 NULL이 됩니다.

따라서 부서별 평균이 예상과 다를 때는 조인 오류를 의심하기 전에 NULL 때문에 각 그룹의 분모가 줄어든 것은 아닌지 확인하십시오.

잘 다듬어진 면접 답변은 다음과 같습니다. "SUM과 AVG는 모두 NULL을 무시합니다. AVG는 NULL이 아닌 값의 개수로 나누므로 NULL은 결과적으로 분모를 줄입니다. NULL을 0으로 처리해야 한다면 집계하기 전에 COALESCE로 변환합니다. 그렇지 않으면 평균은 값이 있는 행만 반영합니다."

이 한 문장으로 정확성, 업무 관점의 이해, 해결 방법을 모두 보여 줄 수 있습니다.

샘플 데이터에 이 규칙을 적용해 보십시오.

NULL이 있는 경우 SUM과 AVG에 관한 핵심 내용:

• 두 함수 모두 NULL을 완전히 무시합니다.
• AVG(col) = SUM(col) / COUNT(col) — 분모에서 NULL이 제외됩니다.
• NULL이 0을 의미하고 계산에 포함해야 한다면 COALESCE(col, 0)을 사용하십시오.
• 모든 입력이 NULL이거나 행이 없으면 SUM과 AVG는 NULL을 반환합니다. (COUNT는 0을 반환합니다.)
• AVG를 직접 재구성할 때 정수 나눗셈에 주의하십시오.

다음은 MIN, MAX 및 숫자가 아닌 데이터의 집계입니다.