取引詐欺を検出するために使用するSQLパターン

2026年5月16日原文(analytics.fixelsmith.com)

概要

SQL を活用した取引データの 不正検知 手法の解説
機械学習やグラフDBではなく、 シンプルなパターン検出 に重点
金融・行政・Eコマースなど幅広い分野で 応用可能
6つの代表的な不正パターン とそのSQL例を紹介
各パターンの 実務的注意点や運用のコツ も説明

取引データの不正検知：実践的SQLパターン集

不正検知 の現場では、SQLによるパターン検出が中心
主な対象： 政府系給付プログラム、クレジットカード、医療請求、Eコマース、POS
取引テーブル があれば、どの業種にも応用可能
6つの基本パターン を順に構築することで、精度と効率を両立

1. ベロシティ（短時間多発取引）

短時間に多数の取引 が発生するカードを検出
例：1時間に10件以上の取引
時間ウィンドウやしきい値は 用途に応じて調整
スライディングウィンドウ を使うことで、柔軟に検出可能
ホワイトリスト 運用で誤検知を削減

2. 不可能な移動（地理的矛盾）

物理的に不可能な距離移動 を検出
例：7分後に遠隔地での利用記録
haversine関数 で距離計算、速度しきい値（例：600mph）で判定
都市間・州内・国境越え など様々なパターンに拡張可能

3. 金額異常

特定の金額 や 閾値直前の金額 に着目
例：$1.00, $5.00, $99.99, $499.99
カードテスト や ATM制限回避 の兆候
給付系ではこのパターンは効果が薄い場合も

4. 怪しい加盟店

短期間に多数の異なるカード が利用された加盟店を検出
静的なしきい値ではなく、 自己ベースライン比較 で異常値を抽出
週単位の移動平均 で曜日・時間帯の季節性にも対応
スパイク比 で異常度を数値化

5. 時間外利用

カードごとの通常利用時間 を分析し、外れた時間の取引を検出
習慣的な時間帯 を2回以上の利用で定義
新規アカウントには適用不可、 履歴が必要

6. ウィンドウ関数による複合パターン

LAG, ROW_NUMBER, SUM などのウィンドウ関数で特徴量を事前計算
複数の不正パターンを SQLフィルタだけで組み合わせ可能
例：「60秒以内に5件以上、すべて異なる加盟店で小額決済」
分析ループの高速化、チーム全体での再利用性向上

運用上の注意点

NULL値の扱い ：システムごとのダミー値に注意
誤検知（False Positive） ：必ず人によるレビューと閾値調整
プライバシー ：PIIデータ利用時は必ず規定遵守
コスト管理 ：大規模ウィンドウ関数は事前に期間フィルタで絞り込み

今後書きたいテーマ

LAG, ROW_NUMBER以外のウィンドウ関数活用法
不正リング検知 （ソーシャルグラフ的アプローチ）
不正検知ダッシュボードの設計指針

Hackerたちの意見

実際のカードホルダーは、ほとんどの場合、ちょうど$1.00のものを買うことはないよね。コーヒーは$4.73だし、ガソリンは$52.81。丸い数字はサインだよ。これは、販売者が価格をどう設定するかによるんじゃない？盗まれたクレジットカードを使ってウェブサイトで何かを試すなら、自分で価格を決めるわけにはいかないし。それに、アメリカの「価格に税金が含まれていない」っていうのを過剰に考えてるかも。ほかの国では、丸い数字の価格はすごく一般的だよ。実際、投稿の他の部分もあまりうまくいかないように思えるし。（例えば、過去90日間に2回以上の取引があった時間外の人をフラグ立ててるの？それって50％の人に当てはまるんじゃない？）この記事が複雑な専門知識を単純化したSQLクエリに分解しようとしているのか、単なる推測や作り話なのかはよくわからない。「取引詐欺を見つけるために使う6つのSQLパターン」と「ここにあるものは、実際に私が関わったことや見たことから来ていない」というのは矛盾してるよね。

└

それより悪いこともあるよ。私の経験では、コーヒーはたいてい丸い数字だし、車を満タンにする時に丸い数字を目指す人も知ってるし、事前に設定された値、たとえば10、20、50ユーロなどを要求するガソリンスタンドもあるよ。

└

ここそこに「寄付のために丸める」オプションがあるお店を見かけるよ。ちょっとお人好しなところがあって、いつもそのオプションを使っちゃうんだ。だから、私の食料品はいつも丸い数字になるよ。

└

「通常の時間帯外の取引」って、かなり基本的なことに思える。俺は普通、午前2時にガソリンやコーヒー、スナックを買わないからね。でも、めったにないけどそうする時は、個人的な緊急事態に対処してるから、銀行に電話したくないんだ。確かに、そういう時間帯は機会を狙った泥棒がいるかもしれないけど、誤検知のコストも無視できないよね。

└

これって、a) テスト取引にディザーを追加することで簡単に回避できるし、b) 適切な統計分析で簡単に改善できるし、c) 近い100%の精度を期待しないようなヒューリスティックパターン認識がAIの得意分野じゃないの？

欠点：これが機能するのは履歴がある場合のみ。新しいアカウントには基準がない。これは過小評価されているCX要因だね。新しい顧客としてカードが拒否されたら、そのソフトウェアには感心するけど、過去に認証した履歴があるのに取引を拒否されたら、その単純なパラノイアアルゴリズムにはイライラするよ。

└

銀行のインセンティブは詐欺を減らすことだよね。詐欺的な取引は最終的に銀行にコストがかかる（逆転処理や補償をしなきゃいけなくなるから）。拒否された取引は、ただ怒った顧客を生むだけで、彼らは文句を言った後すぐに忘れちゃうから、顧客が外部コストの負担を背負うことになる。だから、銀行はより慎重に行動して、誤検知があった場合は取引を拒否するインセンティブがあるんだ。

シカゴでカードをスワイプして、7分後にロサンゼルスでスワイプしたら、そのどちらかは偽物だよね。オンラインショッピングではどうなるの？ソファに座ってAmazonで買い物をする時、住所はどこに登録されるの？ちょっとしたケースも想像できるよ。カップルがオンラインアカウントを共有していて、一方が旅行中に保存されたカード情報で購入する場合とか。

└

システムは「カードあり」と「カードなし」を区別していると思うよ。

└

カードをスワイプ（または挿入、タップ）するのは「カードあり」の取引。カード番号を入力するオンラインショッピングは「カードなし」の取引だね。小売業者や銀行はその違いを見分けられるよ。

└

取引のメタデータに基づいて判断できるんだ。ソース：私はクレジットカード会社で働いてた。

これらのSQL計算の主な問題は、確率的な問題に対する決定論的なショートカットだってことだよね。詐欺って、普通は「ルールXが一致したから真」ってわけじゃない。むしろ「これが詐欺である確率はどれくらい？」って感じ。SQLパターンは役に立つけど、鈍い道具だと思う。銀行が決定論的なヒューリスティックを使ってるとは思えないし、もっとデータサイエンス的なアプローチを取ってるんじゃないかな。

Hacker Newsで議論の続きを見る

ハクソク