Oktatóanyag: Fürtözési modell telepítése R-ben a SQL Machine Learning használatával

A következőkre vonatkozik: Sql Server 2016 (13.x) és újabb verziók felügyelt Azure SQL-példány

A négyrészes oktatóanyag-sorozat negyedik részében egy R-ben fejlesztett fürtözési modellt fog üzembe helyezni egy SQL Server Machine Learning Services vagy Big Data-fürtök használatával.

Ahhoz, hogy rendszeresen klaszterezést végezzen, amikor új ügyfelek regisztrálnak, minden alkalmazásból meg kell tudnia hívni az R scriptet. Ehhez üzembe helyezheti az R-szkriptet egy adatbázisban úgy, hogy az R-szkriptet egy SQL-ben tárolt eljárásba helyezi. Mivel a modell az adatbázisban fut, könnyen betanítható a rá vonatkozó, az adatbázisban tárolt adatokra.

Ebben a cikkben megtudhatja, hogyan:

• A modellt létrehozó tárolt eljárás létrehozása
• Klaszterezés végrehajtása
• A fürtözési adatok használata

Az első részben telepítette az előfeltételeket, és visszaállította a mintaadatbázist.

A második részben megtanulta, hogyan készítheti elő az adatokat egy adatbázisból a fürtözés végrehajtására.

A harmadik részben megtanulta, hogyan hozhat létre és taníthat be K-Means fürtözési modellt az R-ben.

Prerequisites

• Az oktatóanyag-sorozat negyedik része feltételezi, hogy teljesítette az első rész előfeltételeit, és elvégezte a második és harmadik rész lépéseit.

A modellt létrehozó tárolt eljárás létrehozása

Futtassa a következő T-SQL-szkriptet a tárolt eljárás létrehozásához. Az eljárás újra létrehozza az oktatóanyag-sorozat második és harmadik részében kifejlesztett lépéseket:

• az ügyfelek besorolása a vásárlási és visszaküldési előzmények alapján
• K-Means algoritmus használatával négy ügyfélcsoport generálása

Az eljárás az eredményként kapott ügyfélfürt-leképezéseket az customer_return_clusters adatbázis-táblába tárolja.

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

Fürtözés végrehajtása

Most, hogy létrehozta a tárolt eljárást, hajtsa végre a következő szkriptet a fürtözés végrehajtásához.

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

Ellenőrizze, hogy működik-e, és hogy valóban rendelkezünk-e az ügyfelek és a fürtleképezések listájával.

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

Itt van az eredmények összessége.

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

A fürtözési adatok használata

Mivel a klaszterezési eljárást az adatbázisban tárolta, hatékonyan végezhet klaszterezést az ugyanazon az adatbázisban tárolt ügyféladatokon. Az eljárást akkor kezdheti meg, amikor az ügyféladatok frissülnek, és az aktualizált klaszterezési információkat használja.

Tegyük fel, hogy promóciós e-mailt szeretne küldeni a 0. fürtben lévő ügyfeleknek, az inaktív csoportnak (az oktatóanyag harmadik részében láthatja, hogyan írta le a négy fürtöt). Az alábbi kód kiválasztja a 0. fürt ügyfeleinek e-mail-címeit.

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

A c.cluster értékét módosítva visszakaphatja más fürtökben található ügyfelek e-mail-címeit.

Erőforrások tisztítása

Ha befejezte ezt az oktatóanyagot, törölheti a tpcxbb_1gb adatbázist.

Következő lépések

Az oktatóanyag-sorozat negyedik részében megtanulta, hogyan:

• A modellt létrehozó tárolt eljárás létrehozása
• Az SQL gépi tanulás használatával történő fürtcsoportosítás végrehajtása
• A fürtözési adatok használata

Az R Machine Learning Servicesben való használatáról a következő témakörben olvashat bővebben:

• Egyszerű R-szkriptek futtatása
• R-adatstruktúrák, -típusok és -objektumok
• R-függvények