[2026-07-30 | DEV-LAB] SQLite에서 NULL 평균의 분모를 직접 검증하기

CSV에서 쉼표가 연속된 ,, 부분은 공백 문자 ‘ ‘가 아니라 길이 0인 빈 문자열 ‘’이다. SQLite로 가져온 이 값을 곧바로 숫자로 변환하면 0으로 계산되어, 측정하지 못한 거래까지 평균의 분모에 포함되는 실수가 잦다.

이는 실제 매출 0과 결측치를 구분하지 못하게 만들어 성과 지표를 과도하게 낮추는 결과를 초래한다. 이번 실습에서는 온라인과 매장 거래 6건의 데이터를 이용하여 전체 행 수와 실제 값이 존재하는 유효 측정값 수를 분리하여 확인하고, 값이 존재하는 4건에 기반한 정확한 평균 150.0을 계산하는 과정을 검증한다.

먼저 결론부터

평균은 합계를 값의 개수로 나눈 결과다. SQLite의 AVG는 NULL이 아닌 값의 개수만 나누는 수로 사용하므로, 평균과 함께 전체 행 수·유효 측정값 수·누락 건수를 확인해야 한다.

계산 결과를 외우는 것이 목표가 아니다. 아래 순서대로 입력값, 계산식, 출력값을 연결하면 같은 원리를 다른 데이터에도 적용할 수 있다.

실습 순서

1. sales.csv에서 빈 amount와 실제 매출 0이 어떻게 다른지 확인한다.
2. SQLite CLI 또는 DB Browser 중 한 가지 실행 방법을 선택한다.
3. 전체 6건과 유효 측정값 4건을 세고 평균 150.0을 직접 검산한다.
4. 채널별 응용 문제를 풀고 0 치환 평균과 원래 평균의 차이를 설명한다.

▸ 실습 정보

• 난이도: 초급
• 예상 시간: 30분
• 도구: SQLite
• 선행 지식: SELECT와 COUNT 함수의 기본 문법 · SQLite CLI 또는 DB Browser for SQLite
• 이번에 새로 배우는 표현: NULLIF(값, 비교값) · CAST(값 AS REAL)
• 검증할 결과: rows|measured|average → 6|4|150.0

실습 자료 다운로드

sales.csv

222B

practice-data.xlsx

5.7KB

DEV-LAB-2026-07-30-sqlite-null-average.zip

29.1KB

데이터만 빠르게 확인하려면 CSV 또는 XLSX를, 처음부터 끝까지 실습하려면 ZIP을 받으면 된다.

• sales.csv — 원본 형태를 확인하는 실습 입력 데이터
• practice-data.xlsx — 데이터와 열 설명을 함께 보는 엑셀 파일
• DEV-LAB-2026-07-30-sqlite-null-average.zip — 실행 코드·정답·기대 출력·슬라이드를 담은 전체 실습 묶음

ZIP 안에는 SQLite CLI용 starter.sql·solution.sql과 DB Browser용 browser-starter.sql·browser-solution.sql이 함께 들어 있다. 사용하는 환경에 맞는 두 파일만 선택하면 된다.

이제 실습 파일 안의 데이터부터 살펴본다. 코드를 실행하기 전에 각 열이 무엇을 뜻하는지 알아야 결과를 잘못 해석하지 않는다.

1단계. 데이터 이해하기

온라인·매장 채널의 거래 6건을 모사한 학습용 데이터다. NULL은 값이 아예 없는 상태이고, 빈 문자열 ‘’은 글자 수가 0인 문자열 값이다. SQLite CLI의 .import는 모든 CSV 열을 TEXT로 저장하고, DB Browser용 browser-starter.sql도 amount를 TEXT로 만든다. 따라서 두 환경 모두 시작 시 실제 NULL은 0개이고 누락 두 행은 빈 문자열 ‘’ 상태다. 뒤에서 NULLIF를 실행해야 비로소 이 두 빈 문자열이 NULL로 바뀐다. COUNT와 AVG는 NULL은 제외하지만 빈 문자열은 값으로 받아들이므로, 실제 매출 0과 누락을 구분해야 한다.

데이터 구분: 학습용 더미데이터

