역사적 맥락
ANSI SQL:2011 표준은 현재 행을 반드시 포함하는 창 프레임의 제한을 해결하기 위해 창 함수 구문에 프레임 배제 절을 도입했습니다. 이러한 개선 이전에는 개발자들이 현재 값을 총합에서 빼는 방식으로 집계를 계산하기 위해 번거로운 자기 조인이나 대수적 조작에 의존해야 했습니다. 표준은 네 가지 배제 옵션을 정의합니다: EXCLUDE NO OTHERS, EXCLUDE CURRENT ROW, EXCLUDE GROUP, 및 EXCLUDE TIES, 이는 정렬된 파티션 내에서 집합 연산에 대한 결정론적 의미를 제공합니다.
문제
경쟁 지표를 분석할 때—예를 들면 동료 제품의 평균 판매 가격을 계산하면서 해당 제품 자체를 평균에서 제외하는 경우—쿼리는 현재를 제외한 모든 관련 행을 포함하는 창을 정의해야 합니다. 전통적인 창 함수인 AVG() OVER (PARTITION BY category)는 현재 행을 포함하므로 결과를 왜곡합니다. 서브쿼리나 조인을 통해 이를 구현하면 불필요한 복잡성과 성능 저하를 초래합니다, 특히 카드니언 곱이나 연관 서브쿼리를 포함하는 큰 파티션 데이터셋에서는 비용이 과도하게 비쌀 것입니다.
해결책
창 명세 내에서 프레임 배제 절을 활용합니다: AVG(price) OVER (PARTITION BY category ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW). 이 구문은 SQL 엔진에게 전체 파티션 프레임을 설정한 후 현재 행을 논리적으로 제거하고 집계를 계산하도록 지시합니다. 모든 동점(예: 동일 가격대의 모든 제품)을 제외해야 하는 시나리오에 대해, EXCLUDE GROUP은 현재 행과 그 조정 동료 모두를 제거하고, EXCLUDE TIES는 현재 행은 유지하지만 중복된 정렬 값을 제거합니다.
전자상거래 분석 팀은 "시장 위치" 보고서를 작성해야 합니다. 각 판매자의 전자 기기의 목록에 대해 해당 판매자의 가격과 같은 기기 모델을 제공하는 모든 다른 판매자의 평균 가격을 함께 보여주어야 합니다.
초기에는 model_id에서 일치하는 기본 키를 제외한 조인으로 목록 테이블이 자기 조인되는 접근 방식이 프로토타입으로 시도되었습니다. 장점: 모든 SQL 방언에서 보편적으로 지원되며 개념적으로 간단합니다. 단점: 실행 시 최악의 경우 O(n²) 복잡성을 나타내어 수백만 행에서 기하급수적으로 느려지며, 쿼리 최적화기가 종종 비균형 조인 조건으로 인해 비효율적인 실행 계획을 생성하고 해시 스필이나 중첩 루프 조인과 같은 문제를 발생시킵니다.
대수적 해결 방법도 평가되었으며, 각 모델에 대한 전역 합계와 카운트를 계산한 다음, (SUM(price) - current_price) / (COUNT(*) - 1)를 통해 다른 판매자들의 평균을 도출했습니다. 장점: 조인이 필요없고 단일 창 함수 스캔만 필요합니다. 단점: COUNT(*) = 1일 때(영으로 나누기) 또는 가격이 NULL일 때 치명적으로 실패하여 자세한 CASE 검사가 필요합니다; 또한, MEDIAN 또는 MODE와 같은 비대수적 집계에는 적용할 수 없습니다.
팀은 궁극적으로 EXCLUDE CURRENT ROW 프레임 규격을 선택했습니다. 이유: 선언적이며, 빈 프레임에 대해 자연스럽게 NULL을 반환하므로 NULL 확인 CASE 표현식이 필요 없으며, 최소한의 메모리 오버헤드로 단일 정렬 패스를 사용하여 O(n) 시간으로 실행됩니다. 이로 인해 보고서 생성 시간이 12분에서 10초 이내로 단축되었습니다.
결과: 현재 생산 보고서는 매일 5천만 개의 목록에 대해 경쟁업체 벤치마크를 정확하게 계산하며, 단일 판매자가 있는 희귀 항목을 처리할 때는 오류나 0 값을 대신해 NULL("경쟁 없음"으로 해석됨)을 표시합니다.
EXCLUDE CURRENT ROW가 RANGE 기반 대 ROWS 기반 창 프레임에서 어떻게 작동하며, 특히 동료 그룹과 관련하여?
창 프레임이 ROWS를 사용할 경우, EXCLUDE CURRENT ROW는 집계에서 정확히 하나의 물리적 행—현재 행을 제거합니다. 그러나 RANGE(예: RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING)를 사용할 때, "현재 행"은 지정된 범위 내에서 현재 행과 같은 정렬 값을 공유하는 모든 행을 개념적으로 나타냅니다. 이 컨텍스트에서 EXCLUDE CURRENT ROW는 특정 행 인스턴스만 제거하며, 기타 동료(동점)는 프레임에 남아 있게 됩니다. 반대로, EXCLUDE GROUP은 현재 행과 모든 동료를 프레임 단위에 관계없이 제거하며, EXCLUDE TIES는 현재 행을 제외한 모든 동료를 제거합니다. 후보자들은 종종 이러한 부분을 혼동하여 EXCLUDE CURRENT ROW와 RANGE가 EXCLUDE GROUP처럼 작동한다고 생각하며 중복 정렬 키가 존재할 때 잘못된 집계 결과를 초래합니다.
단일 행 파티션에서 EXCLUDE CURRENT ROW를 사용하는 쿼리가 NULL을 반환하는 이유는 무엇이며, 수동 뺄셈 방법과 어떻게 다른가요?
ANSI SQL 표준은 빈 집합에 대한 집계가 NULL을 반환한다고 정의합니다. EXCLUDE CURRENT ROW가 한 행만 있는 파티션에 적용되면, 프레임은 비어 있게 되어 AVG, SUM, 또는 COUNT가 자동으로 NULL을 생성합니다. 반면, (SUM(col) - col) / (COUNT(*) - 1)와 같은 수동 방법은 산술에서 0으로 나누기나 NULL 전파 문제를 겪으며, 개별 파티션을 안전하게 처리하기 위해 명시적인 CASE 문이 필요합니다. 후보자들은 종종 이 자동 NULL 처리 동작을 간과하여 0이나 현재 값을 기대하고, EXCLUDE가 경계 조건에 대해 더 나은 NULL 안전성을 제공한다는 점을 인식하지 못합니다.
EXCLUDE는 슬라이딩 윈도우처럼 임의의 프레임 범위와 결합될 수 있으며, 어떤 성능적인 함의가 있나요?
네, EXCLUDE 절은 BETWEEN 슬라이딩 윈도우를 포함한 모든 프레임 범위와 유효합니다. 예를 들어, AVG(val) OVER (ORDER BY time ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW)는 두 개의 이전 값과 두 개의 다음 값의 평균을 계산하여, 현재 포인트를 제외한 4포인트 이동 평균을 효과적으로 생성합니다. 성능 측면에서 현대 최적화기는 이를 링 버퍼나 데크를 사용한 스트리밍 알고리즘으로 구현하여 각 파티션에 대해 O(n) 복잡성을 유지합니다. 후보자들은 종종 EXCLUDE가 파티션의 전체 자료화를 요구하거나 UNBOUNDED 프레임만 작동한다고 가정하며, 중심 롤링 상관관계나 이상치 강건 스무딩과 같은 계산에 대해 임의의 프레임과 자연스럽게 통합됨을 간과합니다.