Phát hiện gian lận tài chính bằng SQL

Phong Do · 31/08/2026 · 14 phút đọc

Dịch từ bài gốc trên blog fixelsmith

Muốn tìm gian lận trong dữ liệu giao dịch, thứ bạn cần chủ yếu là SQL. Không phải machine learning, không phải graph database, cũng không phải công nghệ nào đang được tung hô năm nay. Chỉ cần SQL chạy trên đúng bảng dữ liệu, join đúng chỗ và biết mình đang tìm cấu trúc gì.

Tôi làm nhiều với các chương trình trợ cấp của nhà nước, nhưng những mẫu dưới đây dùng được cho bất kỳ hệ thống nào có bảng giao dịch: thẻ tín dụng, bồi thường bảo hiểm y tế, thương mại điện tử hay máy POS. Hễ có dòng tiền chạy qua và được ghi log, mấy câu truy vấn này sẽ lôi được điểm bất thường ra.

Sáu mẫu dưới đây được xếp theo đúng thứ tự tôi thường dựng khi nhận một tập dữ liệu mới.

1. Tần suất giao dịch (velocity)

Mẫu đơn giản nhất. Kẻ trộm được thẻ muốn vét sạch tiền trước khi chủ thẻ kịp phát hiện, nên chúng quẹt liên tục trong thời gian rất ngắn.

SELECT
    cardholder_id,
    date_trunc('hour', timestamp) AS hour_bucket,
    count(*)       AS tx_count,
    min(timestamp) AS first_tx,
    max(timestamp) AS last_tx
FROM transactions
WHERE timestamp >= current_date - INTERVAL '30 days'
GROUP BY 1, 2
HAVING count(*) > 10;

Có hai tham số bạn phải tự chỉnh: độ dài cửa sổ thời gian và ngưỡng số giao dịch. Tôi thường chạy song song ba phiên bản 1 phút, 5 phút và 1 giờ rồi so kết quả. Mỗi kiểu gian lận lộ ra ở một quy mô thời gian khác nhau: một nhóm chuyên thử thẻ (card testing) có thể dội hàng loạt request chỉ trong vài giây, còn một đường dây rút tiền trợ cấp thì có thể làm cả buổi chiều.

Sẽ luôn có những chủ thẻ vượt ngưỡng một cách hoàn toàn hợp pháp, ví dụ nhân viên đi nạp hàng cho tuyến máy bán hàng tự động, hoặc người nạp tiền hàng loạt vào thẻ trả trước. Đó chính là các trường hợp báo động giả. Sau lần lọc đầu tiên, bạn nên lập một whitelist và duy trì nó.

Nếu muốn tính velocity theo cửa sổ trượt, đây là khung tôi hay dùng:

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;

QUALIFY chạy tốt trên Snowflake, BigQuery, Databricks và Teradata. Với Postgres thì bạn phải bọc câu lệnh vào một CTE rồi lọc ở ngoài. Hơi lích kích nhưng kết quả không khác gì.

2. Di chuyển bất khả thi (impossible travel)

Một chiếc thẻ quẹt ở Chicago, bảy phút sau lại quẹt ở Los Angeles thì chắc chắn một trong hai lần là giả, nghĩa là thẻ đã bị sao chép. Đây là tín hiệu gian lận rõ ràng nhất mà bạn có thể tìm được, vì gần như không có lý do chính đáng nào để một chiếc thẻ xuất hiện ở hai nơi cách nhau xa như vậy chỉ trong bảy phút.

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
    cardholder_id,
    prev_ts   AS first_tx,
    timestamp AS second_tx,
    prev_loc  AS first_location,
    location  AS second_location,
    EXTRACT(EPOCH FROM (timestamp - prev_ts)) / 60 AS minutes_apart,
    haversine(prev_loc, location) AS miles_apart
FROM ordered_tx
WHERE prev_ts IS NOT NULL
  AND prev_loc <> location
  AND haversine(prev_loc, location)
      / nullif(EXTRACT(EPOCH FROM (timestamp - prev_ts)), 0) * 3600 > 600;

haversine là hàm tính khoảng cách theo đường tròn lớn trên mặt cầu. Đa số data warehouse hiện nay đã có sẵn. Nếu hệ thống của bạn không có, tự viết cũng chỉ mất chừng mười dòng.

Ngưỡng 600 dặm/giờ chỉ là ước lượng: máy bay thương mại bay khoảng 575 dặm/giờ, nên mức này tương đương “di chuyển nhanh hơn cả máy bay”. Bạn có thể hạ xuống 100 dặm/giờ để bắt luôn những chuyến đường bộ nhanh bất thường, nhưng ở ngưỡng đó bạn sẽ bắt nhầm cả khách đi máy bay thật, hay đứa trẻ ngồi trên xe bố mẹ chở từ trại hè về.

