Zelfstudie: Een clusteringmodel implementeren in R met SQL Machine Learning

Van toepassing op: SQL Server 2016 (13.x) en latere versies van Azure SQL Managed Instance

Als u clustering regelmatig wilt uitvoeren, moet u, omdat nieuwe klanten zich registreren, het R-script vanuit elke app kunnen aanroepen. Hiervoor kunt u het R-script in een database implementeren door het R-script in een opgeslagen SQL-procedure te plaatsen. Omdat uw model wordt uitgevoerd in de database, kan het eenvoudig worden getraind op basis van gegevens die zijn opgeslagen in de database.

In dit artikel leert u het volgende:

In deel 1 hebt u de vereisten geïnstalleerd en de voorbeelddatabase hersteld.

In deel twee hebt u geleerd hoe u de gegevens uit een database voorbereidt om clustering uit te voeren.

In deel drie hebt u geleerd hoe u een K-Means-clusteringmodel maakt en traint in R.

Prerequisites

• In deel vier van deze reeks zelfstudies wordt ervan uitgegaan dat u aan de vereisten van deel 1 hebt voldaan en dat u de stappen in deel 2 en deel drie hebt voltooid.

Een opgeslagen procedure maken waarmee het model wordt gegenereerd

Voer het volgende T-SQL-script uit om de opgeslagen procedure te maken. Met de procedure worden de stappen die u in deel twee en drie van deze reeks zelfstudies hebt ontwikkeld, opnieuw gemaakt:

• klanten classificeren op basis van hun aankoop- en retourgeschiedenis
• vier clusters van klanten genereren met behulp van een K-Means-algoritme

In de procedure worden de resulterende klantcluster-toewijzingen opgeslagen in de databasetabel customer_return_clusters.

USE [tpcxbb_1gb]
DROP PROC IF EXISTS generate_customer_return_clusters;
GO
CREATE procedure [dbo].[generate_customer_return_clusters]
AS
/*
  This procedure uses R to classify customers into different groups
  based on their purchase & return history.
*/
BEGIN
    DECLARE @duration FLOAT
    , @instance_name NVARCHAR(100) = @@SERVERNAME
    , @database_name NVARCHAR(128) = db_name()
-- Input query to generate the purchase history & return metrics
    , @input_query NVARCHAR(MAX) = N'
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
 '
EXECUTE sp_execute_external_script
      @language = N'R'
    , @script = N'
# Define the connection string

connStr <- paste("Driver=SQL Server; Server=", instance_name,
                 "; Database=", database_name,
                 "; uid=Username;pwd=Password; ",
                 sep="" )

# Input customer data that needs to be classified.
# This is the result we get from the query.
library(RODBC)

ch <- odbcDriverConnect(connStr);

customer_data <- sqlQuery(ch, input_query)

sqlDrop(ch, "customer_return_clusters")

## create clustering model
clust <- kmeans(customer_data[,2:5],4)

## create clustering output for table
customer_cluster <- data.frame(cluster=clust$cluster,customer=customer_data$customer,orderRatio=customer_data$orderRatio,
			itemsRatio=customer_data$itemsRatio,monetaryRatio=customer_data$monetaryRatio,frequency=customer_data$frequency)

## write cluster output to DB table
sqlSave(ch, customer_cluster, tablename = "customer_return_clusters")

## clean up
odbcClose(ch)
'
    , @input_data_1 = N''
    , @params = N'@instance_name nvarchar(100), @database_name nvarchar(128), @input_query nvarchar(max), @duration float OUTPUT'
    , @instance_name = @instance_name
    , @database_name = @database_name
    , @input_query = @input_query
    , @duration = @duration OUTPUT;
END;

GO

Clustering uitvoeren

Nu u de opgeslagen procedure hebt gemaakt, voert u het volgende script uit om clustering uit te voeren.

--Empty table of the results before running the stored procedure
TRUNCATE TABLE customer_return_clusters;

--Execute the clustering
--This will load the table customer_return_clusters with cluster mappings
EXECUTE [dbo].[generate_customer_return_clusters];

Controleer of het werkt en of we de lijst met klanten en hun clustertoewijzingen hebben.

--Select data from table customer_return_clusters
--to verify that the clustering data was loaded
SELECT TOP (5) *
FROM customer_return_clusters;

Hier is het resultatenoverzicht.

cluster  customer  orderRatio  itemsRatio  monetaryRatio  frequency
1        29727     0           0           0              0
4        26429     0           0           0.041979       1
2        60053     0           0           0.065762       3
2        97643     0           0           0.037034       3
2        32549     0           0           0.031281       4

De clusterinformatie gebruiken

Omdat u de clusterprocedure in de database hebt opgeslagen, kan het clusteren efficiënt worden uitgevoerd op basis van klantgegevens die zijn opgeslagen in dezelfde database. U kunt de procedure uitvoeren wanneer uw klantgegevens worden bijgewerkt en de bijgewerkte clusterinformatie gebruiken.

Stel dat u een promotie-e-mail wilt verzenden naar klanten in cluster 0, de groep die inactief was (u kunt zien hoe de vier clusters zijn beschreven in deel drie van deze zelfstudie). Met de volgende code worden de e-mailadressen van klanten in cluster 0 geselecteerd.

USE [tpcxbb_1gb]
--Get email addresses of customers in cluster 0 for a promotion campaign
SELECT customer.[c_email_address], customer.c_customer_sk
  FROM dbo.customer
  JOIN
  [dbo].[customer_clusters] as c
  ON c.Customer = customer.c_customer_sk
  WHERE c.cluster = 0

U kunt de c.cluster-waarde wijzigen om e-mailadressen te retourneren voor klanten in andere clusters.

De hulpbronnen opschonen

Wanneer u klaar bent met deze zelfstudie, kunt u de tpcxbb_1gb-database verwijderen.

Volgende stappen

In deel vier van deze reeks zelfstudies hebt u geleerd hoe u het volgende kunt doen:

• Een opgeslagen procedure maken waarmee het model wordt gegenereerd
• Clustering uitvoeren met SQL Machine Learning
• De clusterinformatie gebruiken

Zie voor meer informatie over het gebruik van R in Machine Learning Services:

• Eenvoudige R-scripts uitvoeren
• R-gegevensstructuren, -typen en -objecten
• R-functies