지수 이동 평균(EMA)은 20세기 중반 기술 분석에서 최근 관측값에 더 큰 가중치를 부여하는 평활 기술로 시작되었습니다. 간단한 이동 평균과는 달리, EMA 계산은 각 값이 이전에 계산된 EMA에 의존하여 의존성 체인을 형성하는 재귀적인 수학적 속성을 가지고 있어 벡터화에 저항합니다. 이러한 특성으로 인해 표준 윈도우 함수는 누적 결과 대신 정적 프레임에서 작동하기 때문에 집합 기반 SQL에서 구현하는 것이 notoriously 어렵습니다. 면접관들은 이 질문을 통해 후보자가 ANSI SQL 재귀 기능에 대한 이해를 평가하고 반복 알고리즘을 선언적 집합 논리로 변환할 수 있는 능력을 측정합니다.
수학적으로 EMA는 시간 t에 대해 다음과 같이 정의됩니다: EMAt = α × Price_t + (1-α) × EMA{t-1}, 여기서 α는 평활 계수입니다 (통상적으로 N기간 평균에 대해 2/(N+1)). 기본 사례에서는 첫 기간의 가격을 초기 EMA로 사용합니다. 데이터베이스 컨텍스트에서는 각 행이 이전 행의 계산된 결과에 접근해야 하는 타임스탬프별로 정렬된 수백만 개의 행에서 이 계산을 유지하는 문제가 발생합니다. 표준 ANSI SQL 집계 함수인 SUM 또는 AVG는 이 재귀적 의존성을 표현할 수 없으며, ROWS 또는 RANGE 절이 있는 윈도우 함수는 이전 행에서 계산된 출력이 아니라 원시 입력 값만을 접근합니다.
주어진 데이터를 순차적으로 탐색하는 재귀 CTE(공통 테이블 표현식)를 사용하여 이 문제를 구현합니다. 먼저, **ROW_NUMBER()**를 사용하여 불규칙한 타임스탬프나 간격을 처리하기 위해 결정론적인 행 순서를 설정합니다. 기준 멤버는 각 파티션(예: 주식 기호)의 첫 행을 선택하며, 초기 EMA를 첫 가격으로 설정합니다. 재귀 멤버는 CTE를 다음 순차 행(행 번호 = 이전 + 1)과 결합하고 이전 반복의 계산된 값을 사용하여 EMA 공식을 적용합니다. 이 접근법은 엄격히 ANSI SQL:1999 표준을 준수하며 단일 집합 기반 작업으로 실행됩니다.
WITH RECURSIVE numbered_trades AS ( SELECT symbol, price, trade_time, ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY trade_time) AS rn FROM trades ), ema_series AS ( -- 기준: 각 기호에 대한 첫 번째 행 SELECT symbol, price, rn, price AS ema -- 초기 EMA는 첫 가격 FROM numbered_trades WHERE rn = 1 UNION ALL -- 재귀: 이후 행에 대한 EMA 계산 SELECT t.symbol, t.price, t.rn, 0.2 * t.price + 0.8 * e.ema AS ema -- α = 0.2는 9기간 EMA FROM ema_series e JOIN numbered_trades t ON t.symbol = e.symbol AND t.rn = e.rn + 1 ) SELECT symbol, price, ema, rn FROM ema_series ORDER BY symbol, rn;
한 정량적 거래 회사는 새로운 알고리즘을 검증하기 위해 5,000개의 주식 기호에 대한 5년의 역사적 틱 데이터에 대해 EMA 지표를 백필해야 했습니다. 데이터 세트에는 2억 5천만 개의 고빈도 시장 데이터가 포함되어 있었고 기존 Python Pandas 솔루션은 네트워크를 통해 기가바이트의 데이터를 전송해야 하여 시장의 높은 변동성 기간 동안 분석 워크스테이션에서 빈번한 타임아웃 및 메모리 오류를 발생시켰습니다.
팀은 먼저 Python 전처리 스크립트를 구현하는 방안을 고려했습니다. 이 접근법은 빠른 프로토타이핑과 정량 분석가에게 친숙한 구문을 제공했으며, 최적화된 C 확장을 사용하여 재귀 계산을 본래적으로 처리했습니다. 그러나 이 방식은 PostgreSQL 데이터베이스와 애플리케이션 서버 간의 데이터 전송 오버헤드를 유발했으며, 수백만 개의 행을 메모리에 로드해야 했고, 배치 경계를 넘지 않고 연속한 EMA 계산을 유지하기 위한 복잡한 청크 로직이 필요했습니다.
둘째, 그들은 기하학적 가중치를 적용하여 각 행이 200기간의 회귀적 행동을 가지는 SELF JOIN를 사용한 순수 집합 기반 접근을 검토했습니다. 이 방법은 재귀를 완전히 피한 것으로, 이론적으로 데이터베이스 최적화기가 작업을 병렬화할 수 있도록 했습니다. 그러나 이는 O(n²) 복잡도가 발생해 중간 결과 집합이 대규모로 생성되어 고빈도 틱 데이터를 처리할 때 tempdb를 압도했습니다. 그리고 유한한 창截이 발생하여 진정한 EMA의 근사값만 제공했습니다.
셋째, 그들은 ANSI SQL 표준 구문을 사용하는 재귀 CTE 솔루션을 평가했습니다. 이 접근법은 데이터베이스 엔진 내에서 완전히 실행되며 네트워크 전송 오버헤드를 제거하고 수학적으로 정확한 EMA를 계산했습니다. 비록 극도로 긴 기호 역사에서 재귀 깊이 제한에 도달할 위험이 있었고 대부분의 ANSI SQL 구현에서는 기호별로 단일 스레드로 실행되었지만, 메모리 효율이 뛰어나고 자가 조인 방법의 제곱 급증을 피할 수 있었습니다.
그들은 데이터 이동을 제거하고 Pandas와 동일한 숫자 정확성을 보장하여 재귀 CTE 접근법을 선택했습니다. 이로 인해 외부 종속성 없이 데이터베이스 네이티브 관점의 물질화된 뷰 갱신으로 예약할 수 있었습니다. DBA는 가장 긴 기호 역사(대략 50,000 틱)의 최대 재귀 반복 매개변수를 구성했습니다.
이 구현은 전체 2억 5천만 행 데이터 세트를 약 12분 만에 처리했습니다. 결과로 만들어진 EMA 값은 Pandas 계산과 부동 소수점 정확도 내에서 일치하여 SQL 구현의 수학적 정확성을 검증했습니다. 이후 이 회사는 쿼리를 야간 물질화된 뷰 갱신으로 프로덕션화하여 외부 Python 스크립트의 필요성을 없애고 데이터 파이프라인의 복잡성을 크게 줄였습니다.
소스 테이블에 시퀀스의 간격이나 불규칙한 타임스탬프가 포함되어 있을 때 계산을 어떻게 처리합니까?
많은 후보자들은 trade_time이나 ID 열이 rn = e.rn + 1 조인에 적합한 밀집 시퀀스를 제공한다고 가정합니다. 사실, 누락된 틱이나 삭제된 기록이 발생하면 재귀 체인이 끊어지는 간격을 만듭니다. 이 솔루션은 재귀 CTE 전에 ROW_NUMBER() 또는 **DENSE_RANK()**를 사용하여 밀집 순위를 구상하고, 타임스탬프 간격에 관계없이 연속 정수를 보장해야 합니다. 이렇게 하면 물리적 키 값과 논리적 순서를 분리하여 재귀가 중단 없이 진행되고 올바른 시간 순서가 보존됩니다.
왜 재귀 CTE 접근법이 극단적으로 긴 시계열(예: 기호당 100,000개 이상의 행)에 실패할 수 있으며, 이를 ANSI SQL 제약 조건 내에서 어떻게 완화합니까?
후보자들은 종종 ANSI SQL 표준이 무한 재귀 깊이를 요구하지 않으며, PostgreSQL는 기본적으로 1000회의 반복을, SQL Server는 100회를 기본으로 설정하고 있다는 점을 간과합니다. 이러한 한계를 초과하면 쿼리가 중단됩니다. 완화 방법으로는 제어 테이블을 사용한 배치 처리 또는 반복적 접근을 사용할 수 있지만, 엄격한 ANSI SQL에서는 세션의 재귀 한계를 증가시키거나 고정된 회귀 기간(예: 200기간) 동안 대략적인 EMA를 계산하기 위해 윈도우 함수를 사용하는 하이브리드 접근을 구현해야 합니다. 정확한 계산의 경우, 플랫폼의 재귀 한계가 최대 시퀀스 길이를 초과하는지 확인해야 하며, (이 질문의 제약 조건에서 금지된) 저장 프로시저 루프를 사용해야 합니다.
여러 독립적인 시계열(예: 다른 주식 기호)에 대해 동시에 EMA를 계산할 때 교차 오염을 어떻게 방지합니까?
일반적인 실수는 재귀 조인 술어에서 파티션 키를 생략하는 것입니다. 후보자들은 t.rn = e.rn + 1라고 작성하면서 t.symbol = e.symbol을 포함하지 않아 행 번호가 정렬되어 있을 때 다른 기호 간에 재귀가 이동하게 됩니다. 올바른 구현은 기준 및 재귀 멤버 전반에 걸쳐 파티션 키(기호)를 전달하고, 시퀀스 번호 증가와 파티션 동등성 모두에서 엄격하게 조인해야 합니다. 이를 통해 재귀 트리가 각 기호별로 고립되며, 단일 CTE 실행 내에서 별도의 계산 맥락을 효과적으로 생성할 수 있습니다.