Tutorial de Python: Preparación de datos para clasificar clientes por categorías con aprendizaje automático de SQL

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores de Azure SQL Managed Instance

En la parte dos de esta serie de tutoriales de cuatro partes, restaurará y preparará los datos de una base de datos mediante Python. Más adelante en esta serie, usará estos datos para entrenar e implementar un modelo de agrupación en clústeres en Python con SQL Server Machine Learning Services o en clústeres de macrodatos.

En este artículo, aprenderá a:

• Separación de los clientes en distintas dimensiones mediante Python
• Carga de datos de la base de datos en una trama de datos de Python

En la parte uno, instaló los requisitos previos y restauró la base de datos de ejemplo.

En la parte tres, aprenderá a crear y entrenar un modelo de agrupación en clústeres K-Means en Python.

En la parte cuatro, aprenderá a crear un procedimiento almacenado en una base de datos que pueda realizar la agrupación en clústeres en Python en función de los nuevos datos.

Prerequisites

• En la segunda parte de este tutorial se supone que ha cumplido los requisitos previos de la parte uno.

Clientes separados

Para preparar la agrupación en clústeres de los clientes, primero separe los clientes en las dimensiones siguientes:

• orderRatio = relación de pedidos devueltos (número total de pedidos parcialmente o totalmente devueltos frente al número total de pedidos)
• itemsRatio = proporción de artículos devueltos (número total de artículos devueltos frente al número de artículos comprados)
• monetaryRatio = relación de importe devuelto (cantidad monetaria total de artículos devueltos frente a la cantidad comprada)
• frequency = frecuencia de retorno

Abra un nuevo cuaderno en Azure Data Studio y escriba el script siguiente.

En la cadena de conexión, reemplace los detalles de conexión según corresponda.

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

Carga de los datos en una trama de datos

Los resultados de la consulta se devuelven a Python mediante la función read_sql Pandas. Como parte del proceso, usará la información sobre columnas que ha definido en el script anterior.

customer_data = pd.read_sql(input_query, conn_str)

Ahora, muestre el principio de la trama de datos para asegurarse de que sea correcta.

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

Este es el 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

Limpieza de recursos

Si no quiere continuar con este tutorial, elimine la base de datos tpcxbb_1gb.

Pasos siguientes

En la parte dos de esta serie de tutoriales, ha completado estos pasos:

• Separación de los clientes en distintas dimensiones mediante Python
• Carga de datos de la base de datos en una trama de datos de Python

Para crear un modelo de aprendizaje automático que use estos datos de clientes, siga la parte tres de esta serie de tutoriales:

Tutorial de Python: Creación de un modelo predictivo