열 이름	뜻	예시
sale_date	거래가 기록된 날짜	2026-07-01
channel	거래가 발생한 판매 채널	online, store
amount	측정한 매출액. 길이 0인 빈값은 누락이고 0은 실제 매출 0	100, (빈값: CSV에서는 ,,), 0
measurement_status	amount가 정상 측정값인지 누락인지 알려 주는 상태	measured, missing, measured_zero

▸ sales.csv

sale_date,channel,amount,measurement_status
2026-07-01,online,100,measured
2026-07-02,online,,missing
2026-07-03,store,300,measured
2026-07-04,store,0,measured_zero
2026-07-05,online,,missing
2026-07-06,store,200,measured

데이터의 열과 값이 무엇을 뜻하는지 확인했다면 실행 환경을 고른다. 아래 두 방법 가운데 하나만 선택하면 된다.

2단계. 실행 환경 준비하기

▸ SQLite CLI

1. ZIP의 압축을 풀고 sales.csv, starter.sql, solution.sql, expected-output.txt가 같은 폴더에 있는지 확인한다. starter.sql은 일반 SQL만 담은 파일이 아니라 SQLite CLI 전용 점 명령도 함께 담고 있다.
2. 터미널 또는 PowerShell을 열고 cd 명령으로 해당 폴더로 이동한다. PowerShell에서는 dir, 일반 터미널에서는 ls를 입력해 sales.csv와 starter.sql이 현재 폴더에 실제로 보이는지 확인한다.
3. sqlite3 :memory:를 입력해 SQLite 콘솔을 연다. 화면에 sqlite> 프롬프트가 나타나면 준비가 끝난 것이다.
4. sqlite> 프롬프트에서 .read starter.sql을 입력한다. 파일 첫 줄이 기존 sales를 삭제하므로 .import는 새 테이블을 만들고, CSV 첫 행을 열 이름으로 사용해 데이터에서는 제외한다. 따라서 –skip 1 없이도 데이터는 항상 6행이며, 처음에는 rows 아래 6만 보이고 measured와 average는 비어 있어야 한다.
5. 초기 출력이 확인되면 SQLite 콘솔을 열어 둔 채 3단계로 이동한다. 파일 수정과 재실행은 3단계 2번에서 안내한다. 아직 solution.sql은 열지 않는다.

▸ DB Browser for SQLite

1. DB Browser for SQLite에서 새 데이터베이스를 누르고 저장 창에서 실습 폴더에 practice.db라는 이름으로 저장한다.
2. 상단의 SQL 실행 탭에서 폴더 모양의 SQL 파일 열기 버튼으로 browser-starter.sql을 불러온다.
3. 파일에서 – 1구역: 테이블과 데이터 초기화 주석부터 INSERT 문의 마지막 세미콜론까지 선택해 실행한다. Windows·Linux에서는 Ctrl+Enter를 쓸 수 있고, 단축키가 다르면 도구 모음의 선택 SQL 실행 버튼을 누른다.
4. – 2구역: TODO 조회 주석 아래의 SELECT 문만 선택해 실행한다. 편집기 아래 결과 표에 rows=6이고 measured와 average가 빈 값으로 나오면 초기화가 끝난 것이다.
5. TODO를 수정하는 동안에는 2구역 SELECT 문 안에 커서를 두거나 그 SELECT만 마우스로 선택한 뒤, Ctrl+Enter 또는 선택 SQL 실행 버튼을 누른다.
6. 같은 SQL 실행 탭 안에서 + 버튼으로 편집 탭을 추가해도 practice.db 연결과 sales 테이블은 유지된다.
7. browser-starter.sql을 열어 둔 채 3단계로 이동한다. 파일 수정과 SELECT 재실행은 3단계 2번에서 안내한다. 아직 browser-solution.sql은 열지 않는다.

▸ DB Browser 초기화 SQL

ZIP을 열지 않고 글만 읽는 경우에도 아래 1구역을 SQL 실행 탭에 붙여 넣어 같은 sales 테이블과 6행을 만들 수 있다.

-- 1구역: 테이블과 데이터 초기화
DROP TABLE IF EXISTS sales;
CREATE TABLE sales(
  sale_date TEXT,
  channel TEXT,
  amount TEXT,
  measurement_status TEXT
);
INSERT INTO sales VALUES
  ('2026-07-01', 'online', '100', 'measured'),
  ('2026-07-02', 'online', '', 'missing'),
  ('2026-07-03', 'store', '300', 'measured'),
  ('2026-07-04', 'store', '0', 'measured_zero'),
  ('2026-07-05', 'online', '', 'missing'),
  ('2026-07-06', 'store', '200', 'measured');

