고객 매출 1위가 ‘고객 번호 없음’이었습니다: SQL 집계와 JOIN 결과를 검증하는 법

온라인몰 거래 54만 행에 “고객별 총 주문 금액 상위 고객” 쿼리를 그대로 돌렸더니 1위가 £1,447,682의 ‘고객 번호 없음’이었습니다. 조건을 하나 잘못 넣자 12분 만에 전량 취소한 고객이 4위로 올라왔고, 상품 이름을 붙이려고 JOIN하자 전체 매출이 £9.75M에서 £13.97M으로 43% 불어났습니다. 세 쿼리 모두 에러 없이 실행됐습니다. SQL 결과를 믿기 전에 행 수와 합계를 원본과 맞춰 보는 습관이 필요한 이유를 실제 쿼리로 보여드립니다.

앞선 글들과 같은 UCI Online Retail II 데이터(영국 온라인몰, Year 2010-2011 시트 541,910행)를 SQLite 데이터베이스에 넣어 실습합니다. SQLite는 Python에 기본으로 들어 있어서 따로 설치할 게 없습니다.

질문을 SQL 네 조각으로 나눕니다

SQL 쿼리는 분석 질문을 네 조각으로 나눈 것과 같습니다.

조각	역할	“고객별 총 주문 금액 상위 10명”에서
SELECT	무엇을 볼지	고객 번호, 금액 합계
WHERE	어떤 행만 쓸지	어떤 거래를 ‘주문’으로 볼지
JOIN	어떤 표를 붙일지	고객 이름·나라, 상품 이름이 필요하면
GROUP BY	무엇 단위로 묶을지	고객 단위

AI에게 질문을 주면 이 네 조각을 채운 쿼리가 금방 나옵니다. 문제는 WHERE와 JOIN에서 생깁니다. 조건 하나가 빠지거나 붙인 표의 키가 겹치면, 결과는 에러 없이 조용히 틀립니다. 쿼리를 한 줄씩 읽을 수 있어야 틀린 곳을 찾을 수 있습니다.

실습 준비: 엑셀을 SQLite로

UCI에서 받은 online_retail_II.xlsx를 데이터베이스로 옮깁니다. 이때 흔히 만드는 ‘상품표’와 ‘고객표’도 원본에서 중복만 제거해 함께 만들었습니다. 이 두 표가 뒤에서 문제를 일으킵니다.

import os, sqlite3
import pandas as pd

XLSX = os.environ.get("RETAIL_XLSX", "online_retail_II.xlsx")
cols = {"Invoice": "invoice", "StockCode": "stock_code", "Description": "description",
        "Quantity": "quantity", "InvoiceDate": "invoice_date", "Price": "price",
        "Customer ID": "customer_id", "Country": "country"}
kw = dict(dtype={"Invoice": str, "StockCode": str, "Description": str, "Customer ID": "Int64"})

con = sqlite3.connect("retail.db")
for sheet, table in [("Year 2010-2011", "order_lines"), ("Year 2009-2010", "order_lines_prev")]:
    df = pd.read_excel(XLSX, sheet_name=sheet, **kw).rename(columns=cols)
    df["customer_id"] = df["customer_id"].astype("string")          # ID는 문자열로
    df["invoice_date"] = df["invoice_date"].dt.strftime("%Y-%m-%d %H:%M:%S")
    df.to_sql(table, con, if_exists="replace", index=False)

# 흔히 만드는 '상품표'와 '고객표' (원본에서 중복 제거만 해서 만든 것)
con.executescript("""
DROP TABLE IF EXISTS products;
CREATE TABLE products AS
  SELECT DISTINCT stock_code, description FROM order_lines WHERE description IS NOT NULL;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers AS
  SELECT DISTINCT customer_id, country FROM order_lines WHERE customer_id IS NOT NULL;
""")
for t in ["order_lines", "order_lines_prev", "products", "customers"]:
    print(t, con.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0])
con.close()

order_lines 541910
order_lines_prev 525461
products 4792
customers 4380

상품 코드는 4,070개인데 상품표는 4,792행, 고객은 4,372명인데 고객표는 4,380행입니다. 이 차이를 기억해 두세요.

함정 1: GROUP BY는 빈 값을 한 그룹으로 묶습니다

가장 먼저 나오는 쿼리입니다.

SELECT customer_id, ROUND(SUM(quantity * price), 2) AS total
FROM order_lines
GROUP BY customer_id
ORDER BY total DESC LIMIT 5;

  customer_id       total
0        None  1447682.12
1       14646   279489.02
2       18102   256438.49
3       17450   187482.17
4       14911   132572.62 

