Tutorial: Vorbereiten von Daten für das Clustering in R mit SQL Machine Learning
Gilt für: SQL Server 2016 (13.x) und höher Azure SQL Managed Instance
Im zweiten Teil dieser vierteiligen Tutorialreihe bereiten Sie die Daten aus einer Datenbank für das Clustering in R mit SQL Server Machine Learning Services oder in Big Data-Clustern vor.
Im zweiten Teil dieser vierteiligen Tutorialreihe bereiten Sie die Daten aus einer Datenbank für das Clustering in R mit SQL Server Machine Learning Services vor.
Im zweiten Teil dieser vierteiligen Tutorialreihe bereiten Sie die Daten aus einer Datenbank für das Clustering in R mit SQL Server 2016 R Services vor.
Im zweiten Teil dieser vierteiligen Tutorialreihe bereiten Sie die Daten aus einer Datenbank für das Clustering in R mit Machine Learning Services in Azure SQL Managed Instance vor.
In diesem Artikel lernen Sie Folgendes:
- Trennen von Kunden anhand verschiedener Dimensionen mithilfe von R
- Laden der Daten aus der Datenbank in einen R-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 R 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 R durchführen kann.
Voraussetzungen
- Im zweiten Teil dieses Tutorials wird davon ausgegangen, dass Sie Teil 1 abgeschlossen haben.
Aufteilen von Kunden
Erstellen Sie eine neue RScript-Datei in RStudio, und führen Sie das folgende Skript aus. In der SQL-Abfrage trennen Sie Kunden anhand der folgenden Dimensionen:
- 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
Ersetzen Sie in der connStr-Funktion ersetzen Sie ServerName durch Ihre eigenen Verbindungsinformationen.
# Define the connection string to connect to the tpcxbb_1gb database
connStr <- "Driver=SQL Server;Server=ServerName;Database=tpcxbb_1gb;uid=Username;pwd=Password"
#Define the query to select data
input_query <- "
SELECT ss_customer_sk AS customer
,round(CASE
WHEN (
(orders_count = 0)
OR (returns_count IS NULL)
OR (orders_count IS NULL)
OR ((returns_count / orders_count) IS NULL)
)
THEN 0.0
ELSE (cast(returns_count AS NCHAR(10)) / orders_count)
END, 7) AS orderRatio
,round(CASE
WHEN (
(orders_items = 0)
OR (returns_items IS NULL)
OR (orders_items IS NULL)
OR ((returns_items / orders_items) IS NULL)
)
THEN 0.0
ELSE (cast(returns_items AS NCHAR(10)) / orders_items)
END, 7) AS itemsRatio
,round(CASE
WHEN (
(orders_money = 0)
OR (returns_money IS NULL)
OR (orders_money IS NULL)
OR ((returns_money / orders_money) IS NULL)
)
THEN 0.0
ELSE (cast(returns_money AS NCHAR(10)) / orders_money)
END, 7) AS monetaryRatio
,round(CASE
WHEN (returns_count IS NULL)
THEN 0.0
ELSE returns_count
END, 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";
Laden der Daten in einem neuen Datenrahmen
Verwenden Sie nun das folgende Skript, um die Ergebnisse der Abfrage an einen R-Datenrahmen zurückzugeben.
# Query using input_query and get the results back
# to data frame customer_data
library(RODBC)
ch <- odbcDriverConnect(connStr)
customer_data <- sqlQuery(ch, input_query)
# Take a look at the data just loaded
head(customer_data, n = 5);
Das Ergebnis sollte etwa folgendermaßen aussehen:
customer orderRatio itemsRatio monetaryRatio frequency
1 29727 0 0 0.000000 0
2 26429 0 0 0.041979 1
3 60053 0 0 0.065762 3
4 97643 0 0 0.037034 3
5 32549 0 0 0.031281 4
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 Folgendes gelernt:
- Trennen von Kunden anhand verschiedener Dimensionen mithilfe von R
- Laden der Daten aus der Datenbank in einen R-Datenrahmen
Befolgen Sie Teil 3 dieser Tutorialreihe, um ein Machine Learning-Modell zu erstellen, das diese Kundendaten verwendet: