4、特征提取基础：使用SQL提取单笔交易特征（交易金额、交易时间、交易对手IP等），并生成基础特征宽表

特征提取是反洗钱模型构建的起点。单笔交易特征直接从原始交易流水表中提取，不涉及跨表聚合或时间窗口计算。本章将演示如何使用SQL从交易流水表中提取金额、时间、IP等基础字段，并组装成可供下游模型直接使用的基础特征宽表。

4.1 单笔交易特征的定义与分类

单笔交易特征是指仅依赖当前交易记录本身即可计算的特征，不依赖历史数据或关联账户。根据反洗钱监测维度，通常分为以下四类：

4.2 基础特征提取SQL模板

以下SQL从交易流水表 txn_flow 中提取单笔交易特征，并生成基础特征宽表 feature_base_wide：

-- 基础特征宽表：单笔交易特征提取
CREATE TABLE feature_base_wide AS
SELECT
    -- 交易标识
    txn_id,
    account_id,
    counterparty_id,
    txn_type,
    
    -- 金额特征
    txn_amount,
    currency,
    CASE 
        WHEN currency = 'CNY' THEN txn_amount
        ELSE txn_amount * exchange_rate
    END AS amount_cny,               -- 折合人民币金额
    CASE 
        WHEN txn_amount >= 100000 THEN '大额'
        WHEN txn_amount >= 20000 THEN '较大'
        ELSE '普通'
    END AS amount_level,             -- 金额等级标签
    
    -- 时间特征
    txn_timestamp,
    DATE(txn_timestamp) AS txn_date,
    EXTRACT(HOUR FROM txn_timestamp) AS txn_hour,
    CASE 
        WHEN EXTRACT(HOUR FROM txn_timestamp) BETWEEN 6 AND 22 THEN '日间'
        ELSE '夜间'
    END AS txn_period,              -- 交易时段
    CASE 
        WHEN DAYOFWEEK(txn_timestamp) IN (1,7) THEN '周末'
        ELSE '工作日'
    END AS is_weekend,              -- 是否周末
    
    -- 渠道与位置特征
    channel_type,
    counterparty_ip,
    SUBSTRING_INDEX(counterparty_ip, '.', 1) AS ip_first_octet,  -- IP首段，用于初步地域判断
    device_id,
    
    -- 原始记录时间（用于数据回溯）
    etl_time
    
FROM txn_flow
WHERE txn_timestamp >= '2024-01-01';  -- 按需设置数据范围

4.3 特征提取流程架构

下图展示了从原始交易流水到基础特征宽表的完整提取流程：

4.4 特征字段加工技巧

在实际反洗钱场景中，原始字段往往需要经过加工才能成为有效特征。以下是三个常用加工场景：

4.4.1 金额特征加工

-- 金额特征加工示例
SELECT
    txn_id,
    txn_amount,
    -- 金额取整判断（可疑特征：整数大额）
    CASE WHEN txn_amount = FLOOR(txn_amount) THEN 1 ELSE 0 END AS is_round_amount,
    -- 金额尾数判断（可疑特征：.00结尾）
    CASE WHEN txn_amount * 100 % 100 = 0 THEN 1 ELSE 0 END AS is_cent_amount,
    -- 金额区间分箱
    CASE 
        WHEN txn_amount < 1000 THEN 'tiny'
        WHEN txn_amount < 10000 THEN 'small'
        WHEN txn_amount < 100000 THEN 'medium'
        WHEN txn_amount < 1000000 THEN 'large'
        ELSE 'huge'
    END AS amount_bucket
FROM txn_flow;

4.4.2 时间特征加工

-- 时间特征加工示例
SELECT
    txn_id,
    txn_timestamp,
    -- 提取时间分量
    EXTRACT(YEAR FROM txn_timestamp) AS txn_year,
    EXTRACT(MONTH FROM txn_timestamp) AS txn_month,
    EXTRACT(DAY FROM txn_timestamp) AS txn_day,
    EXTRACT(HOUR FROM txn_timestamp) AS txn_hour,
    EXTRACT(MINUTE FROM txn_timestamp) AS txn_minute,
    -- 是否为整点交易（可疑特征）
    CASE WHEN EXTRACT(MINUTE FROM txn_timestamp) = 0 THEN 1 ELSE 0 END AS is_whole_hour,
    -- 是否为节假日（需关联节假日表）
    CASE WHEN h.holiday_flag = 1 THEN 1 ELSE 0 END AS is_holiday
FROM txn_flow t
LEFT JOIN holiday_dim h ON DATE(t.txn_timestamp) = h.calendar_date;

4.4.3 IP特征加工

-- IP特征加工示例
SELECT
    txn_id,
    counterparty_ip,
    -- IP类型判断（内网/外网）
    CASE 
        WHEN counterparty_ip LIKE '10.%' OR counterparty_ip LIKE '192.168.%' 
             OR counterparty_ip LIKE '172.16.%' THEN 'internal'
        ELSE 'external'
    END AS ip_type,
    -- IP段提取（用于后续地域关联）
    SUBSTRING_INDEX(counterparty_ip, '.', 3) AS ip_subnet,
    -- IP是否为中国大陆IP（需关联IP归属地表）
    CASE WHEN ip_loc.country = '中国' THEN 1 ELSE 0 END AS is_domestic_ip
FROM txn_flow t
LEFT JOIN ip_location_dim ip_loc ON t.counterparty_ip = ip_loc.ip_address;

4.5 基础特征宽表质量检查

生成宽表后，必须进行质量检查，确保数据可用性。以下为检查清单：

4.6 特征宽表输出示例

以下为最终生成的基础特征宽表数据样例（仅展示关键字段）：

+----------+------------+----------+-----------+----------+-----------+------------------+
| txn_id   | txn_amount | amount_cny| txn_hour | txn_period| ip_type  | is_domestic_ip   |
+----------+------------+----------+-----------+----------+-----------+------------------+
| T0000001 | 50000.00   | 50000.00 | 14       | 日间     | external | 1                |
| T0000002 | 200000.00  | 200000.00| 23       | 夜间     | external | 0                |
| T0000003 | 9999.99    | 9999.99  | 9        | 日间     | internal | 1                |
| T0000004 | 1000000.00 | 1000000.00| 3       | 夜间     | external | 0                |
+----------+------------+----------+-----------+----------+-----------+------------------+