"행을 위한 정규식": MATCH_RECOGNIZE로 SQL 패턴 감지 간소화하기

사이버 보안 분야에서 근무하며 로그인 시도를 추적하는 테이블이 있다고 가정해 보겠습니다. 이 테이블에는 각 로그인 시도의 성공 또는 실패 여부와 시도한 시간이 기록되어 있습니다. 이상한 로그인 패턴을 찾고 싶다면, "어떤 사용자가 연속으로 로그인을 실패한 후 성공했는가?"라는 질문을 던질 수 있습니다. 표준 SQL로 이러한 의심스러운 활동을 찾아내는 것은 까다로운 작업입니다. SQL은 행을 타임라인이 없는 순서 없는 사실들의 집합으로 취급하며, 이벤트 시퀀스라는 고유한 개념이 없기 때문입니다.

사용자별로 실패한 로그인 횟수를 세어볼 수는 있지만, 단순히 횟수만으로는 이러한 로그인 시도가 짧은 시간 내에 발생했는지 아니면 한 달 동안 분산되어 발생했는지 파악할 수 없습니다. 또한 실패 직후에 로그인 성공이 있었는지도 알 수 없습니다. SQL에서 이를 구현하려면 여러 공통 테이블 표현식(CTE)을 체인처럼 연결하고, 시간 윈도우를 첫 번째 실패에 고정한 다음 이후의 각 행을 확인하는 복잡한 쿼리를 작성해야 합니다. 

MATCH_RECOGNIZE는 이 과정을 단순화합니다. 이제 Databricks 컴퓨트(Lakehouse Real-Time 포함)에서 사용할 수 있는 MATCH_RECOGNIZE를 통해 행에 대한 정규 표현식처럼 관심 있는 시퀀스를 직접 정의할 수 있습니다. 이제 단 하나의 SQL 절로 패턴 매칭을 처리할 수 있어, "gaps and islands(갭과 아일랜드)" 로직에 의존하는 지나치게 복잡한 SQL을 작성할 필요가 없습니다.

다양한 산업 분야에서 MATCH_RECOGNIZE를 통해 시퀀스 탐지를 얼마나 쉽게 수행할 수 있는지 산업별 예시를 통해 살펴보겠습니다.

사이버 보안: 의심스러운 로그인 이상 징후 식별

인증 로그에서 크리덴셜 스터핑(credential stuffing)을 탐지하려는 경우, 단순히 로그인 시도 횟수만 세면 오탐(false positive)이 발생할 수 있습니다. 구체적으로는 특정 짧은 시간 내에 5회 이상의 로그인 실패가 발생한 직후, 바로 로그인이 성공하는 등의 고빈도 급증 현상을 감지해야 합니다.

표준 COUNT() OVER (PARTITION BY user_id ORDER BY event_time) 윈도우 함수는 특정 시간 범위 내에 몇 번의 실패가 발생했는지는 알려줄 수 있지만, 슬라이딩 시간 윈도우를 특정 시퀀스의 첫 번째 실패에 고정하기 어렵고, 성공이 발생한 후의 시퀀스를 깔끔하게 분리해 내기도 어렵습니다.

MATCH_RECOGNIZE를 사용하면 DEFINE 블록 내부에서 직접 FIRST(FAIL.event_time)을 사용하여 최초 실패 시도의 타임스탬프를 고정할 수 있습니다. 이후의 모든 FAIL 이벤트는 SUCCESS 상태로 전환되기 전에 해당 첫 번째 시도로부터 1시간 이내에 발생하는지 동적으로 확인됩니다.

금융 분석: V자형 주가 추세 감지

모든 시장 데이터 분석가는 주가가 하락했다가 갑자기 다시 상승하기 시작하는 순간인 가격 반등에 주목합니다. 이러한 형태를 "V자형"이라고 하며, 표준 SQL에서 이를 찾으려면 "gaps and islands(갭과 아일랜드)"라는 기법을 사용해야 합니다. SQL에는 추세에 대한 기본 개념이 없기 때문에, V자형이 어디서 시작하고 끝나는지 묻기 전에 먼저 행을 "아일랜드"(가격이 동일한 방향으로 움직이는 연속된 구간)로 수동으로 나누어야 합니다.

실제로는 LAG와 LEAD를 사용하여 각 행을 인접한 행과 비교하고, 방향이 바뀔 때마다 증가하는 누적 카운터를 구축하여(각 아일랜드에 대한 그룹 ID를 생성), HAVING 필터를 작성해 각 아일랜드의 형태와 경계를 확인해야 합니다. "주가가 언제 하락했다가 회복되었는가?"라는 단순한 질문에 답하기 위해 너무 많은 부가적인 작업이 필요합니다.

