거래 사기를 탐지하는 SQL 패턴 완벽 가이드

거래 사기를 탐지하는 SQL 패턴 — 픽셀为单位의 이상 거래 탐지

거래 사기를 탐지하는 SQL 패턴 완벽 가이드

결제 사기는 금융 범죄에서 가장 흔한 유형 중 하나입니다. 특히 온라인 거래가 급증하면서 카드 사기, 계정 탈취, 혜택 부정 수급 등의 위협이 늘어나고 있습니다. 놀라운 사실은 머신러닝이나 그래프 데이터베이스 같은 첨단 기술보다, SQL을 활용한 기본적인 이상 거래 패턴 탐지가 실무에서 가장 먼저 효과를 발휘한다는 점입니다.

이 글에서는 실제 프로덕션 환경에서 검증된 여섯 가지 SQL 사기 탐지 패턴을 소개합니다. 각 패턴의 원리와 구현 방법, 임계값 조정 전략, 그리고 오탐 관리까지 폭넓게 다룹니다.

---

1. Velocity: 짧은 시간에 몰리는 거래 탐지

유출된 카드나 계정을 빠르게 소진하려는 공격자는 짧은 시간 안에 여러 거래를 시도합니다. Velocity 패턴은 이런 이상 거래 밀집 현상을 가장 먼저 포착합니다.

핵심 아이디어는 간단합니다. 일정 시간 창 안에서동일 카드로 수행된 거래 수가 임계값을 초과하면후라구를 세웁니다.

-- 슬라이딩 윈도우 기반 거래 속도 탐지WITH transaction_counts AS (SELECTcardholder_id,window_start,COUNT(*) AS tx_countFROM (SELECTcardholder_id,DATE_TRUNC('hour', transaction_time) AS window_start,transaction_idFROM transactionsWHERE transaction_time >= CURRENT_TIMESTAMP - INTERVAL '30 days') subGROUP BY cardholder_id, window_start)SELECTcardholder_id,window_start,tx_countFROM transaction_countsWHERE tx_count > :velocity_threshold;

슬라이딩 윈도우 방식으로 최근 5분 간 거래 수를 실시간 계산하려면 윈도 함수를 활용합니다.

SELECTcardholder_id,transaction_time,COUNT(*) OVER (PARTITION BY cardholder_idRANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW) AS tx_last_5minFROM transactions;

임계값 설정 팁: 1분, 5분, 1시간 버전을 동시에 돌려보면 조직의 특성 파악에 유리합니다. 카드 테스트 조직은 몇 초 안에 수십 건을 몰아 넣지만, 혜택 부정 조직은 하루에 걸쳐 천천히 움직이는 등 규모가 완전히 다릅니다.

오탐 관리: 자판기 충전 담당자, 선불카드 대량 충전 사용자 등 정상 활동에도 기준을 초과하는 경우가 있습니다. 첫 탐지 후 화이트리스트로 해당 카드를 예외 처리하는workflow를 반드시 갖추어야 합니다.

---

2. Impossible Travel: 물리적으로 불가능한 이동 탐지

Chicago에서 결제된 카드가 단 7분 만에 Los Angeles에서 다시 사용된다면, 하나는 반드시 가짜입니다. Impossible Travel 패턴은 복제 카드 사용을 잡아내는 가장 강한 신호로 꼽힙니다.

WITH lagged_locations AS (SELECTcardholder_id,transaction_time,merchant_city,merchant_state,LAG(transaction_time) OVER (PARTITION BY cardholder_idORDER BY transaction_time) AS prev_tx_time,LAG(merchant_city) OVER (PARTITION BY cardholder_idORDER BY transaction_time) AS prev_city,LAG(merchant_state) OVER (PARTITION BY cardholder_idORDER BY transaction_time) AS prev_stateFROM transactionsWHERE transaction_time >= CURRENT_TIMESTAMP - INTERVAL '30 days')SELECTcardholder_id,transaction_time,merchant_city,prev_city,prev_state,-- 두 거래 간 시간 차이(시간)EXTRACT(EPOCH FROM (transaction_time - prev_tx_time)) / 3600 AS hours_elapsed,-- 대권 거리 계산(mile)haversine_distance(prev_city, prev_state,merchant_city, merchant_state) AS distance_milesFROM lagged_locationsWHERE prev_tx_time IS NOT NULLAND EXTRACT(EPOCH FROM (transaction_time - prev_tx_time)) / 3600 < 1  -- 1시간 이내AND haversine_distance(prev_city, prev_state, merchant_city, merchant_state) / (EXTRACT(EPOCH FROM (transaction_time - prev_tx_time)) / 3600) > 600  -- 600mph 초과;