-- 2구역: TODO 조회

▸ 처음 실행했을 때 보이는 출력

rows|measured|average
6||

▸ 실행 환경별 주의사항

• PowerShell과 일반 터미널 모두 먼저 sqlite3 :memory:로 SQLite 콘솔을 연다. sqlite> 프롬프트 안에서 .read starter.sql을 실행하므로 운영체제별 입력 리다이렉션 문법을 사용할 필요가 없다.
• 본문의 코드블록은 SQLite CLI와 DB Browser에서 공통으로 실행할 SELECT 문만 나타낸다. CLI 전용 .mode, .import 같은 준비 명령은 ZIP의 starter.sql과 solution.sql에 들어 있다.
• DB Browser에서는 browser-starter.sql로 sales 테이블을 만든다. 응용 문제를 풀 때는 SQL 실행 탭의 + 버튼으로 새 편집 탭을 열고 6단계 SELECT 문을 붙여 넣은 뒤 그 쿼리만 실행한다.
• DB Browser용 파일도 amount를 TEXT로 만들고 누락을 빈 문자열 ‘’로 넣는다. 따라서 SQLite CLI와 같은 NULLIF·CAST 변환을 그대로 연습한다.
• 본문의 rows|measured|average와 6|4|150.0 표기는 SQLite CLI의 세로줄 출력이다. DB Browser에서는 아래 결과 격자에서 열 이름 rows·measured·average와 셀 값 6·4·150.0이 같은지 확인한다.
• SQLite CLI는 NULL을 NULL이라는 글자 대신 빈 칸으로 표시하므로 초기 출력 6||은 오류가 아니다. 두 세로줄 사이는 measured와 average가 아직 NULL이라는 뜻이다.

환경을 준비했으면 코드가 해결하는 문제를 먼저 이해한 뒤 순서대로 실행한다.

3단계. 코드를 실행하며 원리 확인하기

SQLite의 .import 기능은 기본적으로 CSV의 모든 열을 TEXT 타입으로 생성한다. 따라서 amount 열의 숫자는 ‘100’과 같은 문자열로 저장되며, 비어 있는 행은 NULL이 아닌 길이 0인 빈 문자열 ‘’로 저장된다.

공식 문서에 따르면 avg() 함수는 그룹 내의 non-NULL 값에 대해서만 평균을 계산한다. 그러나 문자열을 숫자로 바꾸는 CAST(amount AS REAL)를 수행하면 빈 문자열은 0.0으로 변환되므로 avg()는 이를 유효한 측정값으로 인식해 분모에 포함시킨다.

이 문제를 해결하려면 NULLIF(amount, ‘’)를 먼저 적용하여 빈 문자열만 명시적인 NULL로 치환해야 한다. 이렇게 처리하면 실제 매출 ‘0’은 남고 누락된 두 건은 NULL이 되어 평균 계산의 분모에서 제외된다.

만약 CAST를 NULLIF보다 먼저 수행하면 빈 문자열과 실제 매출 ‘0’이 모두 0.0이 되어 구분이 불가능해지므로 처리 순서가 중요하다.

starter.sql은 테이블 초기화, CSV 가져오기, 출력 형식 설정, 마지막 TODO SELECT를 위에서 아래로 한 번에 실행한다. SQLite CLI에서는 .read starter.sql 한 번으로 처리하므로 점 명령을 따로 입력하지 않는다.

browser-starter.sql도 위쪽 DROP·CREATE·INSERT로 같은 TEXT 데이터를 만든 뒤 아래쪽 TODO SELECT를 실행한다.

▸ 코드를 읽기 전에 알아둘 표현

표현	쉬운 뜻	이 데이터에서의 결과
NULLIF(값, 비교값)	두 값이 같으면 NULL을 돌려주고, 다르면 원래 값을 유지한다.	NULLIF(amount, ‘’)는 amount가 빈 문자열이면 NULL, ‘100’이면 ‘100’을 돌려준다.
CAST(값 AS REAL)	문자열 형태의 숫자를 소수점 계산이 가능한 숫자로 바꾼다. 입력이 NULL이면 결과도 NULL이다.	CAST(‘100’ AS REAL)은 100.0, CAST(‘’ AS REAL)은 0.0, CAST(NULL AS REAL)은 NULL이다.

