PolarDB：構建信用卡反欺詐模型

核心優勢

• 原生SQL：使用標準SQL完成特徵提取、建立模型和預測，無需學習新的程式設計語言。

• 資料閉環：資料無需在多個系統間遷移，所有操作均在PolarDB叢集內部完成，簡化了架構並降低延遲。

特徵維度設計

為了準確識別異常交易行為，需要從未經處理資料中構造更具表達能力的特徵。這通常涉及三個層面：

• 基礎特徵：直接從未經處理資料中擷取或輕微處理得到。

  • 交易基本資料：交易金額（txn_amount）、時間（txn_timestamp）、渠道（txn_channel）、商戶（merchant_id）、城市（city）、國家（country）、幣種（currency）、交易類型（txn_type）、是否跨境（is_international）等。

  • 客戶基本資料：客戶ID（customer_id）、卡號（card_id）、年齡（customer_age）、客戶分層（customer_segment）、開戶天數（account_open_days）等。

• 行為統計特徵：通過在不同時間視窗內對基礎特徵進行彙總計算，以捕捉使用者的常規行為模式。常見時間視窗為1小時、24小時、7天、30天等。

  • 頻率與金額類：txn_cnt_1h（過去1小時交易筆數）、txn_amount_sum_24h（過去24小時交易總額）、txn_amount_mean_7d（過去7天平均交易金額）等。

  • 多樣性類：distinct_merchant_cnt_7d（過去7天消費的不同商戶數）、distinct_city_cnt_7d（過去7天消費的不同城市數）、is_new_country（本次交易國家是否首次出現）等。

  • 時間模式類：night_txn_ratio_7d（過去7天夜間交易佔比）、inter_txn_time_mean_7d（過去7天平均交易間隔）等。

• 風險與偏離度特徵：將當前交易與使用者的歷史行為模式進行對比，量化其異常程度。

  • 金額偏離：amount_vs_mean_30d（當前金額與30日均值的比率）、amount_zscore_30d（當前金額的Z-score分數）。

  • 頻率偏離：txn_cnt_24h_vs_7d（近24小時交易數與近7日日均交易數的比率）。

  • 地區偏離：geo_distance_from_last（與上筆交易的地理距離）、impossible_travel_flag（是否發生“不可能旅行”，即短時間內地理位置跨度過大）。

建立資料表

執行以下SQL語句，建立用於儲存原始交易流水和客戶資訊的表。

-- 建立交易流水表
CREATE TABLE transaction_log (
    txn_id BIGINT PRIMARY KEY,
    card_id BIGINT,
    customer_id BIGINT,
    txn_timestamp TIMESTAMP(3), -- 使用毫秒精度，以便更好地類比短時連續交易
    txn_amount DECIMAL(18, 2),
    merchant_id BIGINT,
    city VARCHAR(255),
    country VARCHAR(255),
    txn_channel VARCHAR(50), -- 交易渠道 (例如: POS, Online, ATM)
    txn_type VARCHAR(50),    -- 交易類型 (例如: Purchase, Withdrawal)
    currency VARCHAR(10),    -- 幣種 (例如: CNY, USD)
    is_international INT,    -- 是否跨境：1表示跨境，0表示不跨境
    label INT                -- 標籤：1表示欺詐，0表示正常
);

-- 建立客戶資訊表
CREATE TABLE customer_profile (
    customer_id BIGINT PRIMARY KEY,
    card_id BIGINT,
    customer_age INT,
    account_open_days INT,
    customer_segment VARCHAR(50) -- 客戶分層 (例如: Standard, Gold, Platinum)
);

產生類比資料

以下SQL指令碼用於產生類比資料，向transaction_log和customer_profile表中插入包含正常交易和少量欺詐交易的記錄。

-- 插入客戶資訊
INSERT INTO customer_profile (customer_id, card_id, customer_age, account_open_days, customer_segment) VALUES
(1001, 6222020000000001, 35, 730, 'Gold'),
(1002, 6222020000000002, 42, 1200, 'Platinum'),
(1003, 6222020000000003, 28, 365, 'Standard');