haversine_distance 함수는 대부분의 데이터 웨어하우스에서 기본 제공합니다. 두 위도와 경도 쌍 사이의 대권 거리를 계산합니다. 600mph 임계값은 상업용 제트기의 순항 속도인 575mph보다 여유 있게 설정한 값입니다.

추가 신호: 5분 안에동일 주에서 멀리 떨어진 두 도시, 1시간 안에 여러 우편 번호 구역, 10분 안에 국경을 넘는 거래 등 변형 패턴도 함께 활용하면 조직형 사기까지 탐지할 수 있습니다.

---

3. Amount Anomalies: 특정 금액대 이상 탐지

사기 조직은 카드 테스트 단계에서 $1.00, $99.99, $499.99 같은 특정 금액을 반복 사용합니다. Amount Anomalies 패턴은 이런 금액 패턴에서 사기 신호를 추출합니다.

SELECTcardholder_id,transaction_time,amount,merchant_categoryFROM transactionsWHERE-- 카드 테스트 신호: 작은 정수 금액(amount IN (1.00, 5.00, 10.00, 50.00, 100.00))OR-- 한도 회피 신호: 규칙 임계값 바로 아래(amount >= 99.50 AND amount < 100.00)OR(amount >= 499.50 AND amount < 500.00)OR-- 고가 테스트: 카드 번호 유효성 최종 확인(amount BETWEEN 500.00 AND 505.00)ORDER BY transaction_time DESC;

$1.00은 카드 번호 덤프가 실제로 작동하는지 확인하는 전형적인 카드 테스트 금액입니다. $99.99는 $100 이상일 때 요구되는 신분증 확인을 회피하려는 형태이고, $499.99는 일일 ATM 인출 한도변값 활용 패턴입니다.

주의할 점: 미국처럼 세금이 별도 부과되는 환경이 아닌 경우, 정확한 정액 금액이 오히려 정상 거래에서 더 흔할 수 있습니다. 반드시 해당 가맹점과 지역의 결제 문화에 맞게 임계값을 조정해야 합니다.

---

4. Merchant Anomalies: 가맹점 단위 이상 집중 탐지

주유기 카드 리더가 스키머에 감염되면, 해당 리더를 사용한 모든 카드의 정보가 유출됩니다. Merchant Anomalies 패턴은 이런 가맹점 단위 이상 징후를 포착합니다.

-- 자기 기준선 대비 이상 가맹점 탐지WITH merchant_baseline AS (SELECTmerchant_id,EXTRACT(HOUR FROM transaction_time) AS tx_hour,AVG(unique_card_count) AS avg_cards,STDDEV(unique_card_count) AS stddev_cardsFROM (SELECTmerchant_id,DATE_TRUNC('hour', transaction_time) AS tx_hour,COUNT(DISTINCT cardholder_id) AS unique_card_countFROM transactionsWHERE transaction_time >= CURRENT_TIMESTAMP - INTERVAL '60 days'GROUP BY merchant_id, DATE_TRUNC('hour', transaction_time)) hourly_statsGROUP BY merchant_id, EXTRACT(HOUR FROM transaction_time)),current_metrics AS (SELECTmerchant_id,EXTRACT(HOUR FROM transaction_time) AS tx_hour,COUNT(DISTINCT cardholder_id) AS current_cards,SUM(amount) AS total_amountFROM transactionsWHERE transaction_time >= CURRENT_TIMESTAMP - INTERVAL '7 days'GROUP BY merchant_id, EXTRACT(HOUR FROM transaction_time))SELECTc.merchant_id,c.tx_hour,c.current_cards,b.avg_cards,b.stddev_cards,ROUND(c.current_cards / NULLIF(b.avg_cards, 0), 2) AS ratio_to_baseline,c.total_amountFROM current_metrics cJOIN merchant_baseline bON c.merchant_id = b.merchant_idAND c.tx_hour = b.tx_hourWHERE c.current_cards > b.avg_cards * 3;  -- 기준선의 3배 이상