위 표현의 역할을 확인했으면 starter 파일에서 바꿀 두 수식을 먼저 살펴본다. 수식의 결과를 이해한 뒤 전체 코드를 실행하면 각 줄을 외우지 않아도 된다.

▸ starter에서 완성할 수식

1. 유효 측정값 수 구하기

COUNT(NULLIF(amount, ''))

빈 문자열을 먼저 NULL로 바꾼 뒤 COUNT가 NULL을 제외하도록 한다. NULLIF(amount, ‘’)는 문자열 ‘0’을 빈 문자열과 다르다고 판단해 실제 매출 0을 유지한다. CAST를 먼저 하면 빈 문자열 ‘’과 실제 매출 ‘0’이 모두 숫자 0.0이 되어 구분이 사라진다. 그 뒤 NULLIF(0.0, 0)를 적용하면 두 값 모두 NULL이 되므로 순서를 바꾸면 안 된다.

2. 빈 문자열을 제외한 평균 구하기

AVG(CAST(NULLIF(amount, '') AS REAL))

빈 문자열을 NULL로 바꾸고 남은 숫자 문자열을 REAL로 변환한 뒤 평균을 낸다. NULLIF 없이 AVG(CAST(amount AS REAL))만 실행하면 빈 문자열 두 건이 0.0이 되어 600÷6=100.0이라는 비교값이 나온다. NULLIF로 정제하면 NULL이 AVG에서 제외되어 600÷4=150.0이 된다.

▸ 1. TODO와 두 수식의 원리 이해하기

아래 SELECT의 NULL AS measured와 NULL AS average가 바꿀 두 자리다. 코드 아래의 용어표와 두 수식을 순서대로 읽어 각 자리에 들어갈 식을 이해한다.

SELECT
  COUNT(*) AS rows,
  NULL AS measured, -- TODO: 왼쪽 NULL을 유효 측정값을 세는 식으로 바꾸세요.
  NULL AS average   -- TODO: 왼쪽 NULL을 빈 문자열을 제외한 평균 식으로 바꾸세요.
FROM sales;

▸ 2. 두 수식을 입력하고 평균 검산하기

SQLite CLI 환경에서는 제공된 starter.sql 파일 하단의 TODO 영역에 COUNT(NULLIF(amount, ‘’))와 AVG(CAST(NULLIF(amount, ‘’) AS REAL))를 작성한다. 이때 .read starter.sql 명령을 재실행하면 DROP TABLE 명령 덕분에 기존 데이터가 삭제되고 새롭게 CSV가 임포트되므로 데이터 중복 없이 결과를 확인할 수 있다.

DB Browser 사용자는 SQL 실행 탭에서 해당 쿼리를 수정하여 실시간으로 변화하는 measured와 average 값을 관찰하며 수식의 정확성을 직접 검증한다. NULLIF는 두 값이 같으면 NULL을 돌려주므로 빈 문자열을 효과적으로 제거한다.

▸ 3. 내 출력과 기대값 먼저 비교하기

쿼리 실행 후 출력되는 결과가 rows|measured|average 형식에서 6|4|150.0과 일치하는지 대조한다. 만약 measured가 6으로 나오거나 average가 100.0으로 나온다면, 이는 빈 문자열을 NULL로 바꾸는 NULLIF 처리가 누락되어 CAST가 빈 값을 0.0으로 강제 변환했음을 의미한다.

괄호의 중첩 순서가 AVG 내부에 CAST가 있고 그 가장 안쪽에 NULLIF가 위치했는지 확인하는 것이 디버깅의 핵심이다. 모든 수치가 일치한다면 실제 데이터 6건 중 유효한 4건만이 평균 산출의 분모로 사용되었음을 확신할 수 있다.

▸ 4. 정답 코드로 비교하기

SELECT
  COUNT(*) AS rows,
  COUNT(NULLIF(amount, '')) AS measured,
  AVG(CAST(NULLIF(amount, '') AS REAL)) AS average
FROM sales;

▸ 5. 실제 실행 결과 확인하기

rows|measured|average
6|4|150.0

