ハクソク

世界を動かす技術を、日本語で。

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

10日前原文(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パターンは役に立つけど、鈍い道具だと思う。銀行が決定論的なヒューリスティックを使ってるとは思えないし、もっとデータサイエンス的なアプローチを取ってるんじゃないかな。

└

この業界でそれなりの経験があるけど、もう数年前のことだね。2015年と2016年にはSquareの決済リスクチームで働いて、2021年から2024年まではPlaidのACH詐欺API製品「Signal」に関わってた。Plaidではクライアントミーティングに参加して、どれだけの企業がすでにリスクを扱っているかを学んだし、新しい役割を探しているときには他の企業のリスクチームにも面接を受けた。基本的に、これをやっているのは銀行や正式な金融機関だけじゃなくて、やり方は企業の規模によって異なるんだ。規模はリスクチームのリソースの数だけでなく、詐欺リングがターゲットにしているかどうかにも関連していることが多い。通常見てきたのは、企業が日々実行されるバッチSQLやシンプルなロジックプロセスから始めて、手動レビューのためにアカウントをフラグ付けしたり、決済や取引（プラットフォームがやること）を自動的にブロックすることだね。そのレビューが終わるまで。時間が経つにつれて、企業はMLベースのアプローチに移行して、まだほとんど手動レビューのためにフラグを立てるんだ。MLの目標は、ドルのリコールや詐欺イベントのリコールをあまり傷つけずにフラグ付けの精度を向上させることだよ。決済システムによっては、企業は両方に敏感かもしれない（例えば、ACHでは、たとえ非常に少額の支払いの返金でも、返金が多すぎるとパートナーバンクから厳しくされるし、ACHが使えなくなるリスクがある）。

MLのポイントって、データからこれらのルールを学ぶことじゃないの？俺が考える正しいアプローチは、MLモデルを使って詐欺に関連するパターンを検出し、それが意味を持つかどうかを評価することだと思う。そうすれば、新しい仮説を発見できるかもしれないよ。

└

決定論的に説明できず、反復できないものは、金融取引を拒否するビジネスにはリスクが大きすぎる。人間のアナリストは、特定の取引が拒否された理由をコンプライアンスに5分のメールで説明できる必要があるし、何よりもその悪い判断を避けるために何ができたかを説明しなきゃいけない。MLで一つの問題を解決すると、しばしばまだ明らかでない二つの新しい問題が生まれることが多い。SQLは、時間が経つにつれての回帰や予期しない副作用に関しては、驚きが少ない傾向があるね。

取引データの詐欺検出は主にSQLだよ。機械学習でも、グラフデータベースでも、今年のガートナーが持ち上げてるようなものでもない。正しいテーブルに対して、正しい結合を使って、正しい形を探すSQLだ。全てがプログラムの整合性に関するわけでもないし、そんな包括的な発言を正当化できるのはそれだけだよ。問題領域に対処できる限り、悪い方が良い。フィンテックのクライアントは、今まさに起こっている取引が詐欺かどうかを知りたがってる。彼らは数ミリ秒でそれを知りたいんだ、高次元データに対してね。これは、リレーショナルデータベースがリアルタイムの制約に対応できないスケールで行われる作業で、代わりに歴史データの読み込みなどに使われることが多い。そうやってインメモリデータベースやストリーム処理エンジン、そしてもちろん機械学習が生まれるんだ。とはいえ、著者の指摘には正当な部分もあって、次の著作が楽しみだな。特にノイズの多いアラートの扱いは、パフォーマンスエンジニアリングを超えた一般的な問題だからね。

└

僕の経験から言うと、君が説明してるのは詐欺検出よりも詐欺防止って呼ばれることが多いと思う。両者は成熟したシステムでは共存していて、補完関係にあるんだ。防止の方は、常にレイテンシ要件や利用可能なデータ、ユーザー行動の不完全な把握に制約される。大半のケースに対処するために、MLとルールを使って迅速に判断するけど、その制約があるからすべての詐欺を正確に防ぐのは不可能なんだ。検出はその後の結果に対処するもので、アナリストのチームが受け入れられた取引を分析して詐欺の兆候を探すんだ。特に、チャージバックや顧客の苦情のような外部信号が得られない詐欺タイプには重要だよ。プラットフォームの整合性がその一例だね。でも、フィンテック企業もマネーロンダリング防止システムを構築するのを見てきた - 詐欺を探しに行く必要があるんだ。この記事が説明しているのはこのプロセスだよ。検出された取引は、次の防止モデルのトレーニングと評価のためのラベルになるから、補完関係にあると言えるんだ。

10分以内の国境越え。国際的なリング。あるいは、国境近くに住んでる普通の人々。あと、これにカード不在の取引を含めてないと思うけど、すべての商人が正しく位置情報を設定してるって誤解してるよね。それに、すべての販売が実店舗で行われるわけじゃなくて、旅行販売員とかからのものもあるし、すべての取引がオンラインで行われるわけでもない。

└

数週間前にUSからCAに越えたとき、たぶん10分くらいだったかな…

tirreno（1）っていうオープンソースのセキュリティフレームワークを開発してるんだけど、紹介されてるアプローチには疑問があるな。例えば、不可能な移動っていうのは正当な手法で広く使われてるけど、IPアドレスに基づくオンラインユーザーの行動に関係してるんだよね。それに、tirrenoにはApple RelayやVPN/TorからのIPの場合に別のルールがあって、そういうのは別のフラグになるんだ。いくつかの例はLLM生成だと思うけど、文脈が混ざってるし、実際にカードスワイプのためにGPS位置を大量に収集してる人はいないよね。 1. https://github.com/tirrenotechnologies/tirreno

Hacker News、ちょっと話したいことがある！「Fixel Smith」ってAI生成の人物で、詐欺分析とはほとんど関係ない記事があるんだ。『これ』も音楽アーティスト（1）、小説家（2）、詐欺分析者（3）、インフルエンサー（4）、その他いろいろ想像できることをやってる。215ポイント以上、60コメントもあって、ほとんどの人がこれがかなりのフェイク投稿だって気づいてないし、AIの人物だって誰も気にしてないの？ 1. https://www.amazon.it/Forged-Soundtrack-Explicit-Fixel-Smith... 2. https://fixelsmith.com 3. https://analytics.fixelsmith.com/ 4. https://www.instagram.com/fixeltales/

└

大半の人が読んでる記事の著者を調べる習慣があるって聞いたら、むしろ驚くよ。正直なところ、僕は通常著者名すら見ないし、ウェブサイトの他の部分なんて全然見ないんだよね。

└

時間をかけてこれらすべてを持っている人を想像することはできるけど、人には多くの興味があるからね。ざっと見るとAIっぽい印象は確かにある。少なくともInstagramアカウントはたくさん使ってるし、トップドメインもスタイルから見てAIと一緒に作られた可能性が高い。ちょっと魅力的だけど、完全に生成されたペルソナではなく、AIを使ってこれをやっている人かもしれないね。教えてくれてありがとう。

これ読むのめっちゃ面白い！詐欺防止に本格的に関わったことはないけど、ポーカーサイトやカジノを運営してたときに、共謀や詐欺を見つけるために似たようなパターンチェックを自動化することになったんだ。当時はウィンドウ関数が使えなかったから、クエリが長かったんだよね。対処法の一つは、ポーカーテーブルを共有したプレイヤーのペアにUUIDを割り当てて、同じハンドで一緒にプレイしているときのベッティングの偏差を毎晩分析することだった。これ、履歴テーブルで100行以上のSQLクエリを1、2回で実行できたんだ。ウィンドウ関数やラテラルジョインを使えば、サイズを1/4に減らせたかもしれないけど、最初にセットを小さなテーブルに絞るよりもコストがかかることは確実だった。