SQL 빠진 날짜 채우기: 수집 완료 0과 미완료 NULL을 구분하는 보고서

SQL 보고서에서 빠진 날짜를 모두 0으로 채우면 수집 중인 날짜까지 무매출로 보일 수 있습니다. 날짜×매장 목록으로 예상 행을 먼저 만든 뒤, 수집 완료 상태와 거래 집계를 함께 연결하세요. 거래가 없는 셀은 수집 완료가 확인될 때만 0으로 채우고, 미완료나 금액 오류는 NULL로 남깁니다. 실제 0원 거래는 그 값 그대로 보존합니다.

이 실습의 목표는 7일×2매장인 14행을 빠짐없이 만들면서 확정 0과 미확정을 구분하는 것입니다. 완료 표식은 결제가 끝났다는 뜻이 아니라 해당 날짜·매장의 원천 데이터 수집이 모두 끝났다는 별도 업무 계약입니다.

Python 3.12.14 · SQLite 3.53.1에서 실행했습니다. 금액은 가상 정수 단위이며 실제 매출·수익 자료가 아닙니다. 날짜는 업무 기준으로 이미 정한 ISO 일자 문자열이고 시간대 변환은 이 예제의 범위 밖입니다.

1. 결과에 있어야 할 날짜와 매장을 먼저 만듭니다

매출 테이블만 GROUP BY하면 거래가 없는 날짜에는 행이 생기지 않습니다. 날짜 목록과 매장 목록을 CROSS JOIN하면 거래 존재 여부와 무관한 14개의 예상 셀이 생깁니다. 이 골격에 집계 결과를 LEFT JOIN합니다.

날짜·매장별 수집 완료표 load_status는 같은 키가 한 행이도록 기본키로 제한합니다. 실제 거래는 여러 행일 수 있으므로 연결 전에 날짜·매장별로 집계합니다. 여러 상세 테이블을 바로 붙여 금액이 늘어나는 상황을 피하는 목적도 있습니다.

2. 한 파일에서 생성·분류·합계까지 재현합니다

아래를 calendar_report.py로 저장해 실행하세요. 표준 라이브러리만 사용하고 모든 테이블은 메모리에 생성합니다. COUNT(*)와 COUNT(amount)를 비교해 금액 NULL이 섞인 거래도 발견합니다. SUM만 확인하면 정상 금액 300과 NULL이 섞여 있어도 300이 계산될 수 있습니다.

"""Synthetic, in-memory SQLite report. Python prints NULL explicitly."""
import sys
import sqlite3
from collections import Counter

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE load_status(
    day TEXT NOT NULL, shop TEXT NOT NULL,
    complete INTEGER NOT NULL CHECK(complete IN (0,1)),
    PRIMARY KEY(day,shop)
);
CREATE TABLE sales(
    id INTEGER PRIMARY KEY,
    day TEXT NOT NULL, shop TEXT NOT NULL,
    amount INTEGER CHECK(
        amount IS NULL OR (typeof(amount)='integer' AND amount >= 0)
    )
);
""")
days = ["2026-10-12", "2026-10-13", "2026-10-14", "2026-10-15",
        "2026-10-16", "2026-10-17", "2026-10-18"]
shops = ["A", "B"]
incomplete = {("2026-10-13", "B"), ("2026-10-17", "A")}
db.executemany("INSERT INTO load_status VALUES (?,?,?)", [
    (day, shop, int((day, shop) not in incomplete))
    for day in days for shop in shops
])
facts = [
    (1, "2026-10-12", "A", 100), (2, "2026-10-12", "A", 50),
    (3, "2026-10-12", "B", 200),
    (4, "2026-10-13", "B", 40), (5, "2026-10-13", "B", None),
    (6, "2026-10-14", "A", 300), (7, "2026-10-14", "A", None),
    (8, "2026-10-14", "B", 0),
    (9, "2026-10-15", "B", 80),
    (10, "2026-10-16", "A", 120),
    (11, "2026-10-17", "B", 30), (12, "2026-10-17", "B", 20),
    (13, "2026-10-18", "B", 70),
]
db.executemany("INSERT INTO sales VALUES (?,?,?,?)", facts)

CTE = """
WITH dates(day) AS (
    VALUES ('2026-10-12'),('2026-10-13'),('2026-10-14'),('2026-10-15'),
           ('2026-10-16'),('2026-10-17'),('2026-10-18')
),
shops(shop) AS (VALUES ('A'),('B')),
grid AS (
    SELECT day,shop FROM dates CROSS JOIN shops
),
daily AS (
    SELECT day,shop,COUNT(*) AS fact_rows,COUNT(amount) AS amount_rows,
           SUM(amount) AS raw_sum
    FROM sales
    GROUP BY day,shop
),
classified AS (
    SELECT g.day,g.shop,d.fact_rows,d.raw_sum,
           CASE
             WHEN l.complete IS NULL THEN 'completion_unknown'
             WHEN l.complete=0 THEN 'incomplete'
             WHEN d.fact_rows IS NULL THEN 'complete_no_sales'
             WHEN d.amount_rows <> d.fact_rows THEN 'amount_error'
             ELSE 'complete_sales'
           END AS state
    FROM grid AS g
    LEFT JOIN load_status AS l ON l.day=g.day AND l.shop=g.shop
    LEFT JOIN daily AS d ON d.day=g.day AND d.shop=g.shop
),
report AS (
    SELECT day,shop,state,
           CASE
             WHEN state='complete_no_sales' THEN 0
             WHEN state='complete_sales' THEN raw_sum
             ELSE NULL
           END AS report_amount
    FROM classified
)
"""
QUERY = CTE + "SELECT day,shop,state,report_amount FROM report ORDER BY day,shop"
rows = db.execute(QUERY).fetchall()
print("versions:", sys.version.split()[0], sqlite3.sqlite_version)
print("day | shop | state | report_amount")
for row in rows:
    print(" | ".join("NULL" if value is None else str(value) for value in row))

expected = [
    ("2026-10-12","A","complete_sales",150),
    ("2026-10-12","B","complete_sales",200),
    ("2026-10-13","A","complete_no_sales",0),
    ("2026-10-13","B","incomplete",None),
    ("2026-10-14","A","amount_error",None),
    ("2026-10-14","B","complete_sales",0),
    ("2026-10-15","A","complete_no_sales",0),
    ("2026-10-15","B","complete_sales",80),
    ("2026-10-16","A","complete_sales",120),
    ("2026-10-16","B","complete_no_sales",0),
    ("2026-10-17","A","incomplete",None),
    ("2026-10-17","B","complete_sales",50),
    ("2026-10-18","A","complete_no_sales",0),
    ("2026-10-18","B","complete_sales",70),
]
assert rows == expected
assert len(rows) == 14 == len({row[:2] for row in rows})
print("state counts:", dict(sorted(Counter(row[2] for row in rows).items())))
summary = db.execute(CTE + """
SELECT COUNT(*) AS cells,COUNT(report_amount) AS numeric_cells,
       SUM(report_amount) AS known_complete_part_total,
       CASE WHEN SUM(CASE WHEN state NOT IN
             ('complete_sales','complete_no_sales') THEN 1 ELSE 0 END)=0
            THEN SUM(report_amount) ELSE NULL END AS final_weekly_total
