DuckDB — 파일 위에서 바로 분석하는 열 지향 엔진 · 분석 SQL 패턴 · 실습

창, 피벗, 시각 조인으로 답한다

목표

GROUP BY 한 줄로 답이 안 나오는 질문들을 DuckDB 의 윈도 함수·QUALIFY·PIVOT·
ASOF JOIN·리스트/구조체로 풀고, EXPLAIN ANALYZE 로 그 질의가 실제로 무엇을
했는지 읽습니다.

왜 중요한가

"매장별 누적", "월을 열로", "그때 환율로" 는 분석 요청의 단골인데 집계 한 줄로는
풀리지 않습니다. 서브쿼리를 겹치거나 case when 을 여섯 번 적는 대신, 각 질문에
맞는 문법이 따로 있습니다. 특히 시각 순서 표(환율·가격 이력)를 보통 조인으로
붙이면 행이 불어나거나 빠지는데 합계가 그럴듯해서 눈에 안 띕니다 — ASOF JOIN 과
조인 뒤 행 수 확인이 그 사고를 막습니다. 마지막으로 EXPLAIN ANALYZE 의 행 수를
읽을 줄 알면 느린 질의의 원인이 대개 한 줄에 보입니다.

환경

이 실습은 /root/duck/an/analytics.duckdb 한 파일 위에서 합니다 (1단계에서
만듭니다). duckdb /root/duck/an/analytics.duckdb 로 열고, 결과는
COPY (질의) TO '/root/duck/an/이름.csv' (FORMAT csv, HEADER) 로 남깁니다.

픽스처는 /opt/lab/fixtures/duck/ 아래에 있습니다 — sales/*.csv.gz (36만 행),
stores.csv (매장 12곳, 통화 3종), fx_rates.csv (통화별 환율 변경 이력, 날짜가
불규칙합니다), products.json (줄마다 JSON 하나, tags 배열과 spec 중첩 객체).

채점 전에 CLI 를 .quit 로 닫으세요. 1단계 채점기가 DB 파일을 읽기 전용으로
엽니다.

단계

1. /root/duck/an/analytics.duckdb 에 sales · stores · fx_rates · products 표를 만든다 (JSON 은 리스트·구조체 형으로)
2. 매장별 월 누적 qty → /root/duck/an/02-running.csv (month, store_id, qty, running_qty, 72행)
3. QUALIFY 로 매장별 상위 3개 상품 → /root/duck/an/03-top3.csv (store_id, product_id, qty, rnk, 36행)
4. 국가 × 월 PIVOT → /root/duck/an/04-pivot.csv, 그것을 UNPIVOT 으로 되돌려 → /root/duck/an/04-long.csv
5. ASOF JOIN 으로 판매 시각의 환율을 붙여 월별 원화 매출 → /root/duck/an/05-krw.csv (month, revenue_krw)
6. tags 를 UNNEST 해 태그별 상품 수와 spec.weight_g 최대값 → /root/duck/an/06-tags.csv (tag, n, max_weight_g)
7. EXPLAIN ANALYZE 출력 → /root/duck/an/07-explain.txt, threads·memory_limit 값과 시간 → /root/duck/an/07-settings.txt
8. SUMMARIZE sales → /root/duck/an/08-summarize.csv

참고

sum(x) over (partition by a order by b) — 창을 a 마다 열고 b 순서로 누적.
qualify rnk <= 3 — 윈도 결과로 거른다. where 는 윈도보다 먼저 평가되어 쓸 수 없다.
PIVOT t ON col USING sum(x) GROUP BY key / UNPIVOT t ON COLUMNS(* EXCLUDE (key)) INTO NAME n VALUE v.
a asof join b on a.k = b.k and a.ts >= b.valid_from — 가장 가까운 과거 한 행만.
unnest(list), struct.field, read_json('경로').
흔한 실수: ASOF 대신 보통 조인에 >= 를 쓰면 행이 몇 배로 불어납니다. 조인 뒤 count(*) 를 확인하세요.
흔한 실수: EXPLAIN 만 하면 실행하지 않아 행 수·시간이 없습니다. EXPLAIN ANALYZE 여야 합니다.

단계 8개

분석용 DB 에 네 표를 적재한다
매장별 월 누적 판매량
QUALIFY 로 매장별 상위 3개 상품
PIVOT 으로 펼치고 UNPIVOT 으로 되돌린다
ASOF JOIN 으로 그때 환율을 붙인다
리스트를 펼치고 구조체를 꺼낸다
EXPLAIN ANALYZE 와 자원 설정
SUMMARIZE 로 표를 한눈에