Tutorial do Python: Implantar um modelo para categorizar clientes com aprendizado de máquina SQL

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores Instância Gerenciada SQL do Azure

Na quarta parte desta série de tutoriais de quatro partes, você implantará um modelo de clustering, desenvolvido em Python, em um banco de dados usando os Serviços de Aprendizado de Máquina do SQL Server ou em Clusters de Big Data.

Para realizar clustering regularmente, à medida que novos clientes estão a registar-se, deve ser possível chamar o script Python a partir de qualquer aplicativo. Para fazer isso, você pode implantar o script Python em um banco de dados colocando o script Python dentro de um procedimento armazenado SQL. Como seu modelo é executado no banco de dados, ele pode ser facilmente treinado em relação aos dados armazenados no banco de dados.

Nesta seção, você moverá o código Python que acabou de escrever no servidor e implantará o clustering.

Neste artigo, você aprenderá a:

• Criar um procedimento armazenado que gera o modelo
• Executar clustering no servidor
• Utilizar as informações de clustering

Na primeira parte, você instalou os pré-requisitos e restaurou o banco de dados de exemplo.

Na segunda parte, você aprendeu como preparar os dados de um banco de dados para executar clustering.

Na terceira parte, você aprendeu como criar e treinar um modelo de clustering K-Means em Python.

Pré-requisitos

• A quarta parte desta série de tutoriais pressupõe que você tenha cumprido os pré-requisitos da parte um e concluído as etapas da parte dois e da parte três.

Criar um procedimento armazenado que gera o modelo

Execute o seguinte script T-SQL para criar o procedimento armazenado. O procedimento recria as etapas desenvolvidas nas partes um e dois desta série de tutoriais:

• Classifique os clientes com base no seu histórico de compras e devoluções
• gerar quatro clusters de clientes usando um algoritmo 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

Executar agregação de dados

Agora que você criou o procedimento armazenado, execute o script a seguir para executar o clustering usando o procedimento.

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

Utilizar a informação de clustering

Como você armazenou o procedimento de clustering no banco de dados, ele pode executar o clustering de forma eficiente em relação aos dados do cliente armazenados no mesmo banco de dados. Você pode executar o procedimento sempre que os dados do cliente forem atualizados e usar as informações de clustering atualizadas.

Suponha que você queira enviar um e-mail promocional para clientes no cluster 0, o grupo que estava inativo (você pode ver como os quatro clusters foram descritos na parte três deste tutorial). O código a seguir seleciona os endereços de e-mail dos clientes no cluster 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

Você pode alterar o valor c.cluster para retornar endereços de email para clientes em outros clusters.

Limpeza de recursos

Quando terminar este tutorial, você poderá excluir o banco de dados tpcxbb_1gb.

Próximos passos

Na quarta parte desta série de tutoriais, você concluiu estas etapas:

• Criar um procedimento armazenado que gera o modelo
• Executar agrupamento no servidor
• Utilize a informação de agrupamento

Para saber mais sobre como usar Python no aprendizado de máquina SQL, consulte:

• Guia de início rápido: crie e execute scripts Python simples
• Outros tutoriais Python para aprendizado de máquina SQL
• Instalar pacotes Python com sqlmlutils