AsOf 조인

AsOf 조인 (AsOf Join)

시계열 데이터는 언제나 정확히 정렬되어 있지 않아요. 시계가 조금씩 어긋나기도 하고, 원인과 결과 사이에 지연이 생기기도 하죠. 이 때문에 두 개의 정렬된 데이터 집합을 이어 붙이는 일이 까다로울 때가 있어요. AsOf 조인은 이런 문제(와 그와 비슷한 문제들)를 풀기 위한 도구예요.

AsOf 조인이 풀어주는 대표적인 문제 중 하나는, 특정 시점에서 변하는 속성의 값을 찾는 것이에요. 이 사용 사례가 너무 흔해서 조인 이름도 여기서 나왔어요.

그 시점(as of)에서 이 속성의 값은 얼마였지?

더 일반적으로 말하면, AsOf 조인은 표준 SQL로 구현하면 번거롭고 느리기 마련인 시계열 분석 의미론을 담고 있어요.

출처: 공식문서

포트폴리오 예제 데이터

구체적인 예시로 시작해볼게요. 타임스탬프가 있는 주식 prices 테이블이 있다고 가정해요.

또한 여러 시점에 걸친 포트폴리오 holdings 테이블이 있어요.

이 두 테이블을 DuckDB에 불러오려면 이렇게 실행해요.

CREATE TABLE prices AS FROM 'https://duckdb.org/data/prices.csv';
CREATE TABLE holdings AS FROM 'https://duckdb.org/data/holdings.csv';

Inner AsOf 조인

각 보유 종목의 시점 가치를 계산하는 방법은, 보유 타임스탬프보다 이전의 가장 최근 가격을 AsOf 조인으로 찾는 거예요.

SELECT h.ticker, h.when, price * shares AS value
FROM holdings h
ASOF JOIN prices p
       ON h.ticker = p.ticker
      AND h.when >= p.when;

그러면 각 행에 "그 시점의 보유 가치"가 붙어요.

본질적으로 이 조인은 prices 테이블에서 인접 값을 찾아 정의된 함수를 실행하는 것과 같아요. 그리고 짚고 넘어갈 점은, 일치하는 ticker가 없는 행은 출력에 나타나지 않는다는 거예요.

Outer AsOf 조인

AsOf 조인은 오른쪽에서 기껏해야 하나의 일치를 만들기 때문에, 조인 때문에 왼쪽 테이블이 커지지는 않아요. 다만 오른쪽에 시점이 없는 부분이 있으면 왼쪽이 줄어들 수는 있어요. 이런 상황을 다루려면 outer AsOf 조인을 쓰면 돼요.

SELECT h.ticker, h.when, price * shares AS value
FROM holdings h
ASOF LEFT JOIN prices p
            ON h.ticker = p.ticker
           AND h.when >= p.when
ORDER BY ALL;

예상할 수 있듯이, ticker가 없거나 가격이 시작되기 전 시점이면 왼쪽 행을 버리는 대신 NULL 가격·값을 만들어요.

USING 키워드를 쓰는 AsOf 조인

지금까지는 AsOf 조인의 조건을 명시적으로 지정했어요. 그런데 SQL에는 두 테이블에서 컬럼 이름이 같을 때 쓸 수 있는 간편한 조인 조건 문법도 있어요. USING 키워드로 동등 비교할 필드들을 나열하는 방식이죠. AsOf 조인도 이 문법을 지원하지만, 제약이 두 가지 있어요.

• 마지막 필드는 **부등식(inequality)**이어야 해요.
• 그 부등식은 >=여야 해요(가장 흔한 경우).

그럼 처음 봤던 쿼리는 이렇게 쓸 수 있어요.

SELECT ticker, h.when, price * shares AS value
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

USING을 쓸 때 컬럼 선택에 대한 설명

조인에서 USING 키워드를 쓰면, USING 절에 지정한 컬럼들은 결과 집합에서 병합돼요. 즉 다음 쿼리를 실행하면

SELECT *
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

h.ticker, h.when, h.shares, p.price 컬럼만 돌아와요. ticker와 when은 한 번만 나타나는데, ticker와 when은 왼쪽 테이블(holdings)에서 가져와요.

ticker 컬럼은 두 테이블 값이 같으니 이 동작이 문제없어요. 하지만 when 컬럼은 AsOf 조인의 >= 조건 때문에 두 테이블 값이 다를 수 있어요. AsOf 조인은 왼쪽 테이블(holdings)의 각 행을, when 컬럼 기준으로 오른쪽 테이블(prices)에서 가장 가까운 이전 행과 매칭하도록 설계됐어요.

두 테이블의 when을 모두 보고 싶다면 *에 기대지 말고 컬럼을 명시적으로 나열해야 해요.

SELECT h.ticker, h.when AS holdings_when, p.when AS prices_when, h.shares, p.price
FROM holdings h
ASOF JOIN prices p USING (ticker, "when");

이렇게 하면 두 테이블의 완전한 정보를 얻을 수 있고, USING 키워드의 기본 동작 때문에 생기는 혼란도 피할 수 있어요.

더 알아보기 (Learn more)

• 구현 세부 내용은 블로그 포스트 "DuckDB's AsOf joins: Fuzzy Temporal Lookups"를 참고하세요.