Vài biến thể cùng nhóm cũng rất đáng chạy:

Hai thành phố xa nhau nhưng cùng một bang, cách nhau dưới 5 phút: đường dây sao chép thẻ hoạt động trong vùng.
Nhiều mã ZIP khác nhau trong vòng một giờ: skimmer rải theo khu vực.
Vượt biên giới trong vòng 10 phút: đường dây xuyên quốc gia.

3. Bất thường về số tiền

Một số mức tiền xuất hiện dày đặc trong các vụ gian lận nhưng gần như không bao giờ thấy ở giao dịch bình thường.

SELECT cardholder_id, timestamp, amount, merchant_id
FROM transactions
WHERE (amount >= 99.50  AND amount < 100.00)
   OR (amount >= 499.50 AND amount < 500.00)
   OR amount IN (1.00, 5.00, 10.00)
ORDER BY cardholder_id, timestamp;

Lý do đằng sau:

Số tiền tròn và nhỏ ($1.00, $5.00, $10.00) hầu như luôn là hành vi thử thẻ. Kẻ gian lấy được dãy số thẻ từ database bị rò rỉ, quẹt thử một khoản bé xíu để xem thẻ còn sống không rồi mới đem bán. Người dùng thật gần như không bao giờ mua món hàng đúng $1.00. Ly cà phê giá $4.73, đổ xăng hết $52.81. Chính sự tròn trịa mới là dấu hiệu.

Số tiền sát ngưỡng lại kể một câu chuyện khác. $99.99 đáng chú ý vì ở nhiều nơi, $100 là mốc thu ngân buộc phải kiểm tra giấy tờ tùy thân. $499.99 đáng ngờ vì $500 thường là hạn mức rút tiền mặt trong ngày. Kẻ gian thuộc luật, nên chúng luôn đứng ngay dưới ngưỡng.

Riêng với giao dịch trợ cấp, mẫu số tiền tròn gần như vô dụng, vì tiền trợ cấp không bị đem ra thử thẻ theo kiểu đó. Ở đó dấu hiệu thường nằm ở người thụ hưởng bị trùng lặp, chuyện này để dành cho một bài khác.

4. Merchant đáng ngờ

Khi một thiết bị đọc trộm được gắn vào máy quẹt thẻ ở, chẳng hạn, một cây xăng, bạn sẽ không chỉ có một vụ gian lận mà có hàng tá. Mọi chiếc thẻ quẹt ở máy đó trong vài tuần sau sẽ nằm gọn trong database của kẻ gian. Vì vậy, triệu chứng nhìn từ phía merchant là: rất nhiều chủ thẻ chẳng liên quan gì đến nhau bỗng chi tiêu nhiều hơn thường lệ trong một khoảng thời gian ngắn.

SELECT
    merchant_id,
    date_trunc('hour', timestamp) AS hour_bucket,
    count(DISTINCT cardholder_id) AS unique_cards,
    count(*)                      AS total_tx,
    sum(amount)                   AS total_amount
FROM transactions
WHERE timestamp >= current_date - INTERVAL '7 days'
GROUP BY 1, 2
HAVING count(DISTINCT cardholder_id) > 20
   AND sum(amount) > 5000
ORDER BY total_amount DESC;

Điểm yếu của ngưỡng cố định (20 thẻ, $5.000) là nó không tính đến quy mô. Một siêu thị cỡ Costco đạt con số đó trong 90 giây, còn một tiệm sách cũ thì cả đời không chạm tới. Bản tốt hơn là so mỗi merchant với chính lịch sử của họ:

WITH merchant_hourly AS (
    SELECT
        merchant_id,
        date_trunc('hour', timestamp) AS hour_bucket,
        count(DISTINCT cardholder_id) AS unique_cards
    FROM transactions
    WHERE timestamp >= current_date - INTERVAL '60 days'
    GROUP BY 1, 2
),
with_baseline AS (
    SELECT
        *,
        avg(unique_cards) OVER (
            PARTITION BY merchant_id
            ORDER BY hour_bucket
            ROWS BETWEEN 168 PRECEDING AND 1 PRECEDING
        ) AS rolling_avg_cards
    FROM merchant_hourly
)
SELECT
    *,
    unique_cards / nullif(rolling_avg_cards, 0) AS spike_ratio
FROM with_baseline
WHERE unique_cards > rolling_avg_cards * 3
ORDER BY spike_ratio DESC;

