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

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

이번 실습에서는 온라인·매장 거래 6건을 이용해 전체 행 수와 유효 측정값 수를 나란히 확인하고, 값이 있는 4건의 평균 150.0을 재현한다. 마지막에는 실제 매출 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-28-sqlite-null-average.zip

29.1KB

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

• sales.csv — 원본 형태를 확인하는 실습 입력 데이터
• practice-data.xlsx — 데이터와 열 설명을 함께 보는 엑셀 파일
• DEV-LAB-2026-07-28-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단계. 코드를 실행하며 원리 확인하기

1단계에서 확인했듯 이 실습의 .import는 sales의 모든 열을 TEXT로 만든다. 따라서 amount의 숫자는 ‘100’처럼 문자로 저장되고 ,, 부분은 길이 0인 빈 문자열 ‘’로 저장된다.

COUNT(amount)는 NULL만 제외하므로 빈 문자열까지 값으로 세고, AVG에 필요한 CAST는 빈 문자열을 숫자 0.0으로 바꾼다. 정제 없이 평균을 내면 합계 600을 전체 6건으로 나눈 100.0이 나온다.

같은 입력을 COUNT와 AVG에서 제외하려면 먼저 NULLIF(amount, ‘’)로 빈 문자열만 NULL로 바꿔야 하며, 그러면 실제 매출 ‘0’은 남고 평균은 600÷4=150.0이 된다. 아래 TODO를 확인한 뒤 두 수식을 완성한다.

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 상단의 DROP, .mode, .import는 그대로 두고, 외부 편집기에서 파일 하단 TODO 두 줄만 완성한다. 저장 후 열어 둔 sqlite> 콘솔로 돌아와 .read starter.sql을 다시 실행하면 DROP TABLE 때문에 행이 누적되지 않는다.

DB Browser 사용자는 외부 편집기를 쓰지 않고 DB Browser 앱의 SQL 실행 탭 안에서 browser-starter.sql 2구역의 NULL AS measured와 NULL AS average를 직접 수정한다. 그런 다음 2구역 SELECT 안에 커서를 두거나 그 SELECT만 선택해 Ctrl+Enter를 누른다.

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

solution 파일을 열기 전에 내 출력이 rows=6, measured=4, average=150.0인지 expected-output.txt와 비교한다. 값이 다르면 measured의 NULLIF와 average의 NULLIF·CAST 순서를 먼저 확인한다.

세 값과 계산 근거가 모두 맞은 뒤에만 아래 정답 코드를 열어 철자와 괄호 위치를 비교한다.

▸ 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건 중 amount가 있는 거래는 4건이므로 유효 측정 비율은 4÷6×100=66.7%다. 평균 150.0만 제시하면 두 건의 누락이 숨겨지므로, 채널을 비교하기 전에 누락이 어디에 집중됐는지 확인해야 한다.

누락 두 건을 숫자 0으로 본 비교 평균은 합계 600을 전체 6건으로 나눈 100.0이다. 이 비교를 SQL로 구현하는 방법은 새 함수의 뜻과 함께 5단계에서 배운다.

실무 보고서에는 average와 함께 rows, measured, missing_rows를 같은 표에 표시해 매출 수준과 수집 품질을 따로 판단하고, 각 지표가 어떤 분모를 사용했는지 독자가 확인할 수 있게 한다.

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

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의 길이 0인 빈 문자열 ‘’을 이미 NULL이라고 가정하고 CAST(amount AS REAL)만 적용하면 누락이 0으로 계산될 수 있다. COUNT와 AVG 모두에서 빈 문자열을 NULL로 바꾼 뒤 measured가 4인지 먼저 확인한다.

주의 2. amount가 0인 행까지 결측으로 제외하면 실제 매출 0이 사라진다. amount의 값과 measurement_status를 함께 확인하고, 빈 문자열 또는 NULL만 누락으로 분류해야 한다.

확장 1. 채널·캠페인·기간별 보고서에 전체 건수, 유효 측정 건수, 누락 건수와 평균을 함께 둔다. 그룹 간 평균을 비교하기 전에 유효 측정 비율부터 확인하면 성과 차이와 수집 품질 차이를 구분할 수 있다.

확장 2. 원본 amount는 보존하고 NULL 제외 평균과 0 치환 평균을 별도 열로 계산한다. 각 열의 분모까지 함께 저장하면 결측 처리 기준이 바뀌거나 다른 SQL 엔진으로 옮길 때 결과의 의미를 다시 검증할 수 있다.

공식 문서

문서 확인일: 2026-07-28

• SQLite Aggregate Functions