이 질문은 SQL 표준이 SQL-92에서 SQL:2003으로 발전하면서 등장했습니다. 이 때 윈도우 함수가 표준에 공식적으로 도입되었습니다. 이러한 발전 이전에는 개발자들이 절차적 커서나 계산 비용이 많이 드는 자기 조인을 사용하여 순차적 문제를 해결했습니다. gaps-and-islands 패턴은 처리 알고리즘에서 선언적, 집합 기반 논리로의 패러다임 전환을 나타내며, 이 논리는 처리 단계를 정의하는 것이 아니라 결과 집합을 정의합니다.
타임스탬프, ID 또는 날짜와 같은 순차적 값을 포함하는 테이블에서 작업할 때, 연속 값을 인접한 블록(섬)으로 그룹화하면서 이를 차단(갭)과 구별해야 합니다. 기본적인 도전 과제는 ANSI SQL 테이블이 순서가 없는 수학적 집합을 나타내는데, 반면에 시퀀스를 감지하려면 명시적인 정렬이 필요하다는 것입니다. 전통적인 GROUP BY 절은 유사한 값을 집계하지만, 연속성을 식별하는 데 필요한 순차적 관계를 파괴합니다.
전체 데이터 세트에서 **ROW_NUMBER()**의 산술적 차이와 그룹화 키로 나눈 **ROW_NUMBER()**를 활용하여 고정된 섬 식별자를 생성합니다. 이 기술은 동일한 연속 시퀀스 내의 모든 행에 대해 동일하게 계산된 값을 생성하여 표준 집계를 통해 섬을 재구성할 수 있게 합니다.
WITH numbered AS ( SELECT event_date, ROW_NUMBER() OVER (ORDER BY event_date) AS rn_global, event_date - ROW_NUMBER() OVER (ORDER BY event_date) AS island_grp FROM events ) SELECT MIN(event_date) AS island_start, MAX(event_date) AS island_end, COUNT(*) AS consecutive_days FROM numbered GROUP BY island_grp;
소매 분석 팀은 PostgreSQL에 저장된 클릭스트림 데이터에서 고객 쇼핑 세션을 재구성해야 했습니다. 시스템은 user_id 및 event_time을 포함하는 수백만 개의 이벤트를 기록했지만, 미리 계산된 세션 식별자가 부족했습니다. 비즈니스 요구 사항은 세션을 비활동으로 인해 격차가 30분을 초과하지 않는 이벤트의 순서로 정의했습니다.
첫 번째 접근법은 각 이벤트의 즉각적인 이전자를 찾기 위해 상관 서브쿼리가 있는 자기 조인을 사용하는 것이었습니다. 이 방법은 O(n²) 행 비교가 필요하여, 하루에 오백만 개 이상의 행을 처리할 때 쿼리 시간 초과가 발생했지만, 현대 윈도우 함수가 부족한 레거시 SQL-92 시스템과의 호환성을 유지했습니다.
그 후 팀은 절차적 변수를 사용하여 이벤트를 행별로 반복하며 세션 상태를 유지하기 위해 pl/pgSQL 커서를 평가했습니다. 이 접근법은 응용 프로그램 개발자에게 친숙한 직관적인 논리를 제공했지만 집합 기반 처리 원칙을 포기하고 일일 배치를 완료하는 데 4시간 이상이 소요되어, 수용할 수 없는 ETL 지연 및 심각한 테이블 잠금 문제를 일으켰습니다.
선택된 솔루션은 ANSI SQL 윈도우 함수만을 사용했습니다. 팀은 **LAG()**를 적용하여 사용자당 이전 타임스탬프를 캡처하고 시간 차이를 계산하여 30분을 초과하는 경우 세션 경계를 식별했습니다. 조건부 누적 합계는 고유한 세션 식별자를 생성하여 집합 기반 집계를 가능하게 했습니다. 이 방법은 전체 데이터 세트를 8분 만에 처리했으며, 볼륨에 따라 선형적으로 확장되었고, 공급업체 특정 구문 수정 없이도 Oracle, SQL Server, PostgreSQL 간에 휴대 가능했습니다.
타임스탬프를 시간을 기준으로 잘라내고 그 값으로 그룹화하여 세션을 찾을 수는 없나요?
DATE_TRUNC 또는 유사한 함수를 사용하여 타임스탬프를 잘라내면 인공 경계가 시계 시간에 설정되고 상대적 시간 차이를 강요합니다. 10:55와 11:05에 발생한 두 이벤트는 10분 차이에도 불구하고 서로 다른 그룹으로 분리되고, 10:01과 10:59의 이벤트는 58분의 차이가 있음에도 불구하고 함께 그룹화됩니다. 실제 세션 감지에는 각 이벤트의 즉각적인 이전자와의 간격을 계산해야 하며, 달력 경계에 맞추는 것이 아닙니다.
정렬 열의 NULL 값이 LAG 또는 LEAD를 사용한 섬 탐지에 어떤 영향을 미칩니까?
LAG와 LEAD는 각각 각 파티션의 첫 번째 및 마지막 행에 대해 NULL을 반환합니다. 누적된 타임스탬프에서 현재 타임스탬프를 빼서 격차를 계산할 때, NULL과의 산술 연산은 NULL 결과를 생성하므로 전체 섬이 집계에서 사라질 수 있습니다. 섬이 파티션 경계에서 파편화되지 않도록 하려면 LAG의 선택적 default 매개변수를 사용해야 하며(예: LAG(event_time, 1, event_time) OVER (...)) COALESCE로 NULL을 명시적으로 처리해야 합니다.
사용자별, 디바이스별 등 여러 범주를 동시에 확인할 때 어떤 변화가 있나요?
후보자들은 종종 윈도우 함수에서 PARTITION BY 절을 생략하여 전 범위에 대해 ROW_NUMBER를 계산하고 범주별로 계산하지 않습니다. user_id나 동등한 그룹화 열로 파티션을 나누지 않으면 서로 다른 사용자의 섬이 잘못 병합되어 시간적으로 일치할 경우 서로 누적됩니다. 섬 계산에 관여하는 모든 윈도우 함수는 PARTITION BY user_id를 포함해야 하며, 이는 각 고유 엔티티에 대해 산술을 재설정하여 파티션별로 독립된 섬 탐지를 유지합니다.