Ta strona została przetłumaczona przez Cloud Translation API.

Prognozowanie oszustw i analiza geoprzestrzenna w zbiorze danych z programowania dla bankowości detalicznej

1. Wprowadzenie

W tym module utworzysz model regresji liniowej, który będzie przewidywać, czy ostatnie transakcje są fałszywe, przy użyciu SQL w BigQuery. Tworzysz zbiór danych treningowych, który zawiera każdą transakcję kartą i niektóre atrybuty, które naszym zdaniem najbardziej wskazują na oszustwo, takie jak odległość od domu klienta, pora dnia i kwota transakcji.

Następnie za pomocą BQML tworzysz model regresji logistycznej, który na podstawie naszych danych treningowych przewiduje, czy transakcja jest oszustwem. Jedną z zalet BQ ML jest możliwość nadmiernego dopasowania, dzięki czemu dane treningowe nie będą wpływać na wydajność modelu w przypadku nowych danych. Na koniec tworzysz 3 próbne transakcje o różnych cechach i prognozujesz, czy są fałszywe, czy nie, przy użyciu modelu.

Czego się nauczysz

Z tego modułu nauczysz się, jak:

Wczytaj zbiór danych z zasobnika Google Cloud Storage
Tworzenie danych treningowych
Utwórz i wytrenuj model regresji logistycznej
Użyj modelu, aby przewidzieć, czy przykładowe transakcje są fałszywe
Identyfikuj oszustwo na podstawie kodu pocztowego, korzystając z analizy geoprzestrzennej

2. Wczytaj zbiór danych z zasobnika GCS

W tym zadaniu utworzysz zbiór danych o nazwie bq_demo i wczytasz do niego dane dotyczące bankowości detalicznej z zasobnika GCS. Spowoduje to usunięcie wszystkich danych, które masz już w tabelach.

Otwórz Cloud Shell

Na pasku narzędzi w prawym górnym rogu konsoli Cloud kliknij przycisk Aktywuj Cloud Shell.

Po załadowaniu Cloud Shell wpisz:

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

Gdy skończysz, kliknij X, aby zamknąć terminal Cloud Shell. Udało Ci się wczytać zbiór danych z zasobnika Google Cloud Storage.

3. Tworzenie danych treningowych

Zapytania dotyczące fałszywych transakcji według typu karty

Zanim utworzymy dane treningowe, przeanalizujmy rozkład fałszywych transakcji między typami kart. Nasza baza danych bankowości detalicznej zawiera flagę, która wskazuje, że klient zgłosił nieuczciwą transakcję na jego koncie. To zapytanie pokazuje liczbę fałszywych transakcji według typu karty.

[Do rozmowy o konkurencji: w przeciwieństwie do niektórych rozwiązań konkurencji BigQuery nie wymaga eksportowania danych z hurtowni danych do zasobnika na dane, uruchamiania algorytmów systemów uczących się i kopiowania wyników z powrotem do bazy danych. Wszystkie te działania można przeprowadzić w dowolny sposób, co pozwoli zachować bezpieczeństwo danych i nie doprowadzi do „nadmiernego rozprzestrzeniania się danych”.]

Otwórz konsolę BigQuery:

W konsoli Google Cloud wybierz Menu nawigacyjne > BigQuery;

Otworzy się okno „Witamy w usłudze BigQuery w Cloud Console”. Zawiera ono link do krótkiego wprowadzenia oraz informacji o wersji.

Kliknij Gotowe.

Otworzy się konsola BigQuery.

Uruchom zapytanie w Edytorze zapytań:

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

A gdybyśmy mogli użyć tych danych do przewidywania fałszywych transakcji, jeszcze zanim klient o tym wie? Systemy uczące się są nie tylko dla ekspertów. Dzięki BigQuery analitycy mogą uruchamiać światowej klasy modele ML bezpośrednio na danych hurtowni danych za pomocą SQL.

Tworzenie danych treningowych

Utwórz zbiór danych treningowych, który zawiera każdą transakcję kartą i niektóre cechy, które naszym zdaniem najbardziej wskazują na oszustwo, takie jak odległość od domu klienta, pora dnia i kwota transakcji.

Uruchom zapytanie w Edytorze zapytań:

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

W sekcji „Wyniki” kliknij „Przejdź do tabeli”. Powinien pojawić się następujący wynik:

4. Tworzenie i trenowanie modelu

Użyj BQML do utworzenia modelu regresji logistycznej, aby na podstawie naszych danych treningowych utworzonych w poprzednim kroku przewidywać, czy transakcja jest oszustwem. Jedną z zalet BQML jest możliwość nadmiernego dopasowania, dzięki czemu dane treningowe nie będą wpływać na wydajność modelu w przypadku nowych danych.

Uruchom zapytanie w Edytorze zapytań:

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

Wyświetl szczegóły modelu

W sekcji „Wyniki” kliknij „Przejdź do modelu”.

Powinny się wyświetlić karty Schemat, Trening i Ocena.

Na karcie Training (Szkolenia) powinno pojawić się to:

Na karcie „Ocena” powinien pojawić się ten komunikat:

W sekcji „Schemat” (Karta schematu) powinno wyświetlić się to:

5. Używaj modelu do prognozowania oszustw

Teraz za pomocą naszego modelu przewidzimy, czy transakcja może być oszustwem. Utworzymy 3 próbne transakcje o różnych cechach.

Uruchom to zapytanie w edytorze zapytań:

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

Zostaną wyświetlone takie wyniki:

Jak widać, w naszym modelu pierwsza i trzecia transakcja jest niemal na pewno oszustwem. Druga i trzecia transakcja są raczej fałszywe. Ustawiliśmy próg na 55%, czyli wyższą z wartości domyślnej wynoszącej 50%. Mogliśmy wytrenować model przy użyciu dodatkowych danych lub dodać kolejne atrybuty, aby zwiększyć dokładność.

6. Identyfikuj oszustwo na podstawie kodu pocztowego, korzystając z analizy geoprzestrzennej

BigQuery zapewnia zaawansowaną obsługę danych geoprzestrzennych. Oto przykład, który wykorzystuje funkcję GIS ST_WITHIN do określenia kodu pocztowego na podstawie szerokości i długości geograficznej transakcji sprzedawców przy użyciu publicznego zbioru danych granic kodów pocztowych.

Uruchom w Edytorze zapytań ten kod:

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;

Powinny się wyświetlić te wyniki:

Jak widać, współczynnik oszustw w większości kodów pocztowych jest stosunkowo niewielki (poniżej 2%), ale w 69345 r. wynosi on 11%. Prawdopodobnie jest to coś, co chcemy zbadać.