Tutorial: Menyebarkan model pengklusteran di R dengan pembelajaran mesin SQL

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru Azure SQL Managed Instance

Di bagian empat dari seri tutorial empat bagian ini, Anda akan menyebarkan model pengklusteran, yang dikembangkan di R, ke dalam database menggunakan SQL Server Machine Learning Services atau di Kluster Big Data.

Untuk melakukan pengklusteran secara teratur, karena pelanggan baru mendaftar, Anda harus dapat memanggil skrip R dari aplikasi apa pun. Untuk melakukannya, Anda dapat menyebarkan skrip R dalam database dengan meletakkan skrip R di dalam prosedur tersimpan SQL. Karena model Anda dijalankan dalam database, model dapat dengan mudah dilatih terhadap data yang disimpan dalam database.

Dalam artikel ini, Anda akan mempelajari cara:

• Membuat prosedur tersimpan yang menghasilkan model
• Melakukan pengklusteran
• Menggunakan informasi pengklusteran

Di bagian satu, Anda menginstal prasyarat dan memulihkan database sampel.

Di bagian dua, Anda mempelajari cara menyiapkan data dari database untuk melakukan pengklusteran.

Di bagian tiga, Anda mempelajari cara membuat dan melatih model pengklusteran K-Means di R.

Prasyarat

• Bagian empat dari seri tutorial ini mengasumsikan Anda telah memenuhi prasyarat bagian satu dan menyelesaikan langkah-langkah di bagian dua dan bagian tiga.

Membuat prosedur tersimpan yang menghasilkan model

Jalankan skrip T-SQL berikut untuk membuat prosedur tersimpan. Prosedur ini membuat ulang langkah-langkah yang Anda kembangkan di bagian dua dan tiga seri tutorial ini:

• mengklasifikasikan pelanggan berdasarkan riwayat pembelian dan pengembalian mereka
• menghasilkan empat kluster pelanggan menggunakan algoritma K-Means

Prosedur ini menyimpan pemetaan kluster pelanggan yang dihasilkan dalam tabel database 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

Melakukan pengklusteran

Sekarang setelah Anda membuat prosedur tersimpan, jalankan skrip berikut untuk melakukan pengklusteran.

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

Verifikasi bahwa ini berfungsi dan bahwa kami benar-benar memiliki daftar pelanggan dan pemetaan kluster mereka.

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

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

Menggunakan informasi pengklusteran

Karena Anda menyimpan prosedur pengklusteran dalam database, prosedur ini dapat melakukan pengklusteran secara efisien terhadap data pelanggan yang disimpan dalam database yang sama. Anda dapat menjalankan prosedur setiap kali data pelanggan Anda diperbarui dan menggunakan informasi pengklusteran yang diperbarui.

Misalkan Anda ingin mengirim email promosi kepada pelanggan di kluster 0, grup yang tidak aktif (Anda dapat melihat bagaimana empat kluster dijelaskan di bagian tiga tutorial ini). Kode berikut memilih alamat email pelanggan di kluster 0.

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

Anda dapat mengubah nilai c.cluster untuk mengembalikan alamat email untuk pelanggan di kluster lain.

Membersihkan sumber daya

Setelah selesai dengan tutorial ini, Anda dapat menghapus database tpcxbb_1gb.

Langkah berikutnya

Di bagian empat seri tutorial ini, Anda belajar cara:

• Membuat prosedur tersimpan yang menghasilkan model
• Melakukan pengklusteran dengan pembelajaran mesin SQL
• Menggunakan informasi pengklusteran

Untuk mempelajari selengkapnya tentang menggunakan R di Layanan Pembelajaran Mesin, lihat:

• Menjalankan skrip R sederhana
• Struktur, jenis, dan objek data R
• Fungsi R