Diese Seite wurde von der Cloud Translation API übersetzt.

Codelab zur Betrugsvorhersage und raumbezogenen Analysen für das Dataset mit dem Einzelhandel

1. Einführung

In diesem Lab erstellen Sie ein lineares Regressionsmodell, mit dem Sie vorhersagen können, ob die letzten Transaktionen mit SQL in BigQuery betrügerisch sind. Sie erstellen ein Trainings-Dataset, das alle Kartentransaktionen und einige der Attribute enthält, die nach unserer Einschätzung am besten auf Betrug hinweisen, z. B. Entfernung vom Haus des Kunden, Tageszeit und Transaktionsbetrag.

Anschließend erstellen Sie mit BQML ein logistisches Regressionsmodell, um anhand unserer Trainingsdaten vorherzusagen, ob eine Transaktion betrügerisch ist. Zu den Vorteilen von BQ ML gehört die Überanpassung, damit unsere Trainingsdaten sich nicht auf die Leistung des Modells bei neuen Daten auswirken. Schließlich erstellen Sie drei Beispieltransaktionen mit unterschiedlichen Merkmalen und sagen, ob sie betrügerisch sind oder das Modell nicht verwenden.

Lerninhalte

Aufgaben in diesem Lab:

Dataset aus einem Google Cloud Storage-Bucket laden
Trainingsdaten erstellen
Logistisches Regressionsmodell erstellen und trainieren
Modell verwenden, um vorherzusagen, ob Beispieltransaktionen betrügerisch sind oder nicht
Betrugstransaktionen nach Postleitzahl mithilfe raumbezogener Analyse erkennen

2. Dataset aus einem GCS-Bucket laden

In dieser Aufgabe erstellen Sie ein Dataset namens bq_demo und laden es mit Retail Banking-Daten aus einem GCS-Bucket. Dadurch werden alle vorhandenen Daten gelöscht, die sich bereits in den Tabellen befinden.

Cloud Shell öffnen

Klicken Sie in der Cloud Console in der Symbolleiste rechts oben auf die Schaltfläche „Cloud Shell aktivieren“.

Nachdem Cloud Shell geladen wurde, geben Sie Folgendes ein:

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

Wenn Sie fertig sind, klicken Sie auf das „X“, um das Cloud Shell-Terminal zu schließen. Sie haben ein Dataset aus einem Google Cloud Storage-Bucket geladen.

3. Trainingsdaten erstellen

Abfrage nach betrügerischen Transaktionen nach Kartentyp

Bevor wir Trainingsdaten erstellen, analysieren wir, wie sich betrügerische Transaktionen auf die Kartentypen verteilen. Unsere Datenbank für Privatbanken enthält eine Markierung, die anzeigt, wenn ein Kunde eine betrügerische Transaktion in seinem Konto gemeldet hat. Diese Abfrage zeigt die Anzahl betrügerischer Transaktionen nach Kartentyp.

[Gesprächspunkt der Konkurrenz: Im Gegensatz zu bestimmten Mitbewerbern müssen Sie bei BigQuery die Daten in Ihrem Data Warehouse nicht in einen Storage-Bucket exportieren, Algorithmen für maschinelles Lernen ausführen und die Ergebnisse dann zurück in die Datenbank kopieren. All dies kann direkt umgesetzt werden, wodurch die Datensicherheit gewahrt bleibt und nicht zu einer „Datenflut“ führt.]

Öffnen Sie die BigQuery-Konsole:

Klicken Sie in der Google Cloud Console auf „Navigationsmenü“ > BigQuery

Das Fenster Willkommen bei BigQuery in der Cloud Console wird geöffnet. das neben allgemeinen Informationen auch einen Link zur Kurzanleitung und zu den Versionshinweisen enthält.

Klicken Sie auf "Fertig".

Die BigQuery Console wird geöffnet.

Führen Sie die Abfrage im Abfrageeditor aus:

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

Aber was wäre, wenn wir anhand dieser Daten betrügerische Transaktionen vorhersagen könnten, noch bevor der Kunde es bemerkt? ML ist nicht nur für Fachleute gedacht. Mit BigQuery können Analysten erstklassige ML-Modelle direkt über SQL auf Data-Warehouse-Daten ausführen.

Trainingsdaten erstellen

Erstellen Sie ein Trainings-Dataset, das alle Kartentransaktionen und einige der Attribute enthält, die nach unserer Einschätzung am besten auf Betrug hindeuten, z. B. Entfernung vom Haus des Kunden, Tageszeit und Transaktionsbetrag.

Führen Sie die Abfrage im Abfrageeditor aus:

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 );

Unter „Ergebnisse“ klicken Sie auf „Zur Tabelle“ Sie sollten das folgende Ergebnis sehen:

4. Modell erstellen und trainieren

Erstellen Sie mit BQML ein logistisches Regressionsmodell, um anhand der im vorherigen Schritt erstellten Trainingsdaten vorherzusagen, ob eine Transaktion betrügerisch ist. Eines der schönen Merkmale von BQML ist die Überanpassung, damit unsere Trainingsdaten die Leistung des Modells bei neuen Daten nicht beeinträchtigen.

Führen Sie die Abfrage im Abfrageeditor aus:

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));

Modelldetails ansehen

Klicken Sie unter „Ergebnisse“ auf „Zum Modell wechseln“.

Sie sollten die Tabs „Schema“, „Training“ und „Bewertung“ sehen.

Auf dem Tab „Training“ sollten Sie Folgendes sehen:

Auf dem Tab „Bewertung“ sollten Sie Folgendes sehen:

Auf dem Tab „Schema“ sollten Sie Folgendes sehen:

5. Modell zur Betrugsvorhersage verwenden

Lassen Sie uns nun mithilfe unseres Modells vorhersagen, ob eine Transaktion potenziell betrügerisch ist. Wir erstellen drei Beispieltransaktionen mit unterschiedlichen Eigenschaften.

Führen Sie im Abfrageeditor die folgende Abfrage aus:

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)
);

Das Ergebnis sollte so aussehen:

Wie Sie sehen können, gilt die erste Transaktion gemäß unserem Modell mit ziemlicher Sicherheit, während die zweite und dritte Transaktion voraussichtlich nicht als betrügerisch zu werten sind. Wir haben den Grenzwert von standardmäßig 50 % auf 55 % festgelegt. Wir könnten unser Modell mit zusätzlichen Daten trainieren oder weitere Attribute hinzufügen, um die Genauigkeit zu verbessern.

6. Betrugstransaktionen nach Postleitzahl mithilfe raumbezogener Analyse erkennen

BigQuery bietet umfassende Unterstützung für Geodaten. Im folgenden Beispiel wird die GIS-Funktion ST_WITHIN verwendet, um die Postleitzahl anhand des Breiten- und Längengrads von Händlertransaktionen unter Verwendung eines öffentlichen Datasets mit Postleitzahlengrenzen zu bestimmen.

Führen Sie im Abfrageeditor den folgenden Code aus:

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;

Sie sollten folgende Ergebnisse sehen:

Wie Sie sehen, ist die Betrugsrate bei den meisten Postleitzahlen relativ gering (unter 2%), aber die Betrugsrate im Jahr 69345 beträgt überraschend 11%. Wahrscheinlich möchten wir das genauer untersuchen.