질문 배경. 누적 고유 고객 유치 또는 시간에 따른 고유 SKU 소개와 같은 측정을 추적하는 분석 작업에서 고유 개수 계산의 필요성이 발생했습니다. ANSI SQL:2003 윈도우 함수 확장 이전에 분석가들은 자기 조인 또는 상관된 서브쿼리에 의존했으며, 이는 현대 데이터 볼륨에 대해 허용할 수 없는 제곱시간 복잡도를 초래했습니다. 윈도우 함수의 표준화는 절차적 루프 없이 실행 집합을 유지하는 선형 시간, 집합 기반 메커니즘을 제공했습니다.
문제 설명. ANSI SQL은 윈도우 집계 함수 내에서 DISTINCT 키워드를 명시적으로 금지합니다(예: COUNT(DISTINCT col) OVER (...)). 이 제한으로 인해 누적 또는 슬라이딩 프레임 내에서 고유 값을 직접 계산할 수 없게 됩니다. 핵심 도전 과제는 각 개체의 첫 번째 출현을 파티션의 정렬 순서에서 식별하고 이러한 이진 플래그(첫 출현 = 1, 아니면 = 0)를 점진적으로 합산하는 것입니다.
해결책. 표준 방법은 **ROW_NUMBER()**를 결합하여 첫 번째 발생을 플래그하고 조건부 SUM() 윈도우 함수를 사용하는 것입니다. **ROW_NUMBER()**를 개체 식별자로 파티셔닝하여 연대순으로 첫 번째 발생이 값 1을 받습니다. 이후의 출현은 증가하는 정수를 받습니다. 외부 쿼리는 1일 때만 1을 반환하는 사례 표현식을 합산하여 무한 전방 프레임에서 평가합니다.
SELECT event_date, region_id, user_id, SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY region_id ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_unique_users FROM ( SELECT event_date, region_id, user_id, ROW_NUMBER() OVER ( PARTITION BY region_id, user_id ORDER BY event_date, event_id -- 타이브레이커로서 event_id 사용 ) AS rn FROM user_activity ) flagged;
문제 설명. 한 핀테크 스타트업이 회계 연도 내내 판매 지역별로 등록된 누적 고유 상인 수를 추적하여 규제 준수를 모니터링해야 했습니다. 그들의 merchant_signups 테이블은 region_code, merchant_id, signup_timestamp와 함께 1억 2000만 개의 행을 포함하고 있었습니다. 기존 Python 배치 작업은 매일 밤 이러한 지표를 계산하는 데 35분이 걸렸으며, 보고서 지연과 구식 대시보드 데이터를 초래했습니다. 요구 사항은 클라우드 데이터 웨어하우스 간의 이식성을 위해 엄격한 ANSI SQL 내에서 실시간 누적 개수를 생성하는 것이었습니다.
해결책 A: 자기 조인 방법. 이 방법은 일치하는 지역 및 이전 타임스탬프에서 테이블을 자기 자신과 조인하여 외부 행당 고유 상인을 계산합니다. 장점: 윈도우 함수 지원이 필요하지 않으며 구식 SQL-92 엔진에서 작동합니다. 단점: 이 알고리즘은 O(n²) 복잡성을 보이며, 수백만 개의 행에 대해 임시 스토리지를 수 테라바이트 소모하는 중간 카르테시안 곱을 생성하고 몇 시간 내에 완료되지 않습니다. 따라서 운영적으로 실현 불가능합니다.
해결책 B: 상관된 스칼라 서브쿼리. 여기서 SELECT 절은 서브쿼리를 포함합니다: (SELECT COUNT(DISTINCT merchant_id) FROM merchant_signups m2 WHERE m2.region_code = m1.region_code AND m2.signup_timestamp <= m1.signup_timestamp). 장점: 선언적이며 논리적으로 쉽게 읽을 수 있습니다. 단점: 서브쿼리는 각 행마다 한 번 실행되며(1억 2000만 번), 프레디케이트 푸시다운을 방해하여 대규모 랜덤 I/O를 유발합니다. 데이터베이스 최적화기는 서로 다른 시간 범위에서 고유 집계를 비상관적으로 처리할 수 없으며, 추정 실행 시간이 90분을 초과합니다.
해결책 C: ANSI SQL 윈도우 함수 기법. 위의 코드 예제처럼 첫 번째 출현을 식별하기 위해 **ROW_NUMBER()**를 사용한 다음 실행 **SUM()**을 사용합니다. 장점: O(n log n) 복잡성과 제한 메모리 사용으로 최적화기의 윈도우 스풀링 기능을 활용하여 단일 테이블 스캔을 수행합니다. 단점: 시간이 동일한 경우에는 신중한 처리가 필요합니다; 두 개의 등록이 동일한 타임스탬프를 공유하면 비결정적 정렬로 인해 잘못하게 두 번 계산될 수 있습니다. 고유한 타이브레이커(예: event_id)가 ORDER BY 절에 추가되어야 합니다.
선택된 해결책 및 결과. 해결책 C가 구현되었습니다. 결정론적인 첫 번째 출현 검출을 보장하기 위해 event_id를 ORDER BY에 포함하여, 쿼리는 기존 클러스터에서 4분 만에 실행되었습니다—9배의 개선입니다. 결과적으로 리얼 타임 규제 준수 대시보드를 사용할 수 있게 되었고, 리스크 담당자가 ETL 지연 없이 온보딩 다양성을 모니터링 할 수 있게 되며, 쿼리는 수정 없이 PostgreSQL, Snowflake, BigQuery에 완전히 이식 가능합니다.
왜 COUNT(DISTINCT column) OVER (ORDER BY ...)가 엄격한 ANSI SQL에서 구문 오류를 발생시키는가?
SQL 표준은 명시적으로 윈도우 집계 함수인 COUNT, SUM 또는 AVG의 인수 내에서 DISTINCT 키워드를 금지합니다. 특정 공급자(예: PostgreSQL 16+, Oracle)가 이를 독점적 확장으로 제공하기는 하지만, ANSI SQL:2011 및 이전 버전에서는 윈도우 집계가 정의된 프레임 내의 모든 행에서 작동해야 합니다. 이 제한은 스트리밍 평가 중에 각 가능한 윈도우 프레임에 대해 고유 집합 해시 테이블을 유지하는 것이 표준 문법에 의해 요구되지 않기 때문입니다. 후보자들은 DISTINCT가 OVER 절이 없는 표준 집계 함수에서만 허용되거나 PERCENTILE_CONT와 같은 역분포 함수 내에서만 허용된다는 것을 인식해야 합니다. 그러나 결코 윈도우 고유 개수로는 불가능합니다.
개체의 "첫 번째" 발생을 결정할 때 중복된 타임스탬프를 어떻게 처리합니까?
**ROW_NUMBER()**는 ORDER BY 절이 전체 정렬을 지정하지 않으면 동Tie 간 임의 값을 할당합니다. 만약 한 상인이 동일한 타임스탬프를 가진 두 개의 항목이 있다면, 비결정적 정렬이 이루어질 경우 두 행 모두 rn = 1을 받을 수 있어 누적 개수가 잘못하게 두 번 증가할 수 있습니다. 해결 방법은 고유한 기본 키나 자동 증가 ID를 ORDER BY 절에 추가하는 것입니다: ORDER BY signup_timestamp, merchant_signup_id. 이는 더 일찍 할당된 ID가 첫 번째 발생으로 고려되도록 하여 실행 고유 개수의 수학적 무결성을 유지합니다.
이 기법을 고정된 행 수 창(예: 마지막 100개 거래)에 대해 이동 고유 개수로 조정할 수 있습니까, 아니면 무한 전방 대신에?
아니요, 순수한 ANSI SQL로는 효율적으로 불가능합니다. 무한 전방 방법이 성공하는 이유는 고유성이 단조롭기 때문입니다. 개체가 나타나면 영원히 "계산됨"으로 남아 있습니다. 슬라이딩 윈도우(예: ROWS BETWEEN 100 PRECEDING AND CURRENT ROW)에서, 창을 나가는 개체는 개수를 감소시켜야 하며, 현재 프레임 내에서 해당 개체의 유일한 인스턴스를 나타내는지 여부에 대한 지식이 필요합니다. ANSI SQL은 이렇게 효율적으로 이gress를 추적할 배열 집계 또는 집합 차이 연산자를 갖고 있지 않습니다. 이를 구현하려면 재귀 CTE(이 경우 O(n²)로 저하됨) 또는 ARRAY_AGG와 같은 독점적 확장을 필요로 하며, 모두 엄격한 ANSI 준수를 위반합니다.