Tutorial Python: Menyebarkan model untuk mengategorikan pelanggan dengan pembelajaran mesin SQL

Berlaku untuk: SQL Server 2017 (14.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 Python, 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 Python dari Aplikasi apa pun. Untuk melakukannya, Anda dapat menyebarkan skrip Python dalam database dengan meletakkan skrip Python di dalam prosedur tersimpan SQL. Karena model Anda dijalankan dalam database, model dapat dengan mudah dilatih terhadap data yang disimpan dalam database.

Di bagian ini, Anda akan memindahkan kode Python yang baru saja Anda tulis ke server dan menyebarkan pengklusteran.

Dalam artikel ini, Anda akan mempelajari cara:

• Membuat prosedur tersimpan yang menghasilkan model
• Melakukan pengklusteran di server
• 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 belajar cara membuat dan melatih model pengklusteran K-Means di Python.

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 satu dan dua seri tutorial ini:

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

USE [tpcxbb_1gb]
GO

DROP procedure IF EXISTS [dbo].[py_generate_customer_return_clusters];
GO

CREATE procedure [dbo].[py_generate_customer_return_clusters]
AS

BEGIN
    DECLARE

-- Input query to generate the purchase history & return metrics
     @input_query NVARCHAR(MAX) = N'
SELECT
  ss_customer_sk AS customer,
  CAST( (ROUND(COALESCE(returns_count / NULLIF(1.0*orders_count, 0), 0), 7) ) AS FLOAT) AS orderRatio,
  CAST( (ROUND(COALESCE(returns_items / NULLIF(1.0*orders_items, 0), 0), 7) ) AS FLOAT) AS itemsRatio,
  CAST( (ROUND(COALESCE(returns_money / NULLIF(1.0*orders_money, 0), 0), 7) ) AS FLOAT) AS monetaryRatio,
  CAST( (COALESCE(returns_count, 0)) AS FLOAT) 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
 '

EXEC sp_execute_external_script
      @language = N'Python'
    , @script = N'

import pandas as pd
from sklearn.cluster import KMeans

#get data from input query
customer_data = my_input_data

#We concluded in step 2 in the tutorial that 4 would be a good number of clusters
n_clusters = 4

#Perform clustering
est = KMeans(n_clusters=n_clusters, random_state=111).fit(customer_data[["orderRatio","itemsRatio","monetaryRatio","frequency"]])
clusters = est.labels_
customer_data["cluster"] = clusters

OutputDataSet = customer_data
'
    , @input_data_1 = @input_query
    , @input_data_1_name = N'my_input_data'
             with result sets (("Customer" int, "orderRatio" float,"itemsRatio" float,"monetaryRatio" float,"frequency" float,"cluster" float));
END;
GO

Melakukan pengklusteran

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

--Create a table to store the predictions in

DROP TABLE IF EXISTS [dbo].[py_customer_clusters];
GO

CREATE TABLE [dbo].[py_customer_clusters] (
    [Customer] [bigint] NULL
  , [OrderRatio] [float] NULL
  , [itemsRatio] [float] NULL
  , [monetaryRatio] [float] NULL
  , [frequency] [float] NULL
  , [cluster] [int] NULL
  ,
    ) ON [PRIMARY]
GO

--Execute the clustering and insert results into table
INSERT INTO py_customer_clusters
EXEC [dbo].[py_generate_customer_return_clusters];

-- Select contents of the table to verify it works
SELECT * FROM py_customer_clusters;

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].[py_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 menyelesaikan langkah-langkah berikut:

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

Untuk mempelajari selengkapnya tentang menggunakan Python dalam pembelajaran mesin SQL, lihat:

• Mulai cepat: Membuat dan menjalankan skrip Python sederhana
• Tutorial Python lainnya untuk pembelajaran mesin SQL
• Menginstal paket Python dengan sqlmlutils