Esercitazione Python: Preparare i dati per classificare i clienti con Machine Learning in SQL

Si applica a: SQL Server 2017 (14.x) e versioni successive di Istanza gestita di SQL di Azure

Nella seconda parte di questa serie di esercitazioni in quattro parti si ripristineranno e prepareranno i dati da un database tramite Python. Più avanti nel corso della serie questi dati verranno usati per eseguire il training e la distribuzione di un modello di clustering in Python con Machine Learning Services per SQL Server oppure in cluster Big Data.

In questo articolo si apprenderà come:

• Separare i clienti in dimensioni diverse tramite Python
• Caricare i dati dal database in un frame di dati Python

Nella prima parte sono stati installati i prerequisiti e ripristinato il database di esempio.

Nella terza parte si apprenderà come creare ed eseguire il training di un modello di clustering K-Means in Python.

Nella quarta parte si apprenderà come creare una stored procedure in un database in grado di eseguire il clustering in Python in base ai nuovi dati.

Prerequisites

• La seconda parte di questa esercitazione presuppone che siano stati soddisfatti i prerequisiti della prima parte.

Separare i clienti

Per preparare il clustering dei clienti, occorre prima di tutto separare i clienti in base alle dimensioni seguenti:

• orderRatio = rapporto ordine restituito (numero totale di ordini parzialmente o completamente restituiti rispetto al numero totale di ordini)
• itemsRatio = rapporto articolo restituito (numero totale di articoli restituiti rispetto al numero di articoli acquistati)
• monetaryRatio = rapporto di importo restituito (importo monetario totale degli articoli restituiti rispetto all'importo acquistato)
• frequency = frequenza di restituzione

Aprire un nuovo notebook in Azure Data Studio e immettere lo script seguente.

Nella stringa di connessione sostituire i dettagli della connessione in base alle esigenze.

# 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"}
}

Caricare i dati in un frame di dati

I risultati della query vengono restituiti a Python usando la funzione read_sql Pandas. Nell'ambito del processo verranno usate le informazioni sulle colonne definite nello script precedente.

customer_data = pd.read_sql(input_query, conn_str)

Visualizzare ora l'inizio del frame di dati per verificare che sia corretto.

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

Il set di risultati è il seguente.

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

Pulire le risorse

Se non si intende continuare con questa esercitazione, eliminare il database tpcxbb_1gb.

Passaggi successivi

Nella seconda parte di questa serie di esercitazioni sono stati completati i passaggi seguenti:

• Separare i clienti in dimensioni diverse tramite Python
• Caricare i dati dal database in un frame di dati Python

Per creare un modello di Machine Learning che usa questi dati dei clienti, seguire la terza parte di questa serie di esercitazioni:

Esercitazione Python: Creare un modello di previsione