零售银行数据集的欺诈预测和地理空间分析 Codelab

1. 简介

在本实验中，您将使用 BigQuery 中的 SQL 创建一个线性回归模型来预测近期事务是否具有欺诈性。您需要创建一个训练数据集，其中包含每笔卡交易以及我们认定为最能表明欺诈的一些属性，例如距离客户家的距离、时间和交易金额。

然后，您使用 BQML 构建逻辑回归模型，以根据我们的训练数据预测某交易是否具有欺诈性。BQ ML 的一个优点是可以处理过拟合，使训练数据不会影响模型在新数据上的表现。最后，您创建三项具有不同特征的示例交易，并预测这些交易是否具有欺诈性或未使用模型。

学习内容

在本实验中，您将学习如何执行以下任务：

  • 从 Google Cloud Storage 存储分区加载数据集
  • 创建训练数据
  • 创建和训练逻辑回归模型
  • 使用模型预测样本交易是否具有欺诈性
  • 利用地理空间分析，按邮政编码识别欺诈交易

2. 从 GCS 存储分区加载数据集

在此任务中，您将创建一个名为 bq_demo 的数据集，并使用 GCS 存储分区中的零售银行数据加载该数据集。此操作会删除表中的任何现有数据。

打开 Cloud Shell

  1. 在 Cloud 控制台右上角的工具栏中，点击“激活 Cloud Shell”按钮。

3c0c949fe3626208

  1. Cloud Shell 加载后，输入以下命令：
bq rm -r -f -d bq_demo
bq rm -r -f -d bq_demo_shared
bq mk --dataset bq_demo
bq load --replace --autodetect --source_format=CSV bq_demo.account gs://retail-banking-looker/account
bq load --replace --autodetect --source_format=CSV bq_demo.base_card gs://retail-banking-looker/base_card
bq load --replace --autodetect --source_format=CSV bq_demo.card gs://retail-banking-looker/card
bq load --replace --autodetect --source_format=CSV bq_demo.card_payment_amounts gs://retail-banking-looker/card_payment_amounts
bq load --replace --autodetect --source_format=CSV bq_demo.card_transactions gs://retail-banking-looker/card_transactions
bq load --replace --autodetect --source_format=CSV bq_demo.card_type_facts gs://retail-banking-looker/card_type_facts
bq load --replace --autodetect --source_format=CSV bq_demo.client gs://retail-banking-looker/client
bq load --replace --autodetect --source_format=CSV bq_demo.disp gs://retail-banking-looker/disp
bq load --replace --autodetect --source_format=CSV bq_demo.loan gs://retail-banking-looker/loan
bq load --replace --autodetect --source_format=CSV bq_demo.order gs://retail-banking-looker/order
bq load --replace --autodetect --source_format=CSV bq_demo.trans gs://retail-banking-looker/trans
  1. 完成后，点击 X 以关闭 Cloud Shell 终端。您已成功从 Google Cloud Storage 存储分区加载了数据集。

3. 创建训练数据

按卡类型查询欺诈性交易

在创建训练数据之前，我们先分析欺诈性交易在银行卡类型之间的分布情况。我们的零售银行数据库包含一个标志，该标志会指明客户何时报告其账户中存在欺诈性交易。此查询会按银行卡类型显示欺诈性交易的数量。

[竞争性谈话要点 ：与其他竞争对手不同，BigQuery 不要求您将数据仓库中的数据导出到存储分区、运行机器学习算法，然后将结果复制回数据库。这一切都可以就地完成，这样可以确保数据安全，并且不会导致“数据扩张”。]

  1. 打开 BigQuery 控制台：

在 Google Cloud 控制台中，选择导航菜单 >BigQuery。

dfdbd753a900e349.png

  1. 您会看到“欢迎在 Cloud 控制台中使用 BigQuery”消息框。其中提供了指向快速入门指南和版本说明的链接。

点击“完成”。

BigQuery 控制台即会打开。

2982953ee582e477

  1. 在查询编辑器中运行查询：
SELECT c.type, count(trans_id) as fraud_transactions
FROM bq_demo.card_transactions AS t 
JOIN bq_demo.card c ON t.cc_number = c.card_number
WHERE t.is_fraud=1
GROUP BY type

但是，如果我们能在客户注意到之前就利用这些数据来预测欺诈性交易呢？机器学习不仅仅是专家的能手，借助 BigQuery，分析人员可以通过 SQL 直接在数据仓库数据上运行世界一流的机器学习模型。

创建训练数据

创建一个训练数据集，其中包含每笔卡交易以及我们认定为最能表明欺诈的一些属性，例如距客户家的距离、时间和交易金额。

在查询编辑器中运行查询：