1위 None은 고객이 아닙니다. 고객 번호가 비어 있는 거래 13만여 행을 GROUP BY가 한 묶음으로 모은 것입니다. SQL에서 NULL끼리는 같은 그룹이 됩니다. 이 결과를 “1위 고객 매출 145만 파운드”로 보고하면 존재하지 않는 고객을 보고하는 셈입니다.

함정 2: WHERE 조건 하나가 순위를 바꿉니다

고객 번호가 있는 상품 매출만 남기면 이렇게 됩니다. 취소 송장은 수량이 음수라서 합계에서 자동으로 차감됩니다. 그래서 이 결과는 quantity * price 기준으로 취소분을 뺀 순매출에 가깝습니다.

  customer_id      total
0       14646  278778.02
1       18102  259657.30
2       17450  189735.53
3       14911  128882.13
4       12415  123638.18 

여기서 “취소는 빼야지” 하고 AND quantity > 0을 추가하면 어떻게 될까요?

  customer_id      total
0       14646  279138.02
1       18102  259657.30
2       17450  194550.79
3       16446  168472.50
4       14911  136275.72 

4위에 16446 고객이 새로 나타났습니다. 이 고객의 거래를 전부 열어 보면 이유가 보입니다.

   invoice         invoice_date stock_code  quantity  price
0   553573  2011-05-18 09:52:00      22980         1   1.65
1   553573  2011-05-18 09:52:00      22982         1   1.25
2   581483  2011-12-09 09:15:00      23843     80995   2.08
3  C581484  2011-12-09 09:27:00      23843    -80995   2.08 

2011년 12월 9일 오전 9시 15분에 같은 상품 80,995개(£168,472.50)를 주문하고 12분 뒤에 전량 취소했습니다. quantity > 0은 수량이 0 이하인 줄을 모두 걸러냅니다. 이 쿼리 범위(고객 번호가 있는 상품 줄)에서는 그런 줄 8,539개가 전부 취소 송장 줄입니다. 즉 취소 기록만 빠지고 원래 주문은 남기 때문에, 실제로는 거의 사지 않은 고객이 상위 4위가 됩니다. 5위였던 12415 고객은 밀려났습니다.

“취소 제외”라는 말은 두 가지로 읽힙니다. 취소된 주문까지 통째로 빼는 것과 취소 기록 줄만 빼는 것입니다. 쿼리가 어느 쪽인지 확인하지 않으면 순위표가 틀립니다.

함정 3: JOIN 뒤에 행이 불어납니다

상품 이름을 붙이려고 상품표를 JOIN했습니다.

     rows       total
0  541910  9747765.93
     rows        total
0  710920  13966136.88
상품 코드 중복: 650
  stock_code                         description
0     85123A  WHITE HANGING HEART T-LIGHT HOLDER
1     85123A                                   ?
2     85123A         wrongly marked carton 22804
3     85123A  CREAM HANGING HEART T-LIGHT HOLDER 

JOIN 한 번에 541,910행이 710,920행이 되고, 매출 합계가 43% 불어났습니다. 상품 코드 650개가 상품표에 두 번 이상 들어 있기 때문입니다. 가장 자주 팔린 상품(판매 줄 수 1위) 85123A만 해도 설명이 네 가지이고, 그중 ?와 wrongly marked carton 22804는 상품명이 아니라 재고 메모입니다. 주문 줄 하나가 상품표의 네 줄과 모두 짝지어지면서 네 번 집계됩니다.

붙이는 쪽 표에서 키가 겹치면 JOIN 결과의 행이 늘어납니다. 붙이는 표의 키가 유일하고 LEFT JOIN을 쓸 때 원본 행 수가 그대로 유지됩니다. INNER JOIN은 키가 유일해도 짝이 없는 원본 행을 버리기 때문에 오히려 행이 줄 수 있습니다. 고객표도 마찬가지입니다. 고객 8명이 나라가 두 개로 기록되어 있어서, 고객표를 JOIN하면 406,830행이 407,756행으로 926행 늘어납니다.

고치는 방법: 키를 하나로 만든 뒤 붙입니다

상품당 이름을 하나만 남기려고 흔히 MAX(description)을 씁니다.

  stock_code                  description
0     85123A  wrongly marked carton 22804 

가장 자주 팔린 상품의 이름이 wrongly marked carton 22804가 됐습니다. MAX는 문자열을 사전 순으로 비교하고, 소문자가 대문자보다 뒤에 오기 때문입니다. 행 수는 지켜지지만 이름이 틀립니다.

그래서 상품 코드마다 가장 자주 쓰인 설명을 하나 고른 뒤 JOIN했습니다. ROW_NUMBER() OVER (PARTITION BY ...)는 다음 글에서 자세히 다룰 윈도우 함수입니다.

