Python-Tutorial: Vorbereiten von Daten zum Kategorisieren von Kunden mithilfe von SQL Machine Learning

Gilt für: SQL Server 2017 (14.x) und höher Azure SQL Managed Instance

Im zweiten Teil dieser vierteiligen Tutorialreihe stellen Sie Daten aus einer Datenbank mithilfe von Python wieder her und bereiten diese vor. Diese Daten verwenden Sie in einem späteren Teil dieser Reihe zum Trainieren und Bereitstellen eines Clustermodells in Python mit SQL Server Machine Learning Services oder in Big Data-Clustern.

In diesem Artikel lernen Sie Folgendes:

• Aufteilen von Kunden über verschiedene Dimensionen mit Python
• Laden der Daten aus der Datenbank in einen Python-Datenrahmen

In Teil 1 haben Sie die Voraussetzungen installiert und die Beispieldatenbank wiederhergestellt.

In Teil 3 erfahren Sie, wie Sie ein K-Means-Clustermodell in Python erstellen und trainieren.

In Teil 4 erfahren Sie, wie Sie eine gespeicherte Prozedur in einer Datenbank erstellen, die Clustering auf der Grundlage neuer Daten in Python durchführen kann.

Voraussetzungen

• Teil 2 dieses Tutorials setzt voraus, dass Sie die Voraussetzungen von Teil 1 erfüllt haben.

Aufteilen von Kunden

Sie werden zunächst Kunden nach den folgenden Dimensionen aufteilen, um sich auf das Clustering von Kunden vorzubereiten:

• orderRatio = Retourenquote für Bestellungen (Gesamtzahl der teilweise oder vollständig zurückgegebenen Bestellungen im Vergleich zur Gesamtzahl der Bestellungen)
• itemsRatio = Retourenquote für Artikel (Gesamtzahl der zurückgegebenen Artikel im Vergleich zur Anzahl der gekauften Artikel)
• monetaryRatio = Rückzahlungsquote (Monetärer Gesamtbetrag der zurückgegebenen Artikel im Vergleich zum erworbenen Betrag)
• frequency = Häufigkeit der Retouren

Öffnen Sie ein neues Notebook in Azure Data Studio, und geben Sie das folgende Skript ein.

Ersetzen Sie bei Bedarf die Verbindungsdetails in der Verbindungszeichenfolge.

# Load packages.
import pyodbc
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
from scipy.spatial import distance as sci_distance
from sklearn import cluster as sk_cluster

################################################################################################

## Connect to DB and select data

################################################################################################

# Connection string to connect to SQL Server named instance.
conn_str = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; SERVER=<server>; DATABASE=tpcxbb_1gb; UID=<username>; PWD=<password>')

input_query = '''SELECT
ss_customer_sk AS customer,
ROUND(COALESCE(returns_count / NULLIF(1.0*orders_count, 0), 0), 7) AS orderRatio,
ROUND(COALESCE(returns_items / NULLIF(1.0*orders_items, 0), 0), 7) AS itemsRatio,
ROUND(COALESCE(returns_money / NULLIF(1.0*orders_money, 0), 0), 7) AS monetaryRatio,
COALESCE(returns_count, 0) AS frequency
FROM
(
  SELECT
    ss_customer_sk,
    -- return order ratio
    COUNT(distinct(ss_ticket_number)) AS orders_count,
    -- return ss_item_sk ratio
    COUNT(ss_item_sk) AS orders_items,
    -- return monetary amount ratio
    SUM( ss_net_paid ) AS orders_money
  FROM store_sales s
  GROUP BY ss_customer_sk
) orders
LEFT OUTER JOIN
(
  SELECT
    sr_customer_sk,
    -- return order ratio
    count(distinct(sr_ticket_number)) as returns_count,
    -- return ss_item_sk ratio
    COUNT(sr_item_sk) as returns_items,
    -- return monetary amount ratio
    SUM( sr_return_amt ) AS returns_money
FROM store_returns
GROUP BY sr_customer_sk ) returned ON ss_customer_sk=sr_customer_sk'''


# Define the columns we wish to import.
column_info = {
    "customer": {"type": "integer"},
    "orderRatio": {"type": "integer"},
    "itemsRatio": {"type": "integer"},
    "frequency": {"type": "integer"}
}

Laden der Daten in einem neuen Datenrahmen

Die Ergebnisse der Abfrage werden mit der Pandas-Funktion read_sql an Python zurückgegeben. Als Teil des Prozesses verwenden Sie die Spalteninformationen, die Sie im vorherigen Skript definiert haben.

customer_data = pd.read_sql(input_query, conn_str)

Zeigen Sie nun den Anfang des Datenrahmens an, um sicherzustellen, dass er korrekt ist.

print("Data frame:", customer_data.head(n=5))

Rows Read: 37336, Total Rows Processed: 37336, Total Chunk Time: 0.172 seconds
Data frame:     customer  orderRatio  itemsRatio  monetaryRatio  frequency
0    29727.0    0.000000    0.000000       0.000000          0
1    97643.0    0.068182    0.078176       0.037034          3
2    57247.0    0.000000    0.000000       0.000000          0
3    32549.0    0.086957    0.068657       0.031281          4
4     2040.0    0.000000    0.000000       0.000000          0

Bereinigen von Ressourcen

Wenn Sie nicht mit diesem Tutorial fortfahren möchten, löschen Sie die Datenbank „tpcxbb_1gb“.

Nächste Schritte

In Teil 2 dieser Tutorialreihe haben Sie die folgenden Schritte ausgeführt:

• Aufteilen von Kunden über verschiedene Dimensionen mit Python
• Laden der Daten aus der Datenbank in einen Python-Datenrahmen

Befolgen Sie Teil 3 dieser Tutorialreihe, um ein Machine Learning-Modell zu erstellen, das diese Kundendaten verwendet:

Python-Tutorial: Erstellen eines Vorhersagemodells