시계열·분석 SQL
ASOF JOIN, window, QUALIFY, sampling, 분석용 join 패턴을 DuckDB에서 사용하는 기준을 정리합니다.
핵심 요약
- 시간·범위·상태 변화가 얽힌 분석 쿼리를 위한 DuckDB의 ASOF JOIN, window, QUALIFY, range join, sampling 패턴을 정리합니다.
- 가격표·구독 플랜·환율·feature flag처럼 "as of time" 의미가 있는 데이터는 window self join보다 ASOF JOIN을 먼저 검토합니다.
- QUALIFY로 window 결과를 subquery 없이 필터링해 중첩을 줄이고 partition/order 기준을 명시합니다.
- range join은 결과 row가 폭발하기 쉬우므로 각 side의 row count·key cardinality·시간 범위를 먼저 점검합니다.
- sample은 성능 개발용이며 seed·ordering을 고정하고, 품질 검증과 회계성 집계는 full data로 다시 실행합니다.
분석에서 까다로운 쿼리는 보통 "정확히 같은 key"가 아니라 시간, 범위, 상태 변화와 얽힌 쿼리입니다. DuckDB는 이런 분석 SQL에 필요한 ASOF JOIN, window, QUALIFY, sampling을 폭넓게 지원합니다.
ASOF JOIN
ASOF JOIN은 왼쪽 row의 시점 기준으로 오른쪽 테이블의 가장 가까운 과거 값을 붙일 때 씁니다.
SELECT
h.account_id,
h.event_time,
p.plan,
h.usage_amount
FROM usage_events h
ASOF LEFT JOIN plan_history p
ON h.account_id = p.account_id
AND h.event_time >= p.effective_at;가격표, 구독 플랜, 환율, feature flag, inventory 상태처럼 "as of time" 의미가 있는 데이터는 window self join보다 ASOF JOIN을 먼저 검토합니다.
Window와 QUALIFY
SELECT
customer_id,
event_time,
event_name,
row_number() OVER (
PARTITION BY customer_id
ORDER BY event_time
) AS event_seq
FROM events
QUALIFY event_seq = 1;QUALIFY를 쓰면 window 결과를 subquery 없이 필터링합니다. 리뷰하기 좋은 분석 SQL을 만들려면 중첩 subquery를 줄이고 window의 partition/order 기준을 명시합니다.
Range join과 temporal overlap
SELECT
s.session_id,
e.event_id
FROM sessions s
JOIN events e
ON e.user_id = s.user_id
AND e.event_time >= s.started_at
AND e.event_time < s.ended_at;range join은 결과 row가 폭발하기 쉬우니, 먼저 각 side의 row count와 key cardinality, 시간 범위를 점검합니다.
SELECT
count(*) AS rows,
count(DISTINCT user_id) AS users,
min(started_at),
max(ended_at)
FROM sessions;Sampling
분석 개발 단계에서는 sample을 쓰되, seed와 ordering을 고정합니다.
SET threads = 1; -- 같은 입력 순서와 버전에서 재현성 확보
SELECT *
FROM events
USING SAMPLE 1% (bernoulli, 42)
ORDER BY ALL;seed만 같아도 병렬 실행과 입력 순서가 달라지면 같은 표본을 보장하지 못합니다. 회귀용 표본은 파일로 저장하고 버전·입력 순서도 고정합니다. 퍼센트 기본값인 system sampling은 vector 단위라 작은 입력에서 빈 표본이 나올 수 있어 위 예제는 row 단위 bernoulli를 명시했습니다. Samples.
샘플은 성능 개발용이니, 품질 검증과 회계성 집계는 full data로 다시 돌립니다.
2.0 NEAREST JOIN
ASOF가 시간의 인접 관계라면 NEAREST는 거리·유사도 식에 따른 top-k 관계를 표현합니다. 거리에는 작은 값, 유사도에는 큰 값이 우선입니다.
WITH queries AS (
SELECT 1 AS id, [1.0, 0.0]::FLOAT[2] AS embedding
), candidates AS (
SELECT * FROM (VALUES
(10, [1.0, 0.0]::FLOAT[2]),
(20, [0.0, 1.0]::FLOAT[2])
) v(id, embedding)
)
SELECT q.id AS query_id, c.id AS nearest_id
FROM queries q
JOIN candidates c NEAREST 1
BY DISTANCE array_distance(q.embedding, c.embedding); -- (1, 10)APPROX NEAREST는 근사 검색입니다. 속도 비교와 함께 정확 검색 대비 recall을 측정하고, 동점의 순서와 tenant 필터가 결과 계약에 맞는지 확인합니다. 문법만으로 특정 인덱스 사용을 보장하지 않으므로 실행 계획도 봅니다. NEAREST JOIN.