Tutorial do Python: Preparar dados 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 segunda parte desta série de tutoriais de quatro partes, você restaurará e preparará os dados de um banco de dados usando Python. Mais adiante nesta série, você usará esses dados para treinar e implantar um modelo de clustering em Python com os Serviços de Aprendizado de Máquina do SQL Server ou em Clusters de Big Data.

Neste artigo, você aprenderá a:

• Separe os clientes em diferentes dimensões usando Python
• Carregue os dados do banco de dados em um quadro de dados Python

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

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

Na quarta parte, você aprenderá como criar um procedimento armazenado em um banco de dados que pode executar clustering em Python com base em novos dados.

Prerequisites

• A segunda parte deste tutorial pressupõe que você tenha cumprido os pré-requisitos da primeira parte.

Separe os clientes

Para se preparar para o agrupamento de clientes, você primeiro separará os clientes nas seguintes dimensões:

• orderRatio = taxa de ordem de retorno (número total de pedidos parcial ou totalmente devolvidos versus o número total de pedidos)
• itemsRatio = índice de itens de retorno (número total de itens devolvidos versus o número de itens comprados)
• monetaryRatio = rácio do montante de retorno (montante monetário total dos itens devolvidos versus o montante adquirido)
• frequência = frequência de retorno

Abra um novo bloco de anotações no Azure Data Studio e insira o seguinte script.

Na cadeia de conexão, substitua os detalhes da conexão conforme necessário.

# Load packages.
import pyodbc
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
from scipy.spatial import distance as sci_distance
from sklearn import cluster as sk_cluster

################################################################################################

## Connect to DB and select data

################################################################################################

# Connection string to connect to SQL Server named instance.
conn_str = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; SERVER=<server>; DATABASE=tpcxbb_1gb; UID=<username>; PWD=<password>')

input_query = '''SELECT
ss_customer_sk AS customer,
ROUND(COALESCE(returns_count / NULLIF(1.0*orders_count, 0), 0), 7) AS orderRatio,
ROUND(COALESCE(returns_items / NULLIF(1.0*orders_items, 0), 0), 7) AS itemsRatio,
ROUND(COALESCE(returns_money / NULLIF(1.0*orders_money, 0), 0), 7) AS monetaryRatio,
COALESCE(returns_count, 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'''


# Define the columns we wish to import.
column_info = {
    "customer": {"type": "integer"},
    "orderRatio": {"type": "integer"},
    "itemsRatio": {"type": "integer"},
    "frequency": {"type": "integer"}
}

Carregar os dados em um quadro de dados

Os resultados da consulta são retornados ao Python usando a função Pandas read_sql . Como parte do processo, você usará as informações da coluna definidas no script anterior.

customer_data = pd.read_sql(input_query, conn_str)

Agora exiba o início do quadro de dados para verificar se ele está correto.

print("Data frame:", customer_data.head(n=5))

Aqui está o conjunto de resultados.

Rows Read: 37336, Total Rows Processed: 37336, Total Chunk Time: 0.172 seconds
Data frame:     customer  orderRatio  itemsRatio  monetaryRatio  frequency
0    29727.0    0.000000    0.000000       0.000000          0
1    97643.0    0.068182    0.078176       0.037034          3
2    57247.0    0.000000    0.000000       0.000000          0
3    32549.0    0.086957    0.068657       0.031281          4
4     2040.0    0.000000    0.000000       0.000000          0

Limpeza de recursos

Se você não quiser continuar com este tutorial, exclua o banco de dados tpcxbb_1gb.

Próximos passos

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

• Separe os clientes em diferentes dimensões usando Python
• Carregue os dados do banco de dados em um quadro de dados Python

Para criar um modelo de aprendizado de máquina que use esses dados do cliente, siga a parte três desta série de tutoriais:

Tutorial do Python: Criar um modelo preditivo