출력이 나왔다면 정답과 같은지만 보지 말고, 각 숫자가 입력 데이터에서 어떻게 계산됐는지 거꾸로 확인한다.

4단계. 실행 결과 검산하기

확인할 값	결과	이렇게 읽는다
전체 거래 수	rows = 6	CSV에 들어 있는 모든 행을 센 값
유효 측정값 수	measured = 4	amount가 빈 문자열 ‘’인 두 건을 제외하고 실제 값이 있는 행만 센 값
누락 건수	6 - 4 = 2	전체 거래 수에서 유효 측정값 수를 뺀 값. 5단계에서 missing_rows라는 새 열로 만든다.
유효 측정 비율	4 ÷ 6 × 100 = 66.7%	전체 거래 중 amount가 실제로 측정된 거래의 비율
유효 매출 합계	100 + 300 + 0 + 200 = 600	실제 0은 유효 측정값이므로 합계에 포함
원래 평균	600 ÷ 4 = 150.0	누락을 제외하고 값이 있는 네 건으로 나눈 평균
누락을 0으로 본 평균	600 ÷ 6 = 100.0	누락 두 건을 숫자 0으로 처리했을 때의 비교값. SQL 구현은 5단계에서 배운다.

전체 거래 6건 중 실제 매출액이 기록된 건수는 4건이며, 이에 따른 유효 측정 비율은 약 66.7%로 계산된다. 이는 전체 데이터 중 1/3이 누락되었음을 시사하므로, 평균 150.0이라는 수치만 제시하기보다 데이터 수집 품질의 한계를 함께 인지해야 한다.

누락된 2건을 숫자 0으로 간주했을 때의 평균인 100.0과 유효 측정 평균 150.0을 비교하면 결측치가 지표를 얼마나 왜곡하는지 알 수 있다. 실무적으로는 online 채널에 누락이 집중되어 있어 해당 채널의 성과가 과소평가될 위험이 크다.

따라서 보고서에는 average와 함께 rows, measured를 병기하여 각 지표가 어떤 분모를 사용했는지 독자가 판단하게 한다.

기본 결과를 설명할 수 있다면 같은 원리를 응용 문제에 적용한다.

5단계. 직접 풀어보기

channel별로 rows, measured, missing_rows, average_amount, average_if_null_zero를 한 번에 계산한다. 결과 표의 missing_rows와 두 평균 열의 차이를 이용해 어느 채널에 측정 누락이 있고 그 누락을 0으로 볼 때 평균이 어떻게 달라지는지 설명한다.

CAST만으로 빈 문자열이 0.0이 되는 것은 SQLite의 암묵적 변환 부작용이지, 분석가가 선택한 결측 정책이 아니다. 먼저 NULLIF로 누락을 정제하고 CAST한 뒤 COALESCE로 0.0을 넣으면, 누락을 0으로 본다는 비즈니스 정책이 SQL에 드러난다.

원천 데이터에 실제 NULL이 들어와도 CAST 결과는 NULL로 남고 바깥 COALESCE가 0.0으로 바꾸므로 빈 문자열과 같은 정책이 적용된다.

▸ 응용 문제에서 새로 쓰는 표현

표현	쉬운 뜻	이 데이터에서의 결과
GROUP BY channel	channel 값이 같은 행을 한 그룹으로 묶어 그룹마다 집계한다.	online 세 행과 store 세 행이 각각 결과 한 행이 된다.
COUNT(*) - COUNT(NULLIF(amount, ‘’))	두 집계함수의 결과를 빼서 전체 행 중 누락된 행 수를 구한다.	online은 3 - 1 = 2이므로 missing_rows가 2다.
COALESCE(값, 대체값)	앞의 값이 NULL이면 뒤의 대체값을 돌려준다.	빈 문자열 ‘’ → NULLIF 결과 NULL → CAST 결과 REAL NULL → COALESCE 결과 0.0 순서다.
ROUND(숫자, 소수점 자리)	숫자를 지정한 소수점 자리까지 반올림한다.	ROUND(33.333, 2)는 33.33이다.

▸ 중첩 수식 계산 순서

3단계의 NULLIF·CAST 흐름 뒤에 COALESCE가 하나 더 붙는다. 아래에서는 그 추가 단계가 NULL만 비교용 0.0으로 바꾸는 지점을 확인한다.

