5月15日、Fixel Smithが「Six SQL patterns I use to catch transaction fraud」と題した記事を公開した。

「不正検出は大部分がSQLだ。機械学習でもグラフデータベースでもない」。政府系給付プログラムのプログラム・インテグリティチームでデータ業務を担当する同氏の言葉は、AI・機械学習が注目される昨今の不正検出業界に一石を投じている。実際の現場では、複雑なアルゴリズムよりも「適切なテーブルに対して、適切な結合で、適切なパターンを探すSQL」こそが不正を見つける最も確実な手段だという。

近年、不正検出分野では機械学習やAIソリューションが脚光を浴びている。しかし実際の金融機関や決済事業者では、明確なルールベースのSQL分析が依然として主力だ。理由は単純明快で、不正の多くは人間の行動パターンから大きく逸脱するため、統計的な閾値やパターンマッチングで十分検出できるからである。また、SQLベースの手法は結果の説明可能性が高く、規制要件の厳しい金融業界では重要な利点となる。

同記事で紹介される6つのパターンは、クレジットカード、医療請求、eコマース、POSなどお金が動いてログに記録されるあらゆるシステムに応用できる汎用性の高い手法だ。以下、各パターンを詳しく見ていこう。

パターン1: ベロシティ（高頻度取引）

最もシンプルかつ効果的なパターンだ。盗まれたカードの犯人は、持ち主が気づく前に素早く使い切ろうとする行動を取る。

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;

調整すべきはウィンドウサイズとカウント閾値の2つだ。同氏は1分、5分、1時間版を並行して実行し比較している。カードテスト集団はサーバーを数秒で攻撃し、給付金転売集団は午後をかけて活動するため、異なるスケールで異なる不正が現れるからだ。

スライディングウィンドウ版では以下の形式を使用する：

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;

パターン2: 不可能移動

シカゴでカードが使われて7分後にロサンゼルスで使われたら、どちらかは偽物だ。これは最も議論の余地がない不正シグナルだという。

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;

時速600マイルの閾値は商用ジェットの巡航速度575マイルを上回る「物理的に不可能な速度」として設定されている。このパターンはPCI DSSなどのセキュリティ標準でも推奨される基本的な不正検出手法だ。

パターン3: 金額の異常

不正利用で異常に多く現れる特定の金額がある。

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;

小額のきっかり金額（1.00ドル、5.00ドル、10.00ドル）は、ほぼ間違いなくカードテストだ。ダンプから入手したカード番号が有効か確認している。通常のカード利用者がきっかり1.00ドルの買い物をすることはまずない。コーヒーは4.73ドル、ガソリンは52.81ドルといった具合だ。

閾値ぎりぎりの金額は別の意味を持つ。100ドルでID確認が必要な店が多いため99.99ドル、ATMの日次上限が500ドルのため499.99ドルが使われる。

パターン4: 疑わしい店舗

ガソリンスタンドのポンプにスキマーが仕掛けられると、1件の不正ではなく数十件の被害が生まれる。

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;

168は7日間の時間別バケット数を表す。日次・週次の季節性を考慮するためだ。コーヒーショップの火曜午後2時と土曜午前9時では正常な利用パターンが異なる。

パターン5: 異常時間帯

9時5時勤務の人が急に午前3時にガソリンを買うことはない。もしカードが使われているなら、他人が使っているか旅行中かだ。

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;

「その時間に2回以上」のフィルターが重要な働きをしている。3カ月前の1回の深夜購入を「正常」とせず、実際の習慣を捉えるためだ。

パターン6: ウィンドウ関数による信号の連鎖

これは単独のパターンではなく、他の5つのパターンを組み合わせ可能にする仕組みだ。

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;

これらの列を物理化すると、不正ルールは単純なフィルター表現に簡約される。カードテスト集団を狙う場合：

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

たった3つのフィルターだ。アナリストが新しい不正仮説をエンジニアリングチケットではなくSQLフィルターで表現できるようになると、反復サイクルが数週間から数時間に短縮される。

実運用での組み合わせとスケーリング

これらのパターンは単独では不十分だ。ベロシティには偽陽性があり（自販機オペレーター）、地理的不可能性は同一都市圏内では機能しない。全て実行して各取引をスコアリングすることが重要だ。3つや4つの信号で引っかかった取引はほぼ間違いなく不正、1つだけなら休暇中に変な使い方をしているおばあちゃんかもしれない。

同氏は不正検出初心者にはパターン1から始めることを推奨している。それだけで有用な量の不正を検出でき、正常な活動はほとんど引っかからず、実行コストも安いからだ。

大規模システムでこれらのパターンを実装する場合、Apache KafkaによるリアルタイムストリーミングやApache Flinkでの複雑イベント処理と組み合わせることで、リアルタイムでの不正検出が可能になる。また、dbtを使ったデータ変換パイプラインでこれらのSQLパターンを定期実行し、不正スコアを継続的に更新する運用も一般的だ。

詳細はSix SQL patterns I use to catch transaction fraudを参照していただきたい。