-- 插入交易流水 (包含多種正常和欺詐樣本)
-- 欺詐交易樣本通常與正常交易樣本比例嚴重不平衡（如1:1000或更低），此處為示範目的提高了欺詐樣本比例。
INSERT INTO transaction_log (txn_id, card_id, customer_id, txn_timestamp, txn_amount, merchant_id, city, country, txn_channel, txn_type, currency, is_international, label) VALUES
-- 情境一：客戶1001的常規消費 (正常)
(1, 6222020000000001, 1001, '2023-10-01 10:00:00.000', 150.00, 201, 'Shanghai', 'China', 'POS', 'Purchase', 'CNY', 0, 0),
(2, 6222020000000001, 1001, '2023-10-01 19:30:00.000', 88.50, 202, 'Shanghai', 'China', 'Online', 'Purchase', 'CNY', 0, 0),
(3, 6222020000000001, 1001, '2023-10-02 12:15:00.000', 230.00, 201, 'Shanghai', 'China', 'POS', 'Purchase', 'CNY', 0, 0),
(4, 6222020000000001, 1001, '2023-10-03 09:00:00.000', 55.00, 203, 'Shanghai', 'China', 'Online', 'Purchase', 'CNY', 0, 0),

-- 情境二：客戶1002的差旅消費 (正常)
(5, 6222020000000002, 1002, '2023-10-03 19:00:00.000', 1200.00, 301, 'Beijing', 'China', 'POS', 'Purchase', 'CNY', 0, 0),
(6, 6222020000000002, 1002, '2023-10-04 20:00:00.000', 850.00, 302, 'Beijing', 'China', 'POS', 'Purchase', 'CNY', 0, 0),

-- 情境三：客戶1003的日常消費 (正常)
(7, 6222020000000003, 1003, '2023-10-05 18:00:00.000', 99.00, 401, 'Hangzhou', 'China', 'Online', 'Purchase', 'CNY', 0, 0),

-- 情境四：欺詐模式 - "不可能的旅行" + 金額異常 (欺詐)
-- 客戶1001在上海有一筆交易後，5分鐘內突然在紐約出現大額交易
(8, 6222020000000001, 1001, '2023-10-03 23:50:00.000', 9800.00, 501, 'New York', 'USA', 'Online', 'Purchase', 'USD', 1, 1),

-- 情境五：欺詐模式 - 短時高頻盜刷 (欺詐)
-- 客戶1003的卡在短時間內被連續小額盜刷
(9, 6222020000000003, 1003, '2023-10-06 02:10:15.100', 499.00, 601, 'Manila', 'Philippines', 'Online', 'Purchase', 'PHP', 1, 1),
(10, 6222020000000003, 1003, '2023-10-06 02:10:45.500', 499.00, 601, 'Manila', 'Philippines', 'Online', 'Purchase', 'PHP', 1, 1),
(11, 6222020000000003, 1003, '2023-10-06 02:11:30.800', 499.00, 601, 'Manila', 'Philippines', 'Online', 'Purchase', 'PHP', 1, 1);

計算時間視窗特徵

為捕捉使用者的行為模式，需要計算基於不同時間視窗的統計特徵，例如“過去7天內的交易次數”。您可以通過SQL的視窗函數高效完成，以下樣本為每筆交易計算其關聯卡號在過去24小時內的交易總金額和過去7天內的交易總次數。

-- 計算基礎的時間視窗特徵
WITH transaction_features AS (
    SELECT
        *,
        -- 計算過去24小時內的交易總金額
        SUM(txn_amount) OVER (
            PARTITION BY card_id 
            ORDER BY txn_timestamp 
            RANGE BETWEEN INTERVAL '24' HOUR PRECEDING AND CURRENT ROW
        ) AS txn_amount_sum_24h,
        -- 計算過去7天內的交易次數
        COUNT(*) OVER (
            PARTITION BY card_id 
            ORDER BY txn_timestamp 
            RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
        ) AS txn_cnt_7d
    FROM
        transaction_log
)
SELECT * FROM transaction_features;


-- 計算金額偏離度
SELECT
    txn_amount / NULLIF(
        AVG(txn_amount) OVER (
            PARTITION BY card_id 
            ORDER BY txn_timestamp 
            RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND INTERVAL '1' MICROSECOND PRECEDING
        ), 0
    ) AS amount_vs_mean_30d
FROM
    transaction_log;

構建最終特徵表

將所有基礎特徵和計算出的統計特徵合并，建立一張最終用於模型的特徵表train_data。