MATCH_RECOGNIZE 절을 사용하면 이러한 번거로운 작업이 필요 없습니다. 데이터를 심볼(symbol)별로 파티션하고 시간순으로 정렬한 다음, 정규 표현식과 유사한 상태 시퀀스로 V자형 추세의 형태를 정의하기만 하면 됩니다.

이커머스: 결제 이탈 감지

제품 관리자는 구매 의도는 높지만 구매를 완료하지 않은 사용자를 찾고자 합니다. 실제 구매 의사를 보였으나 이후 아무런 활동이 없는 사용자는 매우 가치 있는 신호입니다. 이러한 사용자 그룹을 식별하면 알림을 보낼 사용자를 결정하고, 잠재적 기회와 후속 조치를 통해 회복 가능한 비율을 쉽게 측정할 수 있으며, 이 코호트의 다른 사용자와 비교하여 새로운 인사이트(예: 특정 제품의 가격이 너무 높게 책정됨)를 발견하는 데 도움이 됩니다. 가치가 높은 미전환 퍼널은 다음과 같은 사용자를 추적합니다.

1. 제품 페이지를 2회 이상 조회한 사용자 (높은 관심을 나타내는 VIEW 2회 이상)
2. 장바구니에 상품을 담은 사용자 (ADD_TO_CART)
3. 최종적으로 세션을 이탈한 사용자 (시간 필터 사용)

마지막 단계는 값이 아니라 사용자 활동을 기반으로 한 시간 범위를 기준으로 합니다. 매칭할 "이탈(abandon)" 행이나 "결제 오류(check out error)"가 존재하지 않으며, 사용자가 단순히 활동을 멈춘 것입니다. 기존 SQL에서는 장바구니에 상품을 담은 후 아무런 일도 일어나지 않았고 장바구니가 방치되었다고 판단할 만큼 충분한 대기 시간이 지났음을 증명하기 위해 NOT EXISTS 서브쿼리, 셀프 조인, 윈도우 함수 등을 사용하여 부정 조건(negative)을 증명해야 합니다. 

MATCH_RECOGNIZE는 파티션 끝 앵커인 $를 사용하여 "이후에 아무 일도 일어나지 않음"을 직접 표현하며, 이를 통해 장바구니 담기가 세션에서 마지막으로 기록된 이벤트가 되도록 강제합니다. 대기 시간 윈도우에 대한 시간 필터를 추가하면 셀프 조인 없이도 타임아웃 기반의 이탈 규칙을 만들 수 있습니다.

제조 / IoT: 센서 데이터 기반 장비 고장 예측

예측 유지보수는 트렌드와 패턴을 감지하는 것에 달려 있습니다. 사용 중인 모든 장비는 내부 온도가 상승하는 경향이 있지만, 지속적인 온도 상승에 이어 진동 급증이 발생하는 시퀀스는 곧 발생할 고장의 신호일 수 있습니다. 

기존 SQL에서는 위험한 트렌드를 지속적으로 감지하기 위해 연속적인 행 대 행(row-by-row) 비교가 필요합니다. MATCH_RECOGNIZE 는 행 대 행 논리를 기본적으로 처리합니다. DEFINE 절 내부에서는 PREV 및 NEXT 함수(LAG 및 LEAD 와 유사하게 작동)를 사용할 수 있습니다. 즉, 온도 상승 규칙을 설정하는 것은 temperature > PREV(temperature) 를 작성하는 것만큼 간단합니다.

지금 Lakehouse에서 Match Recognize를 사용해 보세요

이제 데이터 패턴을 발견하고 이벤트 시퀀스 분석을 간소화하는 것이 그 어느 때보다 쉬워졌습니다. MATCH_RECOGNIZE 를 사용하면 논리적인 방식으로 패턴 매칭을 위한 코드를 더 적게 작성할 수 있습니다. 이 절은 검증하기 더 쉽고, 유지관리하기 쉬우며, 업데이트가 간편합니다.

• 문서 탐색하기: 공식 SQL 참조 문서를 자세히 살펴보고 고급 패턴 구문, 수량자, 측정값 집계에 대해 자세히 알아보세요.
• 워크스페이스에서 사용해 보기: 자체 로그 스트림, 클릭스트림 세션 또는 시계열 원격 측정 데이터로 위의 예시를 Databricks SQL 또는 Lakehouse//RT에서 테스트해 보세요.
• 기존 파이프라인 마이그레이션: 가장 복잡한 윈도우 함수 및 셀프 조인 CTE를 식별하고, Genie Code의 도움을 받아 더 간단한 MATCH_RECOGNIZE 쿼리로 재작성해 보세요.

가장 우수한 데이터 웨어하우스는 Lakehouse입니다. 기본 기능이 계속 확장되어 단일 통합 플랫폼에서 더욱 강력한 분석을 수행할 수 있도록 지원합니다. 

(이 글은 AI의 도움을 받아 번역되었습니다. 원문이 궁금하시다면 여기를 클릭해 주세요)