WITH desc_count AS (
  SELECT stock_code, description, COUNT(*) AS n
  FROM order_lines WHERE description IS NOT NULL
  GROUP BY stock_code, description
), product_one AS (
  SELECT stock_code, description FROM (
    SELECT stock_code, description,
           ROW_NUMBER() OVER (PARTITION BY stock_code ORDER BY n DESC, description) AS rn
    FROM desc_count)
  WHERE rn = 1
)
SELECT COUNT(*) AS rows, ROUND(SUM(o.quantity*o.price), 2) AS total,
       MAX(CASE WHEN o.stock_code = '85123A' THEN p.description END) AS name_85123A
FROM order_lines o LEFT JOIN product_one p ON o.stock_code = p.stock_code;

     rows       total                         name_85123A
0  541910  9747765.93  WHITE HANGING HEART T-LIGHT HOLDER 

행 수 541,910과 합계 £9,747,765.93이 JOIN 전과 정확히 같고, 85123A의 이름도 제대로 붙었습니다. LEFT JOIN을 쓴 이유는 설명이 아예 없는 상품 코드의 주문 줄도 빠뜨리지 않기 위해서입니다.

검증: 행 수와 합계를 원본과 맞춥니다

쿼리 결과를 믿기 전에 세 가지를 확인합니다.

의심	증상	점검
JOIN 중복	합계가 과하게 큼	JOIN 전후 COUNT(*) 비교, 붙이는 표의 키가 유일한지 GROUP BY 키 HAVING COUNT(*) > 1
WHERE 누락·과잉	대상 밖 행이 섞이거나 필요한 행이 빠짐	조건을 하나씩 더하며 건수와 상위 결과가 어떻게 바뀌는지 보기
GROUP BY	묶음 합 ≠ 전체 합, 이상한 그룹	그룹별 합의 합을 전체 합과 대조, NULL 그룹 확인

이 데이터에서 그룹 합 검증은 이렇게 나왔습니다.

   sum_of_groups  total_direct
0     8286663.32    8286663.32
   before_join  after_join  customers_with_2_countries
0       406830      407756                           8

고객별 합계를 모두 더한 값과 같은 조건으로 바로 구한 전체 합이 일치합니다. 고객표 JOIN은 앞에서 본 대로 926행이 늘어났으니, 고객 나라를 붙일 때도 키를 먼저 하나로 정리해야 합니다.

AI에게 쿼리를 맡길 때

쿼리를 받을 때 점검 방법까지 같이 받아 두면 검증이 훨씬 쉬워집니다.

‘고객별 총 주문 금액 상위 10명’을 구하는 SQL을 짜 줘. 고객 번호가 비어 있는 행, 취소 거래, JOIN으로 붙인 표의 키 중복 때문에 결과가 틀어질 수 있는지 설명하고, JOIN 전후 행 수와 합계를 비교하는 점검 쿼리도 같이 줘.

점검 쿼리 결과가 원본과 다르면, JOIN을 하나씩 빼고 다시 세어 어디서 행이 늘어났는지 좁혀 갑니다.

면접에서 나올 수 있는 질문

JOIN 후 매출 합계가 커졌습니다. 원인과 확인 방법은요? 붙인 표에 같은 키가 여러 번 있으면 한 행이 여러 행과 짝지어져 중복 집계됩니다. JOIN 전후 행 수를 비교하고, 붙인 표에서 GROUP BY 키 HAVING COUNT(*) > 1로 중복 키를 찾습니다. 해결은 키를 하나로 정리한 뒤 붙이거나, 먼저 집계해서 키가 유일한 상태로 만든 다음 JOIN하는 것입니다.

GROUP BY에서 NULL은 어떻게 처리되나요? NULL 값들은 하나의 그룹으로 묶입니다. 그래서 고객 번호가 빈 거래가 ‘가장 큰 고객’처럼 보일 수 있습니다. 분석 목적에 따라 WHERE ... IS NOT NULL로 빼거나, 따로 표시해서 보고합니다.

“취소 제외”를 SQL로 어떻게 표현하겠습니까? 취소 기록 줄만 뺄지, 취소된 원래 주문까지 뺄지 먼저 정합니다. 취소 줄만 빼면 취소된 주문 금액이 그대로 남아 매출이 부풀 수 있습니다. 순매출이 목적이면 취소 줄의 음수 금액을 그대로 더해 차감하는 편이 정확합니다.

전체 코드

데이터베이스를 만든 폴더에서 실행합니다. 위의 출력은 모두 이 스크립트의 결과입니다.

import sqlite3
import pandas as pd

pd.set_option("display.width", 200)
con = sqlite3.connect("retail.db")
q = lambda sql: pd.read_sql(sql, con)