-- 修改後的SQL語句
CREATE TABLE train_data AS
SELECT
    -- 基礎特徵
    t.txn_amount,
    t.txn_channel,
    t.merchant_id,
    t.city,
    t.country,
    t.txn_type,
    t.currency,
    t.is_international,
    EXTRACT(HOUR FROM t.txn_timestamp) AS txn_hour,
    DAYOFWEEK(t.txn_timestamp) AS txn_weekday,
    p.customer_age,
    p.account_open_days,
    -- 使用視窗Function Compute的統計特徵
    SUM(t.txn_amount) OVER (
        PARTITION BY t.card_id 
        ORDER BY t.txn_timestamp 
        RANGE BETWEEN INTERVAL '24' HOUR PRECEDING AND CURRENT ROW
    ) AS txn_amount_sum_24h,
    COUNT(*) OVER (
        PARTITION BY t.card_id 
        ORDER BY t.txn_timestamp 
        RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
    ) AS txn_cnt_7d,
    -- 標籤列
    t.label
FROM
    transaction_log t
JOIN
    customer_profile p ON t.customer_id = p.customer_id;

建立模型

使用CREATE MODEL語句建立一個二分類模型以識別欺詐交易。

• 模型選擇：選用LightGBM演算法，它在處理表格式資料時效率高且速度快。

• 參數配置：

  • y_cols：指定標籤列為label。

  • is_unbalance：設定為True。由於欺詐交易樣本遠少於正常交易，這是一個典型的資料不平衡情境。啟用此參數有助於模型學習到少數類的特徵。

  • n_estimators：弱學習器（樹）的數量。建議初始值設定在5-8之間，以平衡模型效果和建立時間。

建立樣本

1. 執行以下SQL語句來建立模型。

   /*polar4ai*/CREATE MODEL credit_card_model WITH (
   model_class='lightgbm',
   x_cols='txn_amount,txn_channel,merchant_id,city,country,txn_type,currency,is_international,txn_hour,txn_weekday,customer_age,account_open_days,txn_amount_sum_24h,txn_cnt_7d',
   y_cols='label',
   model_parameter=(n_estimators=5,loss='binary',is_unbalance='True'))
   AS (SELECT * FROM train_data);

2. 建立後，您可以通過/*polar4ai*/SHOW MODEL credit_card_model; 查看建立模型結果。若modelStatus = saved_oss代表已經建立成功。

評估模型效能

建立模型完成後，使用EVALUATE命令在獨立的驗證集（此處為簡化，仍使用train_data）上評估其效能。關鍵評估指標包括AUC和F-score。

• AUC（Area Under Curve）：衡量模型整體排序能力，值越接近1越好。

• F-score：綜合了精確率和召回率，是評估不平衡分類問題常用的指標。

評估樣本

/*polar4ai*/SELECT label FROM EVALUATE(MODEL credit_card_model,
 SELECT * FROM train_data) WITH (
    x_cols='txn_amount,txn_channel,merchant_id,city,country,txn_type,currency,is_international,txn_hour,txn_weekday,customer_age,account_open_days,txn_amount_sum_24h,txn_cnt_7d',
    y_cols='label',
    metrics = 'auc,Fscore',
    mode='async'
);

輸出樣本

執行EVALUATE後，會返回一個任務ID。您可以通過/*polar4ai*/ SHOW TASK `<TASK_id>`; 查看評估結果（results）。一個理想的評估結果應顯示較高的AUC和F-score，表明模型具備良好的欺詐識別能力。

{
  "auc": 0.95,
  "Fscore": "precision:{0: 1.0, 1: 1.0};recall:{0: 1.0, 1: 1.0};f1score:{0: 1.0, 1: 1.0}"
}

預測模型效能

評估模型完成後，使用PREDICT命令在獨立的驗證集（此處為簡化，仍使用train_data）上預測其效能。

預測樣本

/*polar4ai*/SELECT label FROM PREDICT(MODEL credit_card_model,
 SELECT * FROM train_data) WITH (
    x_cols='txn_amount,txn_channel,merchant_id,city,country,txn_type,currency,is_international,txn_hour,txn_weekday,customer_age,account_open_days,txn_amount_sum_24h,txn_cnt_7d',
    y_cols='label',
    mode='async'
);

執行PREDICT後，會返回一個任務ID。您可以通過/*polar4ai*/ SHOW TASK `<TASK_id>`;查看預測結果。如果任務完成（taskStatus=finish）後，在filePath中會得到一個OSS地址，OSS檔案即結果檔案。