6 Mẫu SQL hiệu quả để phát hiện gian lận giao dịch

Trong công việc bảo toàn tính toàn vẹn của dữ liệu (program integrity), phát hiện gian lận trong các giao dịch tài chính chủ yếu dựa vào SQL. Không phải là Machine Learning, không phải là cơ sở dữ liệu đồ thị, cũng không phải là các công nghệ được thổi phồng quá mức trên thị trường. Chỉ cần SQL, chạy trên đúng bảng dữ liệu, với các phép nối (join) phù hợp và tìm kiếm đúng mẫu hình.

Dù làm việc với các chương trình phúc lợi do chính phủ tài trợ hay thẻ tín dụng, yêu cầu bảo hiểm y tế, thương mại điện tử hay điểm bán hàng (POS), nếu tiền bạc di chuyển và được ghi lại, các truy vấn này đều có thể tìm ra những điểm bất thường trong nhật ký giao dịch.

Dưới đây là 6 mẫu SQL cơ bản mà tôi thường sử dụng để phát hiện gian lận, theo thứ tự ưu tiên khi xây dựng trên một bộ dữ liệu mới.

1. Tốc độ giao dịch (Velocity)

Đây là mẫu đơn giản nhất. Kẻ gian sử dụng thẻ bị đánh cắp thường muốn rút sạch tiền trước khi chủ thẻ phát hiện, do đó họ sẽ thực hiện các giao dịch liên tục với tốc độ rất cao.

Truy vấn cơ bản sẽ nhóm các giao dịch theo chủ thẻ và các khoảng thời gian (ví dụ: theo giờ) để đếm số lượng giao dịch. Nếu số lượng vượt quá ngưỡng nhất định (ví dụ: hơn 10 giao dịch trong một giờ), đó là tín hiệu cảnh báo.

Tuy nhiên, để chính xác hơn, chúng ta nên sử dụng kỹ thuật "cửa sổ trượt" (sliding-window). Thay vì chỉ đếm trong một khung giờ cố định, chúng ta đếm số giao dịch xảy ra trong 5 phút gần nhất tính đến thời điểm hiện tại.

SELECT
cardholder_id,
timestamp,
count(*) OVER (
PARTITION BY cardholder_id
ORDER BY timestamp
RANGE BETWEEN INTERVAL '5 minutes' PRECEDING AND CURRENT ROW
) AS tx_in_last_5min
FROM transactions
QUALIFY tx_in_last_5min >= 5
ORDER BY cardholder_id, timestamp;

Bạn có thể điều chỉnh hai thông số: kích thước cửa sổ thời gian và ngưỡng số lượng. Các nhóm kiểm tra thẻ (card-testing rings) có thể tấn công máy chủ trong vài giây, trong khi các nhóm buôn lận lợi ích có thể kéo dài cả buổi chiều.

Lưu ý rằng sẽ có những trường hợp dương tính giả (false positives), như các nhân viên vận hành nạp thẻ trả trước hàng loạt. Hãy duy trì một danh sách trắng (whitelist) sau lần quét đầu tiên.

2. Di chuyển bất khả thi (Impossible Travel)

Nếu một thẻ tín dụng quẹt tại Chicago và 7 phút sau lại quẹt tại Los Angeles, chắc chắn một trong hai giao dịch là giả mạo. Đây là tín hiệu gian lận rõ ràng nhất vì không có lý do chính đáng nào để một chiếc thẻ xuất hiện ở hai địa điểm cách nhau hàng ngàn dặm trong thời gian ngắn như vậy.

Để phát hiện điều này, chúng ta sử dụng hàm cửa sổ LAG để lấy vị trí và thời gian của giao dịch trước đó, sau đó tính toán khoảng cách và tốc độ di chuyển.

WITH ordered_tx AS (
SELECT
cardholder_id,
timestamp,
location,
LAG(timestamp) OVER (PARTITION BY cardholder_id ORDER BY timestamp) AS prev_ts,
LAG(location)  OVER (PARTITION BY cardholder_id ORDER BY timestamp) AS prev_loc
FROM transactions
)
SELECT ...
FROM ordered_tx
WHERE haversine(prev_loc, location) / time_diff * 3600 > 600;

Hàm haversine giúp tính khoảng cách đại tròn giữa hai điểm tọa độ. Ngưỡng 600 dặm/giờ (khoảng 965 km/giờ) tương đương tốc độ của một máy bay phản lực thương mại. Nếu tốc độ tính toán vượt quá con số này, giao dịch đó đáng ngờ. Bạn có thể siết chặt ngưỡng này xuống 100 dặm/giờ để bắt cả các trường hợp di chuyển trên đất liền đáng ngờ, nhưng cần cẩn thận với những người đi du lịch thực sự.

