Delen via

Python-zelfstudie: Een model implementeren om klanten te categoriseren met SQL Machine Learning

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

In deel vier van deze vierdelige reeks zelfstudies implementeert u een clusteringmodel, ontwikkeld in Python, in een database met behulp van SQL Server Machine Learning Services of op Big Data-clusters.

In deel vier van deze vierdelige reeks zelfstudies implementeert u een clusteringmodel, ontwikkeld in Python, in een database met behulp van SQL Server Machine Learning Services.

In deel vier van deze vierdelige reeks zelfstudies implementeert u een clusteringmodel, ontwikkeld in Python, in een database met behulp van Azure SQL Managed Instance Machine Learning Services.

Als u clustering regelmatig wilt uitvoeren, moet u, omdat nieuwe klanten zich registreren, het Python-script vanuit elke app kunnen aanroepen. Hiervoor kunt u het Python-script in een database implementeren door het Python-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 deze sectie verplaatst u de Python-code die u zojuist hebt geschreven naar de server en implementeert u clustering.

In dit artikel leert u het volgende:

  • Een opgeslagen procedure maken waarmee het model wordt gegenereerd
  • Clustering uitvoeren op de server
  • De clusterinformatie gebruiken

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 Python.

Vereiste voorwaarden

  • 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 één en twee 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
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

Clustering uitvoeren

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

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

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].[py_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 de volgende stappen uitgevoerd:

  • Een opgeslagen procedure maken waarmee het model wordt gegenereerd
  • Clustering uitvoeren op de server
  • De clusterinformatie gebruiken

Zie voor meer informatie over het gebruik van Python in SQL Machine Learning:

  • Last updated on