CREATE OR REPLACE TABLE bq_demo.training_data as (
  SELECT
    card_transactions.trans_id  AS trans_id,
    card_transactions.is_fraud  AS is_fraud,
    --amount for transaction: higher amounts are more likely to be fraud
    cast(card_transactions.amount as FLOAT64)  AS card_transactions_amount,
    --distance from the customers home: further distances are more likely to be fraud
    ST_DISTANCE((ST_GEOGPOINT((cast(card_transactions.merchant_lon as FLOAT64)),
            (cast(card_transactions.merchant_lat as FLOAT64)))), (ST_GeogPoint((cast(SPLIT(client.address,'|')[OFFSET(4)] as float64)),
            (cast(SPLIT(client.address,'|')[OFFSET(3)] as float64)))))  AS card_transactions_transaction_distance,
    --hour that transaction occured: fraud occurs in middle of night (usually between midnight and 4 am)
    EXTRACT(HOUR FROM TIMESTAMP(CONCAT(card_transactions.trans_date,' ',card_transactions.trans_time)) ) AS card_transactions_transaction_hour_of_day
  FROM bq_demo.card_transactions  AS card_transactions
  LEFT JOIN bq_demo.card  AS card ON card.card_number = card_transactions.cc_number
  LEFT JOIN bq_demo.disp  AS disp ON card.disp_id = disp.disp_id
  LEFT JOIN bq_demo.client  AS client ON disp.client_id = client.client_id );

在“结果”下点击“转至表格”您应该会看到以下结果：

c03442924d2dc7f3.png

4. 创建和训练模型

使用 BQML 构建逻辑回归模型，根据上一步中创建的训练数据预测交易是否具有欺诈性。BQML 的一个优点是可以处理过拟合，使训练数据不会影响模型在新数据上的表现。

在查询编辑器中运行查询：

CREATE OR REPLACE MODEL bq_demo.fraud_prediction
    OPTIONS(model_type='logistic_reg', labels=['is_fraud']) AS
    SELECT * EXCEPT(trans_id)
    FROM bq_demo.training_data
    WHERE (is_fraud = 1) OR 
            (is_fraud = 0 AND rand() <= 
                (SELECT SUM(is_fraud)/COUNT(*) FROM bq_demo.training_data));

查看模型详情

在“结果”下，点击“转到模型”。

9417986e0fa45427

您应该会看到“架构”“训练”和“评估”标签页。

在“训练”标签页下，您应该会看到以下内容：

cb6b11a01816ba74.png

在“评估”标签页下，您应该会看到以下内容：

ff47d42700a79544.png

在“架构”标签页下，您应该会看到以下内容：

edfd13025ed64705.png

5. 使用模型预测欺诈行为

现在，我们使用模型来预测某笔交易是否可能具有欺诈性。我们将创建 3 项具有不同特征的交易示例。

在查询编辑器中运行以下查询：

SELECT * FROM ML.PREDICT(MODEL bq_demo.fraud_prediction, (
  SELECT '001' as trans_id, 500.00 as card_transactions_amount, 600 as card_transactions_transaction_distance, 2 as card_transactions_transaction_hour_of_day
    UNION ALL
  SELECT '002' as trans_id, 5.25 as card_transactions_amount, 2 as card_transactions_transaction_distance, 13 as card_transactions_transaction_hour_of_day
    UNION ALL
  SELECT '003' as trans_id, 175.50 as card_transactions_amount, 45 as card_transactions_transaction_distance, 10 as card_transactions_transaction_hour_of_day
  ), STRUCT(0.55 AS threshold)
);

您应会看到如下结果：

c3c25d3652e45c9c.png

如您所见，根据我们的模型，第一笔交易几乎肯定是欺诈性交易，而第 2 笔和第 3 笔交易预计不是欺诈性交易。我们已将阈值从默认的 50% 提高到 55%。我们可以使用其他数据或添加其他属性来训练模型，以提高准确性。

6. 利用地理空间分析，按邮政编码识别欺诈交易

BigQuery 可为地理空间数据提供丰富的支持。以下示例使用 GIS 函数 ST_WITHIN 根据邮政编码边界的公开数据集，根据商家交易的纬度和经度确定邮政编码。

在查询编辑器中运行以下代码：

WITH trans_by_zip as (
  SELECT
    card_transactions.trans_id,
    zip_code AS merchant_zip,
    city as merchant_city,
    county as merchant_county,
    state_name as merchant_state
  FROM
    bq_demo.card_transactions AS card_transactions,
    bigquery-public-data.geo_us_boundaries.zip_codes AS zip_codes
  WHERE ST_Within(ST_GEOGPOINT((cast(card_transactions.merchant_lon as FLOAT64)),(cast(card_transactions.merchant_lat as FLOAT64))),zip_codes.zip_code_geom)
)
SELECT merchant_zip, 1.0 * (SUM(is_fraud)) / nullif((COUNT(*)),0) AS percent_transactions_fraud
FROM bq_demo.card_transactions t, trans_by_zip
WHERE t.trans_id = trans_by_zip.trans_id
GROUP BY merchant_zip
ORDER BY percent_transactions_fraud DESC;

您应该会看到以下结果：

6f0dfb6d93c0be11

如您所见，大多数邮政编码的欺诈率都相对较低（低于 2%），但 69345 的欺诈率却高达 11%。这可能是我们想要调查的问题。