1. 簡介
在這個研究室中，您將建立線性迴歸模型，以預測近期交易是否在 BigQuery 中使用 SQL 進行詐欺。您建立了訓練資料集，內含每筆卡片交易，以及我們判斷最能代表詐欺性的某些屬性，例如客戶住家的距離、時段和交易金額。
接著，您將使用 BQML 建構邏輯迴歸模型，依據訓練資料來預測交易是否涉及詐欺行為。BQ ML 的其中一項優點是可以處理過度配適的問題，這樣我們的訓練資料就不會影響模型對新資料的效能。最後，您要建立三個特徵不同的範例交易，並預測這些交易是否以詐欺為由，或未使用模型。
學習目標
在本研究室中，您將瞭解如何執行下列工作：
- 從 Google Cloud Storage 值區載入資料集
- 建立訓練資料
- 建立及訓練邏輯迴歸模型
- 使用模型預測樣本交易是否為詐欺
- 運用地理空間分析功能，根據郵遞區號識別詐欺交易
2. 從 GCS 值區載入資料集
在這項工作中，您會建立名為 bq_demo 的資料集，並利用 GCS 值區中的零售銀行資料載入該資料集。這麼做會刪除資料表中現有的任何資料。
開啟 Cloud Shell
- 在 Cloud 控制台的右上方找到工具列，然後按一下「啟用 Cloud Shell」按鈕。
- 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
- 完成後，按一下「X」即可關閉 Cloud Shell 終端機。您已成功從 Google Cloud Storage 值區載入資料集。
3. 建立訓練資料
查詢各種卡片類型的詐欺交易
建立訓練資料前，讓我們來分析詐欺交易在各種卡片類型中的分配情形。我們的零售銀行資料庫內含標記，用於指出客戶回報帳戶進行詐欺交易。這項查詢會依卡片類型顯示詐欺交易次數。
[競爭談話要點： 與特定競爭對手不同，BigQuery 不需要您先將資料倉儲中的資料匯出至儲存空間值區，並執行機器學習演算法，再將結果複製回資料庫。這一切都能建立完成，不僅可確保資料安全性，而且不會導致「資料激增」。]
- 開啟 BigQuery 控制台：
在 Google Cloud 控制台中，依序選取「導覽選單」圖示 >BigQuery。
- 接著，畫面中會顯示「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊。當中會列出快速入門導覽課程指南的連結和版本資訊。
按一下 [完成]。
BigQuery 控制台會隨即開啟。
- 在查詢編輯器中執行查詢：
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 );
在「結果」下方點按「前往表格」畫面上應會顯示以下結果：
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));
查看模型詳細資料
點選「結果」下方的「前往模型」。
您應該會看到「Schema」、「Training and Evaluation」(結構定義、訓練和評估) 分頁標籤。
「訓練分頁」下方應該會顯示以下內容：
「Evaluation」分頁下方應該會顯示：
在「結構定義」分頁，您應該會看到以下內容：
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)
);
預期會得到以下結果：
如您所見，根據我們的模型，第一筆交易幾乎是詐欺性的，而第 2 筆和第 3 筆交易預計不涉及詐欺。我們已經將門檻設為 55% (從預設值 50% 開始這麼多)。我們可以使用其他資料或加入額外屬性來訓練模型，進而提高準確度。
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;
畫面上會顯示以下結果：
如您所見，大多數郵遞區號的詐欺率相對較小 (低於 2%)，但 69345 年的詐欺率是出乎意料的 11%。這可能是我們需要調查的情況。