FROM report
""").fetchone()
print("summary (cells,numeric_cells,known_part,final_total):", summary)
assert summary == (14, 11, 670, None)
eligible_source_total = db.execute(CTE + """
SELECT SUM(s.amount)
FROM sales AS s
JOIN report AS r ON r.day=s.day AND r.shop=s.shop
WHERE r.state='complete_sales'
""").fetchone()[0]
print("eligible source total:", eligible_source_total)
assert eligible_source_total == 670
print("unfiltered raw SUM(amount):", db.execute("SELECT SUM(amount) FROM sales").fetchone()[0])

db.close()

3. 같은 0과 NULL도 서로 다른 사유가 있습니다

결과 14행 중 수집 완료·거래 없음은 4행, 수집 완료·정상 거래는 7행, 미완료는 2행, 완료됐지만 금액이 잘못된 셀은 1행입니다. 10월 14일 B의 금액 0은 실제 거래의 0이고, 10월 13일 A의 0은 수집 완료 뒤 거래가 없다는 뜻입니다. 상태 열을 함께 남겨야 차이를 알 수 있습니다.

10월 13일 B에는 일부 거래가 이미 들어와 있지만 수집 미완료이므로 금액을 NULL로 둡니다. 10월 14일 A에는 300과 NULL 금액이 함께 있어 amount_error입니다. 완료표가 아예 없는 경우에는 completion_unknown 분기가 적용되며 이것도 0으로 바꾸지 않습니다.

4. 부분합 670을 주간 최종합으로 표시하지 않습니다

SUM(report_amount)는 NULL을 제외하고 알려진 금액을 더해 670을 반환합니다. 계산 가능한 부분합일 뿐, 아직 확정되지 않은 세 셀을 포함한 주간 최종합은 아닙니다. 예제의 마지막 CASE는 미확정·오류 상태가 남아 있으면 최종합을 NULL로 반환합니다.

원천 sales에 바로 SUM을 적용한 값 1010에는 수집 미완료인 일부 거래 40과 금액 오류가 섞인 셀의 300까지 포함됩니다. 어느 값이 더 큰지를 보고 옳고 그름을 정하는 대신 보고서의 집계 계약과 포함된 셀을 대조하세요.

5. 실제 업무에 옮길 때 바꿔야 할 경계

영업일만 보고한다면 회사의 영업일 달력과 매장별 운영 기간으로 골격을 만듭니다. 수집 완료 뒤 늦게 도착하는 거래, 취소·환불, 날짜 변경을 허용한다면 완료를 재개방하는 정책과 재집계 버전도 필요합니다. 이 예제는 음수 금액을 막았으므로 환불 거래를 표현하는 일반 매출 원장에 그대로 적용할 수 없습니다.

긴 기간은 영구 달력 테이블 등으로 관리하고 기간·매장 범위를 제한하세요. CROSS JOIN의 셀 수는 날짜 수×매장 수이므로 무제한으로 만들지 않습니다. SQL 제품마다 날짜 생성 문법과 자료형이 달라 이 코드를 그대로 공통 문법이라고 설명할 수는 없습니다.

연속 날짜를 확보한 다음 전일 비교를 한다면 LAG의 이전 행과 실제 전날을 구분하는 실습을 이어 보세요. 연결한 빈 날짜가 오른쪽 조건 때문에 사라진다면 LEFT JOIN의 ON·WHERE 위치 비교로 행 보존부터 점검할 수 있습니다.

핵심 정리: 날짜 골격은 빠진 행을 드러내고, 수집 완료표는 그 빈칸을 0으로 읽어도 되는지 알려 줍니다. 상태와 금액을 함께 보존하고 최종합에도 완료성 검사를 적용하세요.