고유 카드 수가 과거 평균의 3배를 넘으면 해당 시간대를 이상 징후로 분류합니다. 3배 비율은 알림이 과도하게 쏟아지지 않으면서도 실제 이상을 잡아내는 균형점입니다.

계절성 고려: 동일 커피숍이라도 화요일 오후 2시와 토요일 오전 9시의 기준선은완전불동합니다. 168개 시간 버킷(7일 × 24시간)으로 나눈 자기 자신과의 과거 비교 방식이 정확한 이유입니다.

---

5. Off-Hours: 평소 사용 시간대 밖 거래 탐지

은행 직원인 A사은는 보통 평일 오전 9시부터 오후 5시 사이에 결제합니다. 갑자기 새벽 3시에 주유소에서 결제가 발생하면 카드가 다른 사람에게 사용되었거나 여행 중일 가능성이 높습니다.

-- 개인 평소 시간대 학습WITH cardholder_hours AS (SELECTcardholder_id,EXTRACT(HOUR FROM transaction_time) AS typical_hour,COUNT() AS tx_countFROM transactionsWHERE transaction_time >= CURRENT_TIMESTAMP - INTERVAL '90 days'GROUP BY cardholder_id, EXTRACT(HOUR FROM transaction_time)HAVING COUNT() >= 2  -- 2건 이상 거래한 시간대만 인정),hour_range AS (SELECTcardholder_id,MIN(typical_hour) AS earliest_hour,MAX(typical_hour) AS latest_hourFROM cardholder_hoursGROUP BY cardholder_id)SELECTt.cardholder_id,t.transaction_time,t.merchant_category,t.amount,r.earliest_hour,r.latest_hour,EXTRACT(HOUR FROM t.transaction_time) AS tx_hourFROM transactions tJOIN hour_range r ON t.cardholder_id = r.cardholder_idWHERE(EXTRACT(HOUR FROM t.transaction_time) < r.earliest_hourOR EXTRACT(HOUR FROM t.transaction_time) > r.latest_hour)AND r.earliest_hour IS NOT NULL;  -- 이력 있는 계정만

"해당 시간대에 2건 이상" 조건이 중요한 이유는 3개월 전 우연히 새벽 2시에 한 번 충전한 기록이 평소 시간대로 오인되는 것을 방지하기 위해서입니다.

한계점: 신규 계정은 이력 데이터가 없어서 기준선을 구성할 수 없습니다. 신규 계정에는 전체 사용자 평균 시간대 패턴을 적용하거나, 계정이 충분히 쌓일 때까지 이 패턴을 건너뛰는 것이 현실적입니다.

---

6. 윈도 함수로 신호 조합하기

앞선 다섯 패턴을 각각 탐지했다면, 이제타를 조합하여 각 거래에 대한 종합 사기 점수를 산출할 차례입니다. 윈도 함수로 만든 파생 컬럼을 활용하면 복잡한 사기 규칙이 단순 필터 표현식으로 줄어듭니다.

WITH transaction_features AS (SELECTt.*,-- 직전 거래 이후 경과 시간(초)EXTRACT(EPOCH FROM (t.transaction_time - LAG(t.transaction_time) OVER (PARTITION BY t.cardholder_idORDER BY t.transaction_time))) AS seconds_since_last_tx,-- 가맹점 변경 여부CASEWHEN LAG(t.merchant_id) OVER (PARTITION BY t.cardholder_idORDER BY t.transaction_time) != t.merchant_id THEN 1ELSE 0END AS merchant_changed,-- 최근 24시간 누적 금액SUM(t.amount) OVER (PARTITION BY t.cardholder_idRANGE BETWEEN INTERVAL '24 hours' PRECEDING AND CURRENT ROW) AS amount_24hr,-- 오늘 몇 번째 거래인지ROW_NUMBER() OVER (PARTITION BY t.cardholder_id, DATE(t.transaction_time)ORDER BY t.transaction_time) AS tx_sequence_todayFROM transactions tWHERE t.transaction_time >= CURRENT_TIMESTAMP - INTERVAL '7 days')SELECTcardholder_id,transaction_time,amount,seconds_since_last_tx,merchant_changed,amount_24hr,tx_sequence_today,-- 사기 점수: 여러 신호에 걸리는 거래 탐지CASEWHEN tx_sequence_today >= 5           -- 하루 5번째 이상 거래AND seconds_since_last_tx < 60       -- 직전 거래 후 60초 미만AND merchant_changed = 1             -- 가맹점 변경THEN 'HIGH_RISK'WHEN tx_sequence_today >= 3AND seconds_since_last_tx < 120THEN 'MEDIUM_RISK'ELSE 'NORMAL'END AS fraud_risk_levelFROM transaction_features;