Con số 168 chính là 7 ngày trước đó tính theo giờ. Tôi chọn một tuần vì chu kỳ theo ngày và theo tuần đều quan trọng: 2 giờ chiều thứ Ba ở một quán cà phê không cùng baseline với 9 giờ sáng thứ Bảy ở chính quán đó. Một tuần đủ để ôm trọn cả hai chu kỳ.

Mức “gấp ba lần bình thường” là điểm xuất phát tôi chọn. Đủ rộng để bạn không chết chìm trong cảnh báo, nhưng đủ chặt để gắn cờ những khung giờ thật sự lạ.

5. Giao dịch ngoài giờ

Đa số mọi người có nếp sinh hoạt và chi tiêu khá đều đặn. Một người làm giờ hành chính sẽ không tự nhiên đi đổ xăng lúc 3 giờ sáng. Nếu thẻ của họ phát sinh giao dịch vào giờ đó thì hoặc thẻ đang nằm trong tay người khác, hoặc họ đang đi xa, mà đi xa thì lại để lại những dấu vết khác bạn có thể đối chiếu.

WITH cardholder_hour_pattern AS (
    SELECT
        cardholder_id,
        EXTRACT(HOUR FROM timestamp) AS hour_of_day,
        count(*) AS tx_count
    FROM transactions
    WHERE timestamp >= current_date - INTERVAL '90 days'
    GROUP BY 1, 2
),
cardholder_normal AS (
    SELECT
        cardholder_id,
        min(hour_of_day) FILTER (WHERE tx_count >= 2) AS earliest_hour,
        max(hour_of_day) FILTER (WHERE tx_count >= 2) AS latest_hour
    FROM cardholder_hour_pattern
    GROUP BY 1
)
SELECT t.cardholder_id, t.timestamp, t.amount, t.merchant_id
FROM transactions t
JOIN cardholder_normal cn USING (cardholder_id)
WHERE EXTRACT(HOUR FROM t.timestamp) NOT BETWEEN cn.earliest_hour AND cn.latest_hour
ORDER BY t.timestamp DESC;

Bộ lọc “ít nhất hai lần trong khung giờ đó” ở truy vấn con là phần quan trọng nhất. Thiếu nó, chỉ một lần ghé cây xăng lúc nửa đêm từ ba tháng trước cũng đủ để khung giờ ấy được coi là “bình thường” với chủ thẻ, và từ đó bạn không bao giờ gắn cờ họ nữa. Yêu cầu tối thiểu hai giao dịch trong cùng khung giờ suốt 90 ngày giúp phân biệt “đã thành thói quen” với “tình cờ một lần”.

Hạn chế: cách này chỉ hoạt động khi đã có đủ lịch sử. Tài khoản mới thì chưa có baseline nào cả. Với nhóm đó, bạn phải quay về dùng mẫu giờ chung của toàn hệ thống, hoặc đơn giản là bỏ qua mẫu này cho tới khi họ hoạt động được vài tháng.

6. Window function để xâu chuỗi tín hiệu

Mẫu này không hẳn là một cách phát hiện gian lận riêng. Nó là nền để ghép năm mẫu phía trên lại với nhau.

SELECT
    cardholder_id,
    timestamp,
    amount,
    merchant_id,
    timestamp - LAG(timestamp) OVER w AS time_since_last,
    CASE WHEN merchant_id <> LAG(merchant_id) OVER w
         THEN 'changed' ELSE 'same' END AS merchant_change,
    sum(amount) OVER (
        PARTITION BY cardholder_id
        ORDER BY timestamp
        RANGE BETWEEN INTERVAL '24 hours' PRECEDING AND CURRENT ROW
    ) AS running_24h_total,
    ROW_NUMBER() OVER (
        PARTITION BY cardholder_id, date(timestamp)
        ORDER BY timestamp
    ) AS tx_of_day
FROM transactions
WINDOW w AS (PARTITION BY cardholder_id ORDER BY timestamp)
ORDER BY cardholder_id, timestamp;

Khi đã có sẵn mấy cột này, mọi quy tắc phát hiện gian lận đều rút gọn thành biểu thức lọc. Giả sử bạn đang săn các đường dây thử thẻ, đặc điểm là “nhiều khoản nhỏ, ở nhiều merchant khác nhau, dồn trong vài phút”. Quy tắc sẽ thành:

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

Ba điều kiện lọc, hết.

Chuyện này quan trọng ở chỗ: ngay khi đội phân tích có thể diễn đạt một giả thuyết gian lận mới bằng vài dòng lọc SQL thay vì mở ticket nhờ đội kỹ thuật, vòng lặp cải tiến của bạn rút từ vài tuần xuống còn vài giờ. Bắt được nhiều gian lận hơn, và bắt nhanh hơn.