Öğretici: SQL machine learning ile R'de kümeleme modeli dağıtma

Şunlar için geçerlidir: SQL Server 2016 (13.x) ve sonraki sürümleri Azure SQL Yönetilen Örnek

Bu dört bölümden oluşan öğretici serisinin dördüncü bölümünde, R'de geliştirilen bir kümeleme modelini SQL Server Machine Learning Services veya Büyük Veri Kümeleri kullanarak bir veritabanına dağıtacaksınız.

Kümeleme işlemini düzenli aralıklarla tekrarlayan bir şekilde gerçekleştirmek için, yeni müşteriler kaydolurken R betiğini herhangi bir uygulamadan çağırabilmelisiniz. Bunu yapmak için, R betiğini sql saklı yordamının içine yerleştirerek R betiğini bir veritabanına dağıtabilirsiniz. Modeliniz veritabanında yürütülürken, veritabanında depolanan verilere karşı kolayca eğitilebilir.

Bu makalede şunları nasıl yapacağınızı öğreneceksiniz:

• Bir model oluşturan saklı yordam oluştur
• Kümeleme gerçekleştirme
• Kümeleme bilgilerini kullanma

Birinci bölümde önkoşulları yüklemiş ve örnek veritabanını geri yüklemişsinizdir.

İkinci bölümde, kümeleme gerçekleştirmek için veritabanındaki verileri nasıl hazırlayabileceğinizi öğrendinsiniz.

Üçüncü bölümde R'de K-Means kümeleme modeli oluşturmayı ve eğitmeyi öğrendiniz.

Prerequisites

• Bu öğretici serisinin dördüncü bölümünde birinci bölümün önkoşullarını yerine getirdiğiniz ve ikinci ve üçüncübölümdeki adımları tamamladığınız varsayılır.

Bir model oluşturan saklı yordam oluştur

Saklı yordamı oluşturmak için aşağıdaki T-SQL betiğini çalıştırın. Prosedür, bu öğretici serisinin ikinci ve üçüncü bölümlerinde geliştirdiğiniz adımları yeniden oluşturur.

• müşterileri satın alma ve iade geçmişine göre sınıflandırma
• K-Means algoritması kullanarak dört müşteri kümesi oluşturma

Yordam, elde edilen müşteri kümesi eşlemelerini veritabanı tablosunda customer_return_clusters depolar.

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

Kümeleme gerçekleştirme

Saklı yordamı oluşturduğunuza göre kümeleme gerçekleştirmek için aşağıdaki betiği çalıştırın.

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

Sistemin çalıştığını ve müşterilerin listesi ile küme eşlemelerinin aslında mevcut olduğunu doğrulayın.

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

Sonuç kümesi aşağıdadır.

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

Kümeleme bilgilerini kullanma

Kümeleme yordamını veritabanında depoladığınız için, aynı veritabanında depolanan müşteri verilerine karşı kümeleme işlemini verimli bir şekilde gerçekleştirebilir. Müşteri verileriniz her güncelleştirildiğinde yordamı yürütebilir ve güncelleştirilmiş kümeleme bilgilerini kullanabilirsiniz.

Etkin olmayan grup olan küme 0'daki müşterilere tanıtım amaçlı bir e-posta göndermek istediğinizi varsayalım (bu öğreticinin üçüncü bölümünde dört kümenin nasıl açıklandığını görebilirsiniz). Aşağıdaki kod, küme 0'daki müşterilerin e-posta adreslerini seçer.

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

Diğer kümelerdeki müşterilerin e-posta adreslerini döndürmek için c.cluster değerini değiştirebilirsiniz.

Kaynakları temizle

Bu öğreticiyi tamamladığınızda tpcxbb_1gb veritabanını silebilirsiniz.

Sonraki Adımlar

Bu öğretici serisinin dördüncü bölümünde şunların nasıl yapılacağını öğrendiniz:

• Bir model oluşturan saklı yordam oluştur
• SQL makine öğrenmesi ile kümeleme gerçekleştirme
• Kümeleme bilgilerini kullanma

Machine Learning Services'de R kullanma hakkında daha fazla bilgi edinmek için bkz:

• Basit R betikleri çalıştırma
• R veri yapıları, türleri ve nesneleri
• R işlevleri