• 빈 amount를 비교용 0으로 바꾸기: amount가 ‘’일 때 NULLIF(amount, ‘’) 결과 NULL → CAST 결과 NULL → COALESCE 결과 0.0. AVG는 NULL은 세지 않지만 COALESCE가 만든 0.0은 유효한 값으로 센다. 그래서 online 평균의 분모가 측정값 1건에서 전체 3건으로 늘어난다.
• 실제 매출 0 유지하기: amount가 ‘0’일 때 NULLIF(amount, ‘’) 결과 ‘0’ → CAST 결과 0.0 → COALESCE 결과 0.0. 실제 측정값 0은 CAST 단계에서 이미 숫자 0.0이므로 COALESCE가 바꾸지 않는다.

▸ 힌트

함수는 안쪽부터 NULLIF → CAST → COALESCE → AVG → ROUND 순서로 감싼다. 비교 평균의 괄호 구조는 ROUND(AVG(COALESCE(CAST(NULLIF(amount, ‘’) AS REAL), 0.0)), 2)다. 같은 SELECT 절에서 방금 만든 measured 별칭을 missing_rows 식에 즉시 재사용할 수 없으므로 COUNT(*) - COUNT(NULLIF(amount, ‘’))처럼 집계식을 다시 쓴다. 원본 amount 열은 바꾸지 않고 GROUP BY channel 뒤에 channel 순서로 정렬한다.

6단계. 모범 답안과 해설

SELECT
  channel,
  COUNT(*) AS rows,
  COUNT(NULLIF(amount, '')) AS measured,
  COUNT(*) - COUNT(NULLIF(amount, '')) AS missing_rows,
  ROUND(AVG(CAST(NULLIF(amount, '') AS REAL)), 2) AS average_amount,
  ROUND(AVG(COALESCE(CAST(NULLIF(amount, '') AS REAL), 0.0)), 2) AS average_if_null_zero
FROM sales
GROUP BY channel
ORDER BY channel;

▸ 실행 결과

channel|rows|measured|missing_rows|average_amount|average_if_null_zero
online|3|1|2|100.0|33.33
store|3|3|0|166.67|166.67

▸ 해설

online 채널의 amount는 ‘100’, 빈 문자열 ‘’, 빈 문자열 ‘’이다. 원래 평균은 값이 있는 한 건만 사용하므로 100÷1=100.0이고, 빈 문자열 두 건을 0으로 바꾼 비교 평균은 100÷3=33.33이다.

store 채널의 amount는 ‘300’, ‘0’, ‘200’이며 세 값이 모두 측정값이므로 (300+0+200)÷3=166.67이다. store에는 누락이 없어 두 평균이 같고, online에는 누락 두 건이 있어 두 평균이 달라진다.

정답을 맞혔더라도 아래 항목까지 설명할 수 있어야 실무에서 같은 실수를 피할 수 있다.

마무리: 실무 체크리스트

주의 1. CSV의 빈 문자열을 이미 NULL이라고 가정하고 CAST(amount AS REAL)만 적용하면 누락이 0으로 계산되어 평균이 낮아진다. COUNT와 AVG 모두에서 NULLIF를 사용해 빈 문자열을 NULL로 바꾼 뒤 measured 값이 4인지 먼저 확인한다.

주의 2. 실제 매출이 0인 행까지 결측으로 제외하면 성과가 왜곡된다. amount의 값과 measurement_status를 함께 확인하여, 빈 문자열이나 NULL만 누락으로 분류하고 실제 숫자 0은 유효한 측정값으로 보존해야 한다.

확장 1. 채널별 보고서에 전체 건수, 유효 측정 건수, 누락 건수를 평균과 함께 배치한다. 그룹 간 평균 비교 전에 유효 측정 비율부터 확인하면 성과의 차이와 수집 품질의 차이를 명확히 구분하여 판단할 수 있다.

확장 2. 원본 amount는 보존하고 NULL 제외 평균과 0 치환 평균을 별도 열로 계산한다. COALESCE를 활용해 명시적으로 0을 채우면 결측 처리 정책이 SQL에 드러나므로 다른 엔진으로 이관할 때도 계산 로직을 정확히 검증할 수 있다.

공식 문서

문서 확인일: 2026-07-30

• SQLite Aggregate Functions