한 번 만들어둔 파생 컬럼은 팀의 모든 분석가가 재사용할 수 있습니다. 다음 사기 패턴 추가가 별도 프로젝트가 아니라 단순 SQL 필터 추가가 됩니다.

---

실무 적용 시 반드시 알아야 할 세 가지 주의사항

NULL 처리

실제 거래 테이블은 입문서와 다르게 NULL을 잘 활용하지 않는 경우가 많습니다. 많은 레거시 시스템은 "종료일 없음"에 9999-12-31, "시작일 없음"에 0001-01-01 같은 센티널 값을 사용합니다. IS NULL로 필터링하면 이런 행을 조용히 놓칠 수 있습니다.

오탐 관리

모든 규칙은 이상하지만 합법적인 행동을 하는 실제 카드를 보유자를 잡을 수 있습니다. 플래그가 붙은 건에는 반드시 사람의 검토가 필요합니다. 단일 규칙으로 자동 차단하면 정직한 고객을 잃을 수 있습니다. 실제 사기와 아닌 것을 기준으로 임계값을 조정하는 피드백 루프가 필수입니다.

비용 관리

큰 파티션에서 윈도 함수는 저렴하지 않습니다. 반드시 날짜 범위를 미리 필터링한 뒤 윈도 함수를 적용해야 합니다. 전체 2년치 데이터에 LAG()를 먼저 돌리고 나중에 WHERE를 붙이면 데이터 웨어하우스 크레딧이 빠르게 소진됩니다.

---

마무리

거래 사기 탐지는 한 가지 패턴만으로는 절대 충분하지 않습니다. 각 패턴에는 명확한 한계가 있습니다. Velocity는 자판기 운영자에게 오탐이 발생하고, Impossible Travel은 한 대도시권 내부 사기를 놓치고, 금액 이상은 혜택 거래 맥락에서는 효과가 제한적입니다.

실무에서는 모든 패턴을 병렬로 실행하고, 각 거래를 여러 신호에 걸쳐 점수화하는 방식이 가장 효과적입니다. 세 개 또는 네 개 신호에 동시에 걸리는 거래는 거의 항상 사기입니다. 한 개 신호에만 걸리는 거래는 여행 중인 정상 카드의 특이한 사용일 수 있습니다.

사기 탐지를 처음 시작한다면 Velocity부터 시작하는 것이 가장 좋습니다. 유용한 양의 사기를 빠르게 드러내며, 정상 활동의 오탐 비율이 비교적 낮고, 실행 비용도 낮기 때문입니다.

핵심 정리:

• Velocity — 짧은 시간 거래 밀집 탐지, 오탐은 화이트리스트로 관리

• Impossible Travel — 600mph 이상 이동, 복제 카드 포착에 강함

• Amount Anomalies — $1.00, $99.99, $499.99 등 사기 선호 금액대

• Merchant Anomalies — 자기 과거 기준선 대비 3배 이상 증가 구간

• Off-Hours — 개인 평소 시간대 밖 거래, 90일 이력 필요

• 신호 조합 — 윈도 함수로 점수화, 세 개 이상 신호면 거의 사기

---

📚 출처

• 거래 사기를 탐지하는 데 사용하는 SQL 패턴

• Six SQL Patterns I Use to Catch Transaction Fraud

• Transaction Velocity Fraud Detection with SQL Windows

• SQL Fraud Detection Patterns in 2026: Why They Still Matter

---

📚 출처

• https://news.hada.io/topic?id=29571