# 상품 매출만 보는 공통 조건: 부실채권 조정(A) 제외, 숫자 5자리로 시작하는 상품 코드만
PRODUCT = "invoice NOT LIKE 'A%' AND stock_code GLOB '[0-9][0-9][0-9][0-9][0-9]*'"

print("[1] 조건 없이 고객별 합계 상위 5")
print(q("""
SELECT customer_id, ROUND(SUM(quantity * price), 2) AS total
FROM order_lines
GROUP BY customer_id
ORDER BY total DESC LIMIT 5"""), "\n")

print("[2] 고객 번호 있는 상품 매출 (취소 차감 = 순매출) 상위 5")
print(q(f"""
SELECT customer_id, ROUND(SUM(quantity * price), 2) AS total
FROM order_lines
WHERE customer_id IS NOT NULL AND {PRODUCT}
GROUP BY customer_id ORDER BY total DESC LIMIT 5"""), "\n")

print("[3] 여기에 quantity > 0 만 추가하면 (취소 줄만 빠짐)")
print(q(f"""
SELECT customer_id, ROUND(SUM(quantity * price), 2) AS total
FROM order_lines
WHERE customer_id IS NOT NULL AND {PRODUCT} AND quantity > 0
GROUP BY customer_id ORDER BY total DESC LIMIT 5"""), "\n")

print("[4] 16446 고객의 거래 전체")
print(q("""
SELECT invoice, invoice_date, stock_code, quantity, price
FROM order_lines WHERE customer_id = '16446' ORDER BY invoice_date"""), "\n")

print("[5] 상품표를 JOIN하기 전과 후")
print(q("SELECT COUNT(*) AS rows, ROUND(SUM(quantity*price), 2) AS total FROM order_lines"))
print(q("""
SELECT COUNT(*) AS rows, ROUND(SUM(o.quantity*o.price), 2) AS total
FROM order_lines o JOIN products p ON o.stock_code = p.stock_code"""))
print("상품 코드 중복:", q("""
SELECT COUNT(*) AS codes_with_many_rows FROM (
  SELECT stock_code FROM products GROUP BY stock_code HAVING COUNT(*) > 1)""").iloc[0, 0])
print(q("SELECT stock_code, description FROM products WHERE stock_code = '85123A'"), "\n")

print("[6] MAX(description)으로 하나만 고르면")
print(q("""
SELECT stock_code, MAX(description) AS description
FROM products WHERE stock_code = '85123A' GROUP BY stock_code"""), "\n")

print("[7] 가장 많이 쓰인 설명 하나만 남기고 JOIN")
print(q("""
WITH desc_count AS (
  SELECT stock_code, description, COUNT(*) AS n
  FROM order_lines WHERE description IS NOT NULL
  GROUP BY stock_code, description
), product_one AS (
  SELECT stock_code, description FROM (
    SELECT stock_code, description,
           ROW_NUMBER() OVER (PARTITION BY stock_code ORDER BY n DESC, description) AS rn
    FROM desc_count)
  WHERE rn = 1
)
SELECT COUNT(*) AS rows, ROUND(SUM(o.quantity*o.price), 2) AS total,
       MAX(CASE WHEN o.stock_code = '85123A' THEN p.description END) AS name_85123A
FROM order_lines o LEFT JOIN product_one p ON o.stock_code = p.stock_code"""), "\n")

print("[8] 검증: 그룹별 합의 합 = 전체 합, 고객표 JOIN 전후 행 수")
print(q(f"""
WITH by_customer AS (
  SELECT customer_id, SUM(quantity*price) AS total FROM order_lines
  WHERE customer_id IS NOT NULL AND {PRODUCT} GROUP BY customer_id)
SELECT ROUND((SELECT SUM(total) FROM by_customer), 2) AS sum_of_groups,
       ROUND((SELECT SUM(quantity*price) FROM order_lines
              WHERE customer_id IS NOT NULL AND {PRODUCT}), 2) AS total_direct"""))
print(q("""
SELECT (SELECT COUNT(*) FROM order_lines WHERE customer_id IS NOT NULL) AS before_join,
       (SELECT COUNT(*) FROM order_lines o JOIN customers c ON o.customer_id = c.customer_id) AS after_join,
       (SELECT COUNT(*) FROM (SELECT customer_id FROM customers GROUP BY 1 HAVING COUNT(*) > 1)) AS customers_with_2_countries"""))

참고 자료

• UCI Online Retail II: Chen, D. (2012). UCI Machine Learning Repository. https://doi.org/10.24432/C5CG6D (CC BY 4.0)
• SQLite: SELECT: GROUP BY와 NULL 처리를 포함한 공식 문법 설명

실행 환경은 Python 3.9.6, pandas 2.3.3, SQLite 3.51.0입니다.