3. Số tiền bất thường (Amount Anomalies)

Có một số mức tiền xuất hiện tỷ lệ cao trong các giao dịch gian lận nhưng rất hiếm khi gặp trong sử dụng bình thường. Điển hình là các khoản tiền nhỏ như $0.01, $0.99, hoặc các số tiền tròn như $99.99, $499.99.

Kẻ gian thường dùng các số tiền này để kiểm tra xem thẻ có hoạt động hay không (card testing) hoặc để cố tình lẩn tránh các quy tắc kiểm soát.

Truy vấn sẽ lọc các giao dịch có số tiền rơi vào các khoảng cụ thể này trong vài ngày gần đây.

4. Đột biến tại người bán (Merchant Spikes)

Nếu một người bán (merchant) đột nhiên nhận được lượng giao dịch lớn bất thường từ nhiều thẻ khác nhau trong thời gian ngắn, có thể họ đang là tâm điểm của một vụ bẻ khóa thẻ hoặc đang bị lợi dụng để rửa tiền.

Thay vì đặt một ngưỡng tĩnh (ví dụ: 20 thẻ duy nhất), cách tốt hơn là so sánh hoạt động hiện tại của người bán với lịch sử của chính họ. Chúng ta sử dụng trung bình trượt (rolling average) trong 7 ngày trước đó để làm đường cơ sở (baseline).

Nếu số lượng thẻ duy nhất trong giờ hiện tại gấp 3 lần trung bình của cùng giờ đó trong tuần trước, đó là một tín hiệu spike.

5. Giao dịch ngoài giờ (Off-hours)

Hầu hết mọi người đều có thói quen chi tiêu cố định. Một nhân viên văn phòng không thường xuyên xăng vào lúc 3 giờ sáng. Nếu thẻ của họ được sử dụng vào giờ đó, rất có thể thẻ đang bị người khác dùng hoặc chủ thẻ đang đi du lịch (trường hợp này sẽ có các tín hiệu khác đi kèm).

Chúng ta có thể xây dựng một "khung giờ bình thường" cho từng chủ thẻ dựa trên lịch sử 90 ngày qua. Bất kỳ giao dịch nào diễn ra ngoài khung giờ này sẽ bị đánh dấu.

Lưu ý: Quy tắc này yêu cầu dữ liệu lịch sử. Với các tài khoản mới, bạn cần sử dụng mẫu hình toàn cầu hoặc bỏ qua quy tắc này cho đến khi họ có đủ dữ liệu.

6. Hàm cửa sổ cho chuỗi tín hiệu (Window Functions)

Đây không hẳn là một mẫu phát hiện, mà là một thiết lập giúp kết hợp các mẫu trên lại với nhau. Bằng cách sử dụng các hàm cửa sổ, chúng ta có thể tính toán nhiều chỉ số cùng một lúc và kết hợp chúng thành các quy tắc lọc đơn giản.

Ví dụ, để bắt một nhóm kiểm tra thẻ, đặc điểm nhận dạng là "nhiều khoản tiền nhỏ, tại nhiều người bán khác nhau, trong vài phút". Thay vì viết các truy vấn phức tạp, bạn chỉ cần lọc trên bảng đã được tính toán sẵn các chỉ số:

SELECT *
FROM tx_with_windows
WHERE tx_of_day >= 5
AND time_since_last < INTERVAL '60 seconds'
AND merchant_change = 'changed';

Khi các nhà phân tích có thể diễn đạt các giả thuyết gian lận mới dưới dạng các bộ lọc SQL thay vì phải tạo yêu cầu phát triển phần mềm (engineering tickets), vòng lặp cải tiến sẽ rút ngắn từ vài tuần xuống còn vài giờ.

Tổng kết

Không có mẫu nào trong số này là đủ để khẳng định gian lận 100%. Tốc độ giao dịch cao có thể do nhân viên nạp thẻ, di chuyển nhanh có thể do đi máy bay. Tuy nhiên, việc chạy đồng thời tất cả các mẫu này và tính điểm cho từng giao dịch sẽ mang lại hiệu quả cao. Một giao dịch vi phạm 3 hoặc 4 quy tắc thường là gian lận chắc chắn.

Nếu bạn mới bắt đầu với phát hiện gian lận, hãy bắt đầu với mẫu Tốc độ (Velocity). Nó rẻ, dễ chạy và mang lại hiệu quả tức thì. Nếu bạn đã có các quy tắc cơ bản, hãy đầu tư vào các hàm cửa sổ (Window Functions) để giúp đội ngũ phân tích linh hoạt hơn trong việc tạo ra các quy tắc mới.