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

핵심 #1

사기 탐지는 머신러닝보다 먼저 테이블과 조인을 제대로 잡고, 속도·위치·금액·가맹점·시간대의 이상 패턴을 SQL로 찾는 데서 시작되는 경우가 많음Velocity는 짧은 시간 안에 같은 카드 소유자 거래가 몰리는 구간을 찾으며, 시간 창·임계값 조정과 오탐 화이트리스트가 필요함Impos…

핵심 #2

거래 사기를 탐지하는 데 사용하는 SQL 패턴 (analytics.fixelsmith.com)17P by GN⁺ 8일전 | ★ favorite | 댓글 4개

핵심 #3

사기 탐지는 머신러닝보다 먼저 테이블과 조인을 제대로 잡고, 속도·위치·금액·가맹점·시간대의 이상 패턴을 SQL로 찾는 데서 시작되는 경우가 많음

핵심 #4

Velocity는 짧은 시간 안에 같은 카드 소유자 거래가 몰리는 구간을 찾으며, 시간 창·임계값 조정과 오탐 화이트리스트가 필요함

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

사기 탐지는 머신러닝보다 먼저 테이블과 조인을 제대로 잡고, 속도·위치·금액·가맹점·시간대의 이상 패턴을 SQL로 찾는 데서 시작되는 경우가 많음Velocity는 짧은 시간 안에 같은 카드 소유자 거래가 몰리는 구간을 찾으며, 시간 창·임계값 조정과 오탐 화이트리스트가 필요함Impos… 거래 사기를 탐지하는 데 사용하는 SQL 패턴 (analytics.fixelsmith.com)17P by GN⁺ 8일전 | ★ favorite | 댓글 4개 사기 탐지는 머신러닝보다 먼저 테이블과 조인을 제대로 잡고, 속도·위치·금액·가맹점·시간대의 이상 패턴을 SQL로 찾는 데서 시작되는 경우가 많음 Velocity는 짧은 시간 안에 같은 카드 소유자 거래가 몰리는 구간을 찾으며, 시간 창·임계값 조정과 오탐 화이트리스트가 필요함 Impossible travel은 LAG()와 거리 계산으로 Chicago 결제 7분 뒤 Los Angeles 결제처럼 물리적으로 불가능한 이동을 강한 복제 카드 신호로 잡아냄 금액 이상은 $1.00, $99.99, $499.99처럼 카드 테스트나 규칙 회피를 시사하는 금액대를 찾지만, 혜택 거래에는 잘 맞지 않음 가맹점 급증, 평소 시간대 밖 거래, 윈도 함수 파생 컬럼을 함께 쓰면 거래를 여러 신호로 점수화하고 반복 주기를 몇 주에서 몇 시간으로 줄일 수 있음 거래 데이터에서 사기 징후를 찾는 SQL 패턴 사기 탐지는 머신러닝이나 그래프 데이터베이스보다 먼저 올바른 테이블과 조인, 그리고 이상한 거래 형태를 찾는 SQL에서 시작되는 경우가 많음 신용카드, 의료 청구, 전자상거래, POS, 정부 지원 혜택 프로그램처럼 돈이 이동하고 로그가 남는 데이터에 적용 가능함 새 데이터셋에서는 보통 속도, 불가능한 이동, 금액 이상, 가맹점 집중, 비정상 시간대, 윈도 함수 기반 신호 순서로 패턴을 쌓아감 1. Velocity: 짧은 시간에 과도한 거래 훔친 카드나 계정을 빨리 소진하려는 경우, 같은 카드 소유자에게 짧은 시간 안에 거래가 몰리는 패턴이 나타남 기본 쿼리는 최근 30일 거래를 시간 단위로 묶고, cardholder_id별 거래 수가 기준을 넘는 구간을 찾음 핵심 조정값은 시간 창 크기와 거래 수 임계값임 1분, 5분, 1시간 버전을 병렬로 돌려 비교 가능함 카드 테스트 조직은 몇 초 안에 거래를 몰아넣고, 혜택 부정 거래 조직은 한나절에 걸쳐 움직일 수 있어 스케일이 다름 정상 사용자도 기준을 넘을 수 있음 자판기를 관리하는 운영자 선불카드를 대량 충전하는 사람 첫 탐색 이후에는 이런 오탐 대상 화이트리스트가 필요함 슬라이딩 윈도 방식은 COUNT(*) OVER (...) RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW로 최근 5분 내 거래 수를 계산함 QUALIFY는 Snowflake, BigQuery, Databricks, Teradata에서 동작함 Postgres에서는 전체 쿼리를 CTE로 감싸고 바깥에서 필터링해야 함 2. Impossible travel: 물리적으로 불가능한 이동 한 카드가 Chicago에서 결제되고 7분 뒤 Los Angeles에서 결제되면, 둘 중 하나는 가짜일 가능성이 큼 이 패턴은 복제 카드를 잡는 강한 신호이며, 한 카드가 몇 분 안에 먼 두 장소에 있을 정상 사유는 거의 없음 쿼리는 LAG()로 직전 거래의 시간과 위치를 가져오고, 현재 위치와 직전 위치 사이의 거리와 시간을 계산함 haversine은 대권 거리(great-circle distance) 를 계산하는 함수임 대부분의 데이터 웨어하우스가 제공함 없으면 직접 작성할 수 있는 수준의 함수임 예시 임계값은 600mph임 상업용 제트기의 순항 속도가 약 575mph이므로, 비행기로도 불가능한 속도라는 뜻임 100mph로 낮추면 빠른 지상 이동도 잡을 수 있지만, 실제 항공 여행객이나 부모가 자녀를 태워오는 정상 거래까지 걸리기 시작함 같은 계열에서 추가로 볼 수 있는 신호가 있음 5분 안에 같은 주의 먼 두 도시에서 거래되면 지역 복제 조직을 시사할 수 있음 한 시간 안에 여러 ZIP 코드에서 거래되면 한 지역에서 움직이는 스키머 조직을 시사할 수 있음 10분 안에 국경을 넘는 거래는 국제 조직 신호가 될 수 있음 3. Amount anomalies: 특정 금액대의 이상 거래 사기에서는 자주 나타나지만 정상 사용에서는 드문 금액 패턴이 있음 예시 조건은 다음 금액대를 찾음 $99.50 이상 $100.00 미만 $499.50 이상 $500.00 미만 작은 정수 달러 금액은 대체로 카드 테스트 신호임 카드 번호 덤프에서 얻은 번호가 실제로 동작하는지 확인한 뒤 재판매하려는 흐름임 실제 카드 소유자가 정확히 $1.00짜리 물건을 사는 경우는 드묾 커피는 $4.73, 주유는 $52.81처럼 정확히 rounded 금액이 아닐 가능성이 높음 임계값 바로 아래 금액은 다른 의미를 가짐 $99.99는 많은 곳에서 $100부터 신분증 확인을 요구하는 선을 피하려는 형태일 수 있음 $499.99는 $500 일일 ATM 한도를 피하려는 형태일 수 있음 거래자가 규칙을 알고 그 아래에 머무르는 신호가 됨 혜택 거래에서는 Rounded 금액 패턴이 크게 도움이 되지 않음 혜택은 같은 방식으로 카드 테스트되지 않음 보통은 중복 수급자가 더 중요한 신호가 됨 4. Suspicious merchants: 가맹점 단위의 이상 집중 주유기 카드 리더처럼 특정 리더가 스키머에 감염되면, 한 건이 아니라 수십 건의 사기로 이어질 수 있음 해당 리더를 몇 주간 사용한 모든 카드가 누군가의 데이터베이스에 들어갈 수 있음 가맹점 관점에서는 짧은 기간에 서로 관련 없는 카드 수가 평소보다 많이 늘고, 거래 금액도 커지는 형태로 나타남 단순 기준 예시는 최근 7일 동안 가맹점과 시간 단위로 묶어 다음을 계산함 고유 카드 수가 20개를 넘고 총액이 $5000을 넘는 시간대를 탐색함 정적 임계값에는 크기 보정 문제가 있음 Costco는 90초 안에 이 기준을 넘을 수 있음 중고 서점은 거의 넘지 않음 더 나은 방식은 각 가맹점을 자기 자신의 과거 기준선과 비교하는 것임 최근 60일 거래를 시간 단위로 묶음 각 가맹점의 과거 168개 시간 버킷을 기준으로 평균 고유 카드 수를 계산함 현재 고유 카드 수가 과거 평균의 3배를 넘는 구간을 찾음 168개 시간 버킷은 지난 7일의 시간 단위 구간임 일별·주별 계절성이 중요하기 때문임 같은 커피숍도 화요일 오후 2시와 토요일 오전 9시는 기준선이 다름 시작점으로는 평소의 3배를 사용할 수 있음 알림이 과도하게 쏟아지지 않을 만큼 느슨함 실제로 이상한 시간대를 잡을 만큼은 빡빡함 5. Off-hours: 개인의 평소 사용 시간대 밖 거래 대부분의 사람에게는 지출 습관이 있음 9시부터 5시까지 일하는 사람이 갑자기 새벽 3시에 주유를 시작하면, 카드가 다른 사람에게 쓰였거나 여행 중일 가능성이 있음 여행 중인지 여부는 다른 신호로 추가 확인 가능함 쿼리는 최근 90일 동안 카드 소유자별·시간대별 거래 수를 구한 뒤, 거래가 2번 이상 있었던 시간대만 평소 시간대로 인정함 이후 새 거래의 시간이 해당 카드 소유자의 earliest_hour와 latest_hour 범위 밖이면 탐지함 내부 쿼리의 “해당 시간대에 2건 이상” 조건이 중요함 3개월 전 우연히 있었던 심야 주유 1건이 평소 시간대에 포함되는 것을 막음 기준을 “한 번 있었던 일”이 아니라 실제 습관에 맞춤 단점은 이력 데이터가 필요하다는 점임 신규 계정에는 기준선이 없음 신규 계정은 전체 사용자 시간대 패턴을 쓰거나, 계정이 몇 달 쌓일 때까지 이 패턴을 건너뛸 수 있음 6. 윈도 함수로 신호 조합하기 윈도 함수 패턴은 별도 사기 유형이라기보다, 앞선 다섯 패턴을 조합 가능한 신호로 만드는 준비 작업임 거래별로 다음 파생 컬럼을 만들어둘 수 있음 직전 거래 이후 경과 시간: timestamp - LAG(timestamp) 가맹점 변경 여부: 직전 merchant_id와 현재 merchant_id 비교 최근 24시간 누적 금액: SUM(amount) OVER (...) 해당 날짜의 몇 번째 거래인지: ROW...