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

Articolo
04/17/2023

Si applica a: SQL Server 2017 (14.x) e versioni successive 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 ed è stato 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.

Prerequisiti

Per poter eseguire la seconda parte di questa esercitazione si 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 ordini di reso (numero totale di ordini parzialmente o completamente resi rispetto al numero totale di ordini)
itemsRatio = rapporto articoli resi (numero totale di articoli resi rispetto al numero di articoli acquistati)
monetaryRatio = rapporto importi resi (importo monetario totale di articoli resi rispetto all'importo di articoli acquistati)
frequency = frequenza dei resi

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 Pandas read_sql. 